The most efficient method to obtain the total entries into a mysql table

Question

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but I'm thinking to read it from information_schema instead of doing the count.

select table_rows from tables where table_name = 'my_table'

Is this reliable enough ?

If there is a different method please let me know.

thanks!

Answer 1

If the table is MyISAM then you can

select table_rows from information_schema.tables
where table_schema='my_db' table_name = 'my_table';

or

select count(1) from my_db.my_table;

Either will work since the row count is physically stored in the .MYD file of the MyISAM table.

If the table is using InnoDB, the information_schema is absolutely no good for you because of the MVCC and transaction isolation it supports.

What is even worse about InnoDB is terms of counting is the fact that mysqld will guess at the cardinality of a table (row count) by doing BTREE dives in the indexes.

To learn more about this InnoDB Counting phenomenon, please read my Apr 23, 2012 post : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?