Bug #76755 Query execution plan / indexes problem
Submitted: 20 Apr 2015 12:35 Modified: 25 Apr 2015 13:21
Reporter: Eimantas Jatkonis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.24 OS:Any
Assigned to: CPU Architecture:Any

[20 Apr 2015 12:35] Eimantas Jatkonis
Description:
LEFT JOIN with "<" condition not using indexes.
5.6.24 version has problem, but 5.1.55 does not.

5.6.24 gives output
+----+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    5 | NULL                                           |
|  1 | PRIMARY     | numbers    | ALL   | PRIMARY       | NULL    | NULL    | NULL |   10 | Range checked for each record (index map: 0x1) |
|  2 | DERIVED     | numbers    | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where; Using index                       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+

5.1.55 gives output

+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    4 |                          |
|  1 | PRIMARY     | numbers    | index | PRIMARY       | PRIMARY | 4       | NULL |   10 | Using index              |
|  2 | DERIVED     | numbers    | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where; Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

In this case time difference is minimal, but with bigger table huge difference.

How to repeat:
use test;

create table numbers (i int not null primary key);
insert into numbers values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

explain
select *
from
(
  select i 
  from numbers 
  where i < 5
) AS a
left join numbers on numbers.i < a.i
;  

Suggested fix:
No fix or query modification found to solve problem.
[25 Apr 2015 13:21] MySQL Verification Team
Hello Eimantas Jatkonis,

Thank you for the report.

Thanks,
Umesh