Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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? –  ypercube Nov 29 '12 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 '12 at 14:53
    
why does this question refer to foreign keys in the title? –  miracle173 Jan 28 '13 at 8:17

2 Answers 2

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:

SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gauge_data'

Source

share|improve this answer
3  
This is an INNODB table, so the table does not keep updated row counts like MyISAM tables –  Patrick Nov 29 '12 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. –  ypercube Jul 29 '13 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 '13 at 19:44

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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