Bug #41136 ORDER BY + range access:EXPLAIN shows "Using MRR" while MRR is actually not used
Submitted: 30 Nov 2008 14:32 Modified: 23 Nov 2010 3:34
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 bzr OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[30 Nov 2008 14:32] Sergey Petrunya
Description:
If there's an ORDER BY query which is resolved using range access over index that matches the ordering, EXPLAIN will show "Using MRR".  

This can't be right as MRR can't guarantee any order for data it emits. Analysis reveals that MRR actually is not used, and EXPLAIN output is wrong.

How to repeat:
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, key(a));
insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; 

create table t1a as select * from t1 order by a desc;
alter table t1a add key(a);

# The physical order of records in the table is reverse to t1a.a order:
mysql> select * from t1a where a+1 < 20 ;
+------+------+
| a    | b    |
+------+------+
|   18 |   18 | 
|   17 |   17 | 
|   16 |   16 | 
|   15 |   15 | 
|   14 |   14 | 
|   13 |   13 | 
|   12 |   12 | 
|   11 |   11 | 
|   10 |   10 | 
|    9 |    9 | 
|    8 |    8 | 
|    7 |    7 | 
|    6 |    6 | 
|    5 |    5 | 
|    4 |    4 | 
|    3 |    3 | 
|    2 |    2 | 
|    1 |    1 | 
|    0 |    0 | 
+------+------+
19 rows in set (0.03 sec)

#
# This is the EXPLAIN that claims that MRR is used to resolve ORDER BY: 
#
mysql> explain select * from t1a where a < 20  order by a;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                            |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | t1a   | range | a             | a    | 5       | NULL |   20 | Using index condition; Using MRR | 
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
1 row in set (0.01 sec)

#
# Try the query and see that the rows come in the order of t1a.a, not in disk order. This means that 
# the MRR is actually not used and EXPLAIN output is wrong. 
#
mysql> select * from t1a where a < 20  order by a;
+------+------+
| a    | b    |
+------+------+
|    0 |    0 | 
|    1 |    1 | 
|    2 |    2 | 
|    3 |    3 | 
|    4 |    4 | 
|    5 |    5 | 
|    6 |    6 | 
|    7 |    7 | 
|    8 |    8 | 
|    9 |    9 | 
|   10 |   10 | 
|   11 |   11 | 
|   12 |   12 | 
|   13 |   13 | 
|   14 |   14 | 
|   15 |   15 | 
|   16 |   16 | 
|   17 |   17 | 
|   18 |   18 | 
|   19 |   19 | 
+------+------+
20 rows in set (0.01 sec)
[30 Nov 2008 16:24] Valeriy Kravchuk
Thank you for a problem report. Do you use some team tree or very recent man tree from bzr?

I can not repeat this with a main tree pulled on Nov. 25th:

mysql> explain select * from t1a where a < 20 order by a;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1a   | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 6.0.9-alpha-debug |
+-------------------+
1 row in set (0.07 sec)
[1 Jan 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[1 Jan 2009 15:55] Sergey Petrunya
I can repeat with 6.0.10-alpha-debug-log and latest mainline bk.

revno: 2717
committer: Sergey Petrunia <sergefp@mysql.com>
branch nick: mysql-6.0-look
timestamp: Mon 2008-12-29 20:07:04 +0300
message:
  Fix .bzr-mysql/default.conf
[13 Jan 2009 6:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[25 Jan 2009 14:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63998

2809 Sergey Petrunia	2009-01-25
      BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
      - Testcase
[26 Jan 2009 12:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64023

2810 Sergey Petrunia	2009-01-26
      BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
[2 Feb 2009 16:06] Bugs System
Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20090202090240-dlkxhmc1asrar5rl) (version source revid:sergefp@mysql.com-20090126112127-6lndjeo2ok4u75je) (merge vers: 6.0.10-alpha) (pib:6)
[11 Feb 2009 3:05] Paul DuBois
Noted in 6.0.10 changelog.

For a query that is executed using a range access method over an
index that matches the ordering and there is an ORDER BY clause,
EXPLAIN showed "Using MRR" even though Multi-Range Read access was not
used.
[16 Aug 2010 6:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:11] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:34] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.