Re: "select count(*) from contacts" is too slow!

Ang Chin Han <angch(at)bytecraft(dot)com(dot)my> writes:

> Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs,
> etc are autocommitted.

That's sort of true, but not the whole story. Even autocommitted transactions
can be pending for a significant amount of time. The reason it's accurate is
because with mysql isam tables all updates take a table level lock. So there's
never a chance to select the count while an uncommitted transaction is
pending, even if the update takes a long time.

This is simple and efficient when you have low levels of concurrency. But when
you have 4+ processors or transactions involving lots of disk i/o it kills
scalability.

I'm curious how it's implemented with innodb tables. Do they still take a
table-level lock when committing to update the counters? What happens to
transactions that have already started, do they see the new value?

Actually it occurs to me that that might be ok for read-committed. Is there
ever a situation where a count(*) needs to represent an old snapshot in
read-committed? It has to for long-running selects, but if the count(*) itself
is always fast that need should never arise, just shared-lock and read the
value and unlock.

In order words, imagine if you had every transaction keep a net delta of rows
for every table and at commit time locked the entire table and updated the
count. The lock would be a point of contention but it would be very fast since
it would only have to update an integer with a precalculated adjustment. In
read-committed mode that would always be a valid value. (The transaction would
have to apply its own deltas I guess.)

--
greg