3

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.

Community
  • 1
Patrick
  • 4,089
  • 7
  • 26
  • 26
  • 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?](http://stackoverflow.com/questions/6082810/how-to-do-a-fast-but-innacurate-innodb-row-count) – ypercubeᵀᴹ Nov 29 '12 at 06: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 08:17

2 Answers2

1

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.

Community
  • 1
RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
-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

Bacon Bits
  • 1,815
  • 14
  • 16
  • 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 08:22
  • @ypercube Yes, that's why RDBMSs have variable [isolation levels](http://msdn.microsoft.com/en-us/library/ms173763.aspx) and [table hints](http://msdn.microsoft.com/en-us/library/ms187373.aspx). 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](http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html), 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