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 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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '13 at 18:07
    
BTW if you have some time to spare dba.stackexchange.com/questions/48072/… :) –  Cratylus Aug 13 '13 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 –  Raymond Nijland Aug 14 '13 at 12:18
    
Makes sense now –  Cratylus Aug 14 '13 at 18:32
add comment

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.