2

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.

NeDark
  • 1,212
  • 7
  • 23
  • 36

2 Answers2

7

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.

RC.
  • 27,409
  • 9
  • 73
  • 93
  • 1
    +1 scroll down and read Ken Jacob's post (5) http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ – Jon Black Oct 12 '10 at 22:17
6

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/

AlePani
  • 519
  • 2
  • 7