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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
EXPLAIN output doesn't match query execution for a query. {noformat} 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); {noformat} {noformat} 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 | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ {noformat} 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: {noformat} 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) {noformat} It shows that the query did use filesort. |
EXPLAIN output doesn't match query execution for a query. {noformat} 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); {noformat} {noformat} 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 | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ {noformat} 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: {noformat} 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) {noformat} It shows that the query did use filesort. |
| Assignee | Sergei Petrunia [ psergey ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Workflow | MariaDB v2 [ 60304 ] | MariaDB v3 [ 67251 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Fix Version/s | 10.2.0 [ 20700 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 67251 ] | MariaDB v4 [ 148979 ] |