Why does InnoDB do a full scan for COUNT(*)?

Question

In a table with 5 millions rows, a SELECT count(*) FROM table would be instant in MyISAM but would take several seconds in InnoDB.

Why is this that way? Why haven't they optimise count in InnoDB like MyISAM?

Thanks.

Answer 1

It's a difference in implementation. InnoDB supports transactions and therefore it has to count the rows based on your transactionally consistent view of the table(s). Since MyISAM doesn't support ACID properties, if a row is inserted, it's inserted for everyone and therefore it can just update a count it keeps within the storage engine.

Answer 2

MyIsam has the number of rows stored somewhere and so the query is almost instant, InnoDB has to scan the table to get the full count. If you have a conditional count ( Ex: "SELECT COUNT(*) WHERE CUSTOMER =4" ) both have to do the scan and there is not much difference in this case

Source:

http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/