MySQL Bugs: #41136: ORDER BY + range access:EXPLAIN shows "Using MRR" while MRR is actually not used

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

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)

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)

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".

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

Thank you for the report.

Verified as described.

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

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

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)

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.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.