Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.35, 10.0.7
-
None
-
None
Description
Query plans produced by range optimizer depend on whether the condition has form "X < Y", or "Y >X".
Example:
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 + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
alter table one_k add key(a);
explain select * from ten, one_k where one_k.a < ten.a;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | one_k | ALL | a | NULL | NULL | NULL | 1148 | Range checked for each record (index map: 0x1) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
now, the same query with changed WHERE:
mysql> explain select * from ten, one_k where ten.a > one_k.a;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | one_k | index | a | a | 5 | NULL | 1148 | Using where; Using index; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
The problem can be only observed if there is a table field on the other side of the comparison. Besides "range checked for each record", the optimizer may miss regular plans:
create table t2 (a int primary key, b int);
insert into t2 select a,a from ten;
mysql> explain select * from t2, one_k where one_k.a < t2.b and t2.a=1;
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | SIMPLE | one_k | range | a | a | 5 | NULL | 1 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|
mysql> explain select * from t2, one_k where t2.b > one_k.a and t2.a=1;
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
| 1 | SIMPLE | one_k | index | a | a | 5 | NULL | 1148 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|