Re: COUNT(*) very slowly in large InnoDB table

[Rick James]

http://en.wikipedia.org/wiki/ACID
http://www.tutorialspoint.com/mysql/mysql-transactions.htm
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html

MyISAM (not InnoDB) optimizes
SELECT COUNT(*) FROM tbl;

But virtually every use (both MyISAM and InnoDB) of COUNT() must scan through the data and/or index to derive the answer.

SELECT COUNT(*) FROM tbl WHERE x > 3;
will scan through an index on x if there is one. If not, it will scan through the entire table.

SELECT COUNT(...) FROM t1 JOIN t2 ...;
probably has to at least pretend to build the entire resultset.