Bug #39424 EXPLAIN shows wrong access method for FROM subquery with ORDER BY
Submitted: 12 Sep 2008 17:18 Modified: 10 Jan 2013 11:24
Reporter: Sergey Petrunya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1/5.0 OS:Any
Assigned to: CPU Architecture:Any

[12 Sep 2008 17:18] Sergey Petrunya
Description:
EXPLAIN shows wrong access method for FROM subquery with ORDER BY. See an example below.

How to repeat:
#
#  Filling the table
#
CREATE TABLE `sort_table` (
 `id` int(10) unsigned NOT NULL,
 `sort` datetime NOT NULL,
 `info1` int(10) unsigned NOT NULL,
 `info2` int(10) unsigned NOT NULL,
 `info3` int(10) unsigned NOT NULL,
 KEY `info1` (`info1`,`info2`,`info3`),
 KEY `info4` (`info1`,`info2`,`info3`,`sort`)
) CHARSET=utf8;

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
# insert 100K rows
insert into sort_table select A.a, now(), B.a, C.a, D.a from t0 A, t0
B, t0 C, t0 D, t0 E;

#
# Pick the constants such that conditions on info(1,2,3) match 100 records,
# and condition on sort matches nothing.
#
mysql> explain select DISTINCT(id) from (select id from sort_table
FORCE INDEX (info1) where info1=7 AND info2=7 and info3=7 and  (sort
>='2008-10-10' AND sort <= '2008-11-11') ORDER BY sort DESC) t\G
*************************** 1. row ***************************
          id: 1
 select_type: PRIMARY
       table: <derived2>
        type: system
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 0
       Extra: const row not found
*************************** 2. row ***************************
          id: 2
 select_type: DERIVED
       table: sort_table
        type: ALL
possible_keys: info1
         key: info1
     key_len: 12
         ref:
        rows: 99
       Extra: Using filesort
2 rows in set (0.01 sec)

#
# Now analyze the counters:
#
mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select DISTINCT(id) from (select id from sort_table FORCE INDEX
(info1) where info1=7 AND info2=7 and info3=7 and  (sort
>='2008-10-10' AND sort <= '2008-11-11') ORDER BY sort DESC) t\G
Empty set (0.01 sec)

mysql> show status like 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1     |
| Handler_read_next          | 100   | ---(1)
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 1     | ---(2)
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 14    |
+----------------------------+-------+
15 rows in set (0.00 sec)

When we look at (1) and (2) it is a apparent that
* Full table scan wasn't done. If it was, we'd see 100K reads
* Instead it did an index scan, and got 100 records. This is exactly how
 many records the range scan over "info1=7 AND info2=7 and info3=7" would
 produce.

Looks like an EXPLAIN bug
[12 Sep 2008 17:56] MySQL Verification Team
Thank you for the bug report. Verified as described.
[10 Jan 2013 11:24] Erlend Dahl
Can't repeat on recent trunk (5.7).