2

I have the following table:

mysql> select count(*) from employees;  
+----------+  
| count(*) |  
+----------+  
|    10000 |  
+----------+  
1 row in set (0.03 sec)  

I do an EXPLAIN: mysql> explain select last_name from employees order by last_name;

+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 9894 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+  
1 row in set (0.00 sec)

The rows are 9894. I was expecting 10000.
I do:

mysql> analyze table employees;
+-------------------------------------+---------+----------+----------+  
| Table                               | Op      | Msg_type | Msg_text |  
+-------------------------------------+---------+----------+----------+  
| sql_dummy.employees | analyze | status   | OK       |  
+-------------------------------------+---------+----------+----------+  
1 row in set (0.04 sec)  

and re-run EXPLAIN:

mysql> explain select last_name from employees order by last_name;

+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.01 sec)  

The rows are now 10031.

Does anyone know why the rows is never 10000? I have noticed this in other cases as well.

1 Answer 1

2

You may find this surprising but that's the behavior of InnoDB.

The InnoDB storage engine does cardinality approximation by walking a few levels down the nonleaf BTREE nodes and takes an educated guess.

I wrote about this a long time ago based on mysqlperformanceblog.com

Perhaps you could try disabling that behavior by running this:

SET GLOBAL innodb_stats_on_metadata = 0;

Setting innodb_stats_on_metadata should stabilize the approximate cardinality and you should get the same cardinality over and over again.

4
  • Do they do this for some kind of performance reasons?It seems easy to me to keep a counter for the leaf nodes of the b-tree
    – Cratylus
    Aug 13, 2013 at 18:07
  • BTW if you have some time to spare dba.stackexchange.com/questions/48072/… :)
    – Cratylus
    Aug 13, 2013 at 18:08
  • @Cratylus Keeping a real count on a transaction based engine like innodb its very very hard because of the row level locking and rollbacks that should be happen on the same time because innodb is more multithreaded engine and calculations on multiple threads is hard to pull off correctly thats probably the reason why it's a estimate value Aug 14, 2013 at 12:18
  • Makes sense now
    – Cratylus
    Aug 14, 2013 at 18:32

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.