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
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