MySQL - Complexity of: SELECT COUNT(*) FROM MyTable;

Question

What is the complexity of this MySQL query

SELECT COUNT(*) FROM MyTable;

Is the count of number of entries in a table stored somewhere and updated every time a row is inserted or deleted? If that is the case, then the complexity should be O(1).

Answer 1

It depends on the storage engine.

• For MyISAM the total row count is stored for each table so SELECT COUNT(*) FROM yourtable is an operation O(1). It just needs to read this value.
• For InnoDB the total row count is not stored so a full scan is required. This is an O(n) operation.

From the manual:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 13.2.13.1, "InnoDB Performance Tuning Tips".

Answer 2

AFAIK in MyISAM rows-count are cached, in InnoDB not, and with every count-all he counts all rows.