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