I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;
| COUNT(1) |
| 25453476 |
1 row in set (2 min 36.20 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;
| id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
|  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |
1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

  • 1
    Yes, you can. It may not be an exact count, but when the table has high traffic, the exact number changes all the time anyway. See this question, too: How to do a fast but innacurate InnoDB row count? Nov 29, 2012 at 6:43
  • @ypercube would the EXPLAIN command be more accurate than a SHOW TABLE STATUS command? I've seen the row count fluctuate wildly in phpMyAdmin
    – Patrick
    Nov 29, 2012 at 14:53
  • why does this question refer to foreign keys in the title?
    – miracle173
    Jan 28, 2013 at 8:17

2 Answers 2


You cannot trust the EXPLAIN plan's row count. Why?

Each time Query Optimization takes place where it involves an InnoDB table, the InnoDB Storage Engine does an approximation of the BTREE entries for the keys. They approximations change from query to query.

Simply run SHOW INDEXES FROM guage_data; multiple times and you will get a different set of row counts each time. You could set innodb_stats_on_metadata to 0:

SET GLOBAL innodb_stats_on_metadata = 0;

This will cause SHOW INDEXES FROM guage_data; to give the same number over and over again. This is still self-defeating because the row count would still be approximated anyway. You would have to disable the metadata stats and run ANALYZE TABLE. Again, this is self-defeating.

With innodb_stats_on_metadata on, approximations are always done. I wrote about this back on June 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

In light of all this, you are better off doing SELECT id FROM guage_data if id is the PRIMARY KEY. However, notice in your EXPLAIN plan that MySQL choose to traverse the gauge_data_FI_1 index. This makes all the sense in the world if this table is InnoDB. Why? Every non-unique index stores the rowid of the PRIMARY KEY anyway.


This behavior seems very strange to me. I don't have any MySQL databases with tables this size, but COUNT(*) on my Informix and MS SQL Server databases that have tables even larger than this run instantaneously.

How exact do you need it to be? If inaccurate but reasonable estimates are fine, can you use this:

WHERE TABLE_NAME = 'gauge_data'


  • 3
    This is an INNODB table, so the table does not keep updated row counts like MyISAM tables
    – Patrick
    Nov 29, 2012 at 14:52
  • @BaconBits Strange but true. I'm not much familiar with Informix but InnoDB uses the MVCC model, where it is not easy to have accurate counts of table rows. Jul 29, 2013 at 8:22
  • @ypercube Yes, that's why RDBMSs have variable isolation levels and table hints. You can specify NOLOCK to read uncommitted data. This means you can direct the query planner to risk giving you unreliable data in exchange for not locking the database at all. MySQL appears to only support isolation levels, meaning your query would need to set the session isolation level. Probably what OP should actually be using.
    – Bacon Bits
    Jul 30, 2013 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.