sqlsvr wrote:

> The query: SELECT COUNT(*) FROM accounts will run incredibly slow on a table that has half a million records. I thought it would use a key by default? SQL Server could return it in half a millisecond.
>
> Shouldn't a COUNT query be fast?

Not in an MVCC database. The index includes record versions that are
older than your transaction can see and versions that are too new for
your transaction. Although Monty Widenius has said that when MariaDB
is transactional, he'll keep transaction information in the index, all
the MVCC databases I know of must read the record version to determine
whether it fits the view of the current transaction. Adding transaction
information would increase the length of index entries by 16 bytes -
which may or may not be a good tradeoff for being able to get a reliable
count without reading the data.

Non-MVCC systems sometimes maintain the count as a value to avoid the
cost of reading the index. That's hard for an MVCC system because three
concurrent transactions may "see" three different counts - each correct
from the point of view of that transaction.

There are several reasons for counting a table - one is to generate
a unique ascending key. If that's what you want, use a generator
(aka Sequence).


Good luck,

Ann