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.
The reason for this is that the choice whether to use an index is made by two different pieces of code (one for EXPLAIN, another one for SELECT).
EXPLAIN uses this code in JOIN::exec_inner():
if (select_options & SELECT_DESCRIBE){...bool made_call= false;if (order &&(order != group_list || !(select_options & SELECT_BIG_RESULT)) &&(const_tables == table_count ||((simple_order || skip_sort_order) &&(made_call=true) &&test_if_skip_sort_order(&join_tab[const_tables], order,select_limit, 0,&join_tab[const_tables].table->keys_in_use_for_query))))order=0;SELECT uses this in create_sort_index:
/*When there is SQL_BIG_RESULT do not sort using index for GROUP BY,and thus force sorting on disk unless a group min-max optimizationis going to be used as it is applied now only for one table querieswith covering indexes.*/if ((order != join->group_list ||!(join->select_options & SELECT_BIG_RESULT) ||(select && select->quick &&select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) &&test_if_skip_sort_order(tab,order,select_limit,0,is_order_by ? &table->keys_in_use_for_order_by :&table->keys_in_use_for_group_by)){The create_sort_index() call is the second one made from JOIN::exec_inner.