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.