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.
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.
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)
- Undo Logs
- Data Dictionary Changes
- Buffer Pool Management
- Transaction Isolation (4 types)
- Repeatable Reads
- Read Committed
- Read Uncommitted
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 !!!