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;