Fast counting of rows on InnoDB

Fast counting of rows on InnoDB

See, MyISAM always stores the number of rows on the table header. So, whenever we ask "how many rows are there?", it can just grab the count and return it. Not InnoDB. This makes InnoDB very slow in count queries without a where clause.

So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can’t be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.

If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.

The trick is to hint it at a specific index. So, if you’re getting poor response times from InnoDB count, it means you’ve got lots of rows that have to be counted one at a time. And since you’ve got a lot of rows, you have at least one index. Just pick an index that will never contain a NULL value, and tell MySQL to USE INDEX (index_name).

Note: COUNT doesn't count NULL values, so if you are counting values by a field that has NULL values, those rows won't be counted by COUNT.

SELECT COUNT(*) FROM messages USE INDEX (index_messages_on_remote_created_at);

+----------+
| count(*) |
+----------+
|  1276831 |
+----------+

1 row in set (3.19 sec)

Reference:

http://kingori.co/minutae/2013/05/mysql-count-innodb/

Fast MySQL InnoDB count. Really fast

MySQL Performance Blog | COUNT(*) for Innodb Tables

MySQL 5.1 Reference Manual | Counting Rows