Bug #59195 Change "rows" to "est rows" in explain output

• Submitted: 28 Dec 2010 17:05
• Modified: 28 Dec 2010 17:45
• Reporter: Jonathan Miller
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.5.7-rc
• OS: Linux
• CPU Architecture: Any

[28 Dec 2010 17:05] Jonathan Miller

Description:
Noticed while working on other test related programming that explain was returning a larger row count then was select count(*).

For example.

mysql> select count(*) from account;
+----------+
| count(*) |
+----------+
|  5000000 | 
+----------+
1 row in set (1.00 sec)

mysql> explain select count(*) from account;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | account | index | NULL          | PRIMARY | 4       | NULL | 5000168 | Using index | 
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from account;
+----+-------------+---------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | account | ALL  | NULL          | NULL | NULL    | NULL | 5000168 |       | 
+----+-------------+---------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from account;
+----------+
| count(*) |
+----------+
| 40000000 | 
+----------+
1 row in set (1 min 25.03 sec)

mysql> explain select count(*) from account;
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | account | index | NULL          | PRIMARY | 4       | NULL | 40000162 | Using index | 
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.01 sec)

 

How to repeat:
mysql> show create table account;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| account | CREATE TABLE `account` (
  `aid` int(11) NOT NULL DEFAULT '0',
  `bid` int(11) DEFAULT NULL,
  `balance` double DEFAULT NULL,
  `filler` char(80) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Load 40 or 50 million rows

select count(*) from account;
explain select count(*) from account;
explain select * from account;

[28 Dec 2010 17:21] Valeriy Kravchuk

As this is InnoDB table, number of rows in it is only estimated (precise number of rows is not known until one will read them all). Check SHOW TABLE STATUS for the table also.

This is documented at http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html and thus is not a bug.

[28 Dec 2010 17:39] Jonathan Miller

Since the "rows" count returned by explain is an estimate the number of rows MySQL believes it must examine to execute the query, the output should reflect it as an estimate and not actual rows.

This can be confusing to some customers. By changing "rows" to "est rows" it would be clearer to all customers.

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

#rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.