Mysql – Why isn’t the rows of an EXPLAIN accurate

explaininnodbMySQLoptimizationperformance

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.

Best Answer

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

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Aug 03, 2011 : When should I rebuild indexes?
Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables?

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.

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql

Mysql – Filesort while using primary key for orderby

KEY `index_dedupe_variables`
(`deleted_at`,`state`,`reference_type`,`reference`,`id`,`client_identifier`)

Try removing that index and replacing it with this one:

KEY `new_index`
(`deleted_at`,`state`,`reference_type`,`id`)

Even when an index is being used, only the useful portion of the index is used; columns to the right of the first instance where the index cannot be used are not considered in that index.

In your query, 'reference' uses a wildcard on the left side, making that column of the index not useful, and eliminating the use of `id` in the index.

Your explain plan tells you this is happening:

ref: const,const,const

From this, you can tell that only the first three columns of the index are actually being used by the query.

By redefining the index to eliminate the unusable "reference" column, we can use all 4 of the columns of the index, and since the index is already sorted in the order you want, we can then do this:

WHERE `deleted_at` IS NULL
  AND state = 'Maharashtra'
  AND reference_type = 4 
  AND reference LIKE '%8858' 
ORDER BY deleted_at, state, reference_type, id

We don't really want them ordered by those first three columns, but since all they are all equality comparisons, ordering by them won't change the results and it should be "obvious" to the optimizer that it can simply return the results in index order... no filesort needed.

Your EXPLAIN should change to "Using Where" and possibly "Using index" (since this can mean "using index for lookups" even when it's not a covering index.)

It's possible that the optimizer might catch on and not use a filesort even if your ORDER BY were only `id`\ so you might try it both ways after you change the index.

Also, remove the use index from your query. It shouldn't be needed.

Best Answer

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

Mysql – Filesort while using primary key for orderby

Related Question