MySQL Bugs: #38538: 'rows' in EXPLAIN output doesn't reflect number of rows correctly with LIMIT

Bug #38538	'rows' in EXPLAIN output doesn't reflect number of rows correctly with LIMIT
Submitted:	4 Aug 2008 14:23	Modified:	26 Jan 2009 16:14
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.70	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
If there is LIMIT clause, EXPLAIN still shows it scans all rows, though SHOW STATUS shows SELECt actually scans only first N rows.

For example:

mysql> explain select pk from i order by pk limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 295342
        Extra: Using index
1 row in set (0.00 sec)

It would be good if EXPLAIN will shows more close to reality number of rows.

How to repeat:
Create a simple table:

CREATE TABLE `i` (
`pk` int(11) NOT NULL auto_increment,
`dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`pk`),
KEY `dt` (`dt`)
) ENGINE=InnoDB;

and add some data then check explain output:

explain select pk from i order by pk limit 1\G

Looks like the problem is fixed in 6.0.7:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 6.0.7-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `i` (
    -> `pk` int(11) NOT NULL auto_increment,
    -> `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMES
TAMP,
    -> PRIMARY KEY (`pk`),
    -> KEY `dt` (`dt`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.11 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.05 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.09 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> explain select pk from i order by pk limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index
1 row in set (0.06 sec)

mysql> explain select pk from i order by pk limit 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index
1 row in set (0.00 sec)

It also looks fixed in 5.1.30. But 5.0.70 is still affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.70-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `i` (
    -> `pk` int(11) NOT NULL auto_increment,
    -> `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMES
TAMP,
    -> PRIMARY KEY (`pk`),
    -> KEY `dt` (`dt`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.34 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.06 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into i () values ();
Query OK, 1 row affected (0.05 sec)

mysql> explain select pk from i order by pk limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using index
1 row in set (0.03 sec)

This is a known limitation of 5.0 optimizer. EXPLAIN works correctly - it prints what the optimizer thinks it will need to scan.  5.0 optimizer considers two options for resolving ORDER BY ... LIMIT:
* Use an ordered index
* Use filesort + limit
but the choice is not cost based (it's rule-based), and hence the value of "expected number of records we'll need to scan when using an ordered index" is never calculated (and thus not shown in EXPLAIN).

In MySQL 5.1 and up, the choice is cost-based, the value is calculated and displayed (and there's a USE/IGNORE INDEX FOR {ORDER|GROUP} BY hint in case the optimizer gets it wrong)

Considering that the problem is fixed in a GA version, 5.1, changing status to "Closed".