Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
Description
EXPLAIN output doesn't match query execution for a query.
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
|
create table t2 (key1 int, col1 int, key(key1));
|
insert into t2 select a,a from ten;
|
insert into t2 select 15,15 from one_k;
|
alter table t2 add key2 int, add key(key2);
|
explain
|
select * from t2 ignore index for order by (key1) where col1<0 order by key1 limit 10;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t2 | index | NULL | key1 | 5 | NULL | 10 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
This looks odd - why does the optimizer use index key1 when we have explicitly specified *ignore index for order by (key1)* ?
Let's run the SELECT itself:
MariaDB [j2]> flush status;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [j2]> select * from t2 ignore index for order by (key1) where col1<0 order by key1 limit 10;
|
Empty set (0.03 sec)
|
|
MariaDB [j2]> show status like '%sort%';
|
+---------------------------+-------+
|
| Variable_name | Value |
|
+---------------------------+-------+
|
| Sort_merge_passes | 0 |
|
| Sort_priority_queue_sorts | 1 |
|
| Sort_range | 0 |
|
| Sort_rows | 0 |
|
| Sort_scan | 1 |
|
+---------------------------+-------+
|
5 rows in set (0.01 sec)
|
It shows that the query did use filesort.