InnoDB and COUNT(*)

MySQL & MariaDB Other MySQL® Questions
1

It is probably well-known feature/bug but still is a somewhat mystery for me - why exactly a simple “SELECT COUNT(*) FROM table” can take hours to complete on a relatively small dataset (~50M records), not depending on isolation level, no different in 4/4.1/5 ?

Thank you!

Dmitry

2

Innodb does not store count(*) counter as MyISAM does, this is because it is multi version system and each transaction could have different number of rows visible.

So SELECT COUNT(*) is executed as full table scan or index scan.
If your table is fragmented (physically) it may take quite a while.

SHOW TABLE STATUS LIKE ‘table’ provides approximate row counter which can be used for some applications. There also other workarounds.

3

That’s what I thought… even SELECT COUNT(primary_key_field) was no faster (

Yeah, my last idea was to create a table with list of tables and a bunch of triggers to keep actual row counts ))

Just curious - haven’t you had clients who had the same problem? Weren’t they surprised (not in a good way)? ))

Dmitry