Can COUNT(*) be used in MySQL on InnoDB tables?

COUNT() function returns a number of rows returned by a query. In a popular opinion COUNT(*) should not be used on InnoDB tables, but this is only half true.

If a query performs filtering on any column, there is no relevant difference in how COUNT(*) will be executed regardless of the storage engine. In any such case MySQL has to look for matching rows and then count them.

In the following queries COUNT(*) can be used without any negative impact on performance:

SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled = 1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND is_enabled = 1

The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value instantly without even accessing any rows. InnoDB, on the other hand, cannot easily keep such statistics as it enables customizable views on data and changes to different transactions. It means the engine has to read and count all rows in a table every time such query is executed.

Example query where COUNT(*) works slower on an InnoDB table:


Can COUNT(*) be used on InnoDB tables? Yes, it can. However it should be avoided for counting how many rows a table has, especially if it is expected to perform as good as on MyISAM tables.

Take care of your MySQL performance.

MySQL audits available from only $129 per server. Learn More
blog comments powered by Disqus