I agree with @RemusRusanu (+1 for his answer)
SELECT COUNT(*) FROM mydb.mytable
in InnoDB behaves like a transactional storage engine should. Compare it to MyISAM.
MyISAM
If mydb.mytable
is a MyISAM table, launching SELECT COUNT(*) FROM mydb.mytable;
is just like running SELECT table_rows FROM information_schema.table WHERE table_schema = 'mydb' AND table_name = 'mytable';
. This triggers a quick lookup of the row count in the header of the MyISAM table.
InnoDB
If mydb.mytable
is a InnoDB table, you get hodge-podge of things going on. You have MVCC going on, governing the following:
- ib_logfile0/ib_logfile1 (Redo Logs)
- ibdata1
- Undo Logs
- Rollbacks
- Data Dictionary Changes
- Buffer Pool Management
- Transaction Isolation (4 types)
- Repeatable Reads
- Read Committed
- Read Uncommitted
- Serializable
Asking InnoDB for a table count requires navigation through these ominous things. In fact, one never really knows if SELECT COUNT(*) from mydb.mytable
counts repeatable reads only or includes reads that have been committed and those that are uncommitted.
You could try to stabilize things a little by enabling innodb_stats_on_metadata.
According to the MySQL Documentation on innodb_stats_on_meta_data
When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.
Disabling it may or may not give you a more stable count in terms of setting up EXPLAIN plans. It may affect performance of SELECT COUNT(*) from mydb.mytable
in either a good way, bad way, or not at all. Give it a Try and See !!!
SELECT COUNT(*) FROM ...
queries are precise. If you prefer, phpMyAdmin can be configured to always use exact row counts at the expense of speed. More info: stackoverflow.com/questions/11926259/… – DOOManiac Mar 26 '15 at 21:18