I think that David's response will be very helpful to the original poster.

There are a few things I thought I could second.

Firstly, David was not joking when he implied that select count(*)
based queries is a possible weakness in any MGA database.

The crux of the problem is that in a locking system, you don't even
need to check the data pages to do a select count, and if the index is
already cached, it is even faster. I mean your inserts will be sitting
there waiting to be processed while your count(*) is running, so it is
not all good news.

With a MGA system, you COULD count the nodes of the index, but the
problem is that doesn't tell you how many records your transaction is
able to see, so if there is more than one active transaction, then the
answer would probably be wrong. Firebird will not even read the index
if there is not a where clause to knock out some records, because the
index read would just be just another I/O read for no gain.

The next question would be "Well why not store the transaction
information in the index too? That way you don't need to check the
data pages to see if a record is visible." That is a reasonable
question, but unfortunately doing so would bloat the index to the
point where performance would actually significantly suffer in pretty
much every other operation (the index becomes less dense and therefore
more I/O is required for it).

So you don't get the impression it is a weird Firebird thing, it is a
problem for Postgres as well.

(http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php)

It is actually quite an interesting discussion if you follow it, and
possibly some ideas may come out as to a better compromise for the
count(*) operation, who knows. Hopefully in the future someone
brilliant comes up with a method that brings the best of both worlds.

Next, there are some good techniques in a MGA system such as Firebird
for improving the speed considerably. I wont repeat it, but it is
right here

(http://groups.yahoo.com/group/firebird-support/message/56457)

Thirdly, I would certainly hope that for you 50% of the requirement of
your database is count(*) related, because you are testing and basing
your judgements (possibly unintentionally) on that parameter. If you
run queries that actually have to fetch and return data, the results
will be significantly closer, and if you run the inserts concurrently
to the selects, you may find the readers and writers not blocking each
other actually gives MGA a real edge. You also want to check how
competent the other DBMS are at actually fetching from their data pages.

Fouthly (and finally), there is one difference in Firebird's index
structure that you should be aware of. It is directional, not
bi-directional. This allows a greater density, and can still help with
most operations, but certainly not all. An index is ascending unless
you specifically declare it to be descending. That means that a normal
index on a field qty could not help you run the following

select max(qty) from tablea

However add a descending index to qty and it will fly. Also be aware
that primary and foreign keys internally create an ascending index to
implement, so don't add another duplicate index on those fields.
Duplicate indices (2 or more indices on the same sequence of fields)
actually confuses the optimiser in some cases, and it may choose to
use neither. Indexes on fields with lots of duplicate values are also
of little help, so if 95% of items have a qty < 5 for example, then
the index will have a very poor selectivity.

You can create a combined index on (qty, id), which is just as useful
for any index on qty alone, but it will have perfect selectivity.

Hope that also helps

Adam