[MDEV-4168] EXPLAIN SELECT ... ORDER BY doesn't show "using MRR" even if it uses it - JIRA

Details

Type: Bug
Status: Open
Priority: Minor
Resolution: Unresolved
Affects Version/s: 5.3.12, 5.5.36, 10.0.11
Fix Version/s: 10.0
Component/s: None
Labels:
None

Description

The following testcase produces incorrect EXPLAIN output ("Using MRR" is missing). This is probably a long-known problem of wrong output of EXPLAIN .. ORDER BY. I am filing it, because 1) we now have SHOW EXPLAIN (and so, potential to fix it) and 2) it is also fixed in mysql-5.6

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 + 10*B.a + 100*C.a from ten A, ten B, ten C;

create table t1 (a int, b int, c int, filler blob);
insert into t1 select a,a,a, 'filler-data' from test.one_k;
alter table t1 add key(a,b);
explain select * from t1 force index(a) where a < 10000 order by b limit 10;
select * from t1 force index(a) where a < 10000 order by b limit 10;
set optimizer_switch='mrr_cost_based=off';

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Elena Stepanova added a comment - 2014-04-08 19:21

I'm not getting essentially different explain output between MySQL 5.6 and MariaDB 10.0 – neither on current versions (5.6.17 vs 10.0.10), nor on versions around the time the bug was reported (5.6.9 vs 10.0.1).
However, I do see some strange behavior in regard to Last_query_cost.
In any case, if there is a bug in there, apparently nothing has changed since the time it was reported, so I'm setting the affected versions (and reducing priority since it doesn't look really major). If, at the second thought, you decide there is no bug in here, please close it.

The results are for the tables and query from the description.

It looks like with mrr_cost_based=on, MRR is not used either for MariaDB or for MySQL.

set optimizer_switch='mrr=on,mrr_cost_based=on';

MariaDB 10.0:

+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |  998 | Using index condition; Using filesort |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1398.209000 |
+-----------------+-------------+
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_first       | 0     |
| Handler_read_key         | 1     |
| Handler_read_last        | 0     |
| Handler_read_next        | 1000  |
| Handler_read_prev        | 0     |
| Handler_read_rnd         | 10    |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 0     |
+--------------------------+-------+

MySQL 5.6:

+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |  998 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1398.209000 |
+-----------------+-------------+
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 11    |
| Handler_read_last     | 0     |
| Handler_read_next     | 1000  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 10    |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

With mrr=on and mrr_cost_based=off, it is used both in MySQL and MariaDB, only EXPLAIN shows it in different words ('Rowid-ordered scan' in MariaDB means the same as 'Using MRR' in MySQL, right?)
But what seems odd is that while for MySQL the query cost is different from the previous one where MRR was not used, for MariaDB it's still the same, even though status confirms the query was executed in a different way.

set optimizer_switch='mrr=on,mrr_cost_based=off';

MariaDB 10.0:

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                     |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |  998 | Using index condition; Rowid-ordered scan; Using filesort |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1398.209000 |
+-----------------+-------------+
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_first       | 0     |
| Handler_read_key         | 1     |
| Handler_read_last        | 0     |
| Handler_read_next        | 1000  |
| Handler_read_prev        | 0     |
| Handler_read_rnd         | 1010  |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 0     |
+--------------------------+-------+

MySQL 5.6:

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                            |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |  998 | Using index condition; Using MRR; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 505.175297 |
+-----------------+------------+
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1011  |
| Handler_read_last     | 0     |
| Handler_read_next     | 1000  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 1010  |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Show

Elena Stepanova added a comment - 2014-04-08 19:21 I'm not getting essentially different explain output between MySQL 5.6 and MariaDB 10.0 – neither on current versions (5.6.17 vs 10.0.10), nor on versions around the time the bug was reported (5.6.9 vs 10.0.1). However, I do see some strange behavior in regard to Last_query_cost. In any case, if there is a bug in there, apparently nothing has changed since the time it was reported, so I'm setting the affected versions (and reducing priority since it doesn't look really major). If, at the second thought, you decide there is no bug in here, please close it. The results are for the tables and query from the description. It looks like with mrr_cost_based=on , MRR is not used either for MariaDB or for MySQL. set optimizer_switch='mrr=on,mrr_cost_based=on'; MariaDB 10.0: +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 998 | Using index condition; Using filesort | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1398.209000 | +-----------------+-------------+ +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1000 | | Handler_read_prev | 0 | | Handler_read_rnd | 10 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+-------+ MySQL 5.6: +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 998 | Using index condition; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1398.209000 | +-----------------+-------------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 11 | | Handler_read_last | 0 | | Handler_read_next | 1000 | | Handler_read_prev | 0 | | Handler_read_rnd | 10 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ With mrr=on and mrr_cost_based=off , it is used both in MySQL and MariaDB, only EXPLAIN shows it in different words ('Rowid-ordered scan' in MariaDB means the same as 'Using MRR' in MySQL, right?) But what seems odd is that while for MySQL the query cost is different from the previous one where MRR was not used, for MariaDB it's still the same, even though status confirms the query was executed in a different way. set optimizer_switch='mrr=on,mrr_cost_based=off'; MariaDB 10.0: +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 998 | Using index condition; Rowid-ordered scan; Using filesort | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+ +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1398.209000 | +-----------------+-------------+ +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1000 | | Handler_read_prev | 0 | | Handler_read_rnd | 1010 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+-------+ MySQL 5.6: +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 998 | Using index condition; Using MRR; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 505.175297 | +-----------------+------------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1011 | | Handler_read_last | 0 | | Handler_read_next | 1000 | | Handler_read_prev | 0 | | Handler_read_rnd | 1010 | | Handler_read_rnd_next | 0 | +-----------------------+-------+

People

Assignee:

Unassigned

Reporter:

Sergei Petrunia

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2013-02-12 14:34

Updated:

2014-06-16 13:27

Agile

View on Board