Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.67
-
None
-
None
Description
The optimizer produces an expected execution plan for the query:
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020); |
MariaDB [test]> explain
|
-> select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020); |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 64 | | |
| 1 | SIMPLE | t1 | range | i1 | i1 | 4 | NULL | 78 | Using where | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |
While for the query:
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
the produced plan employs unnecessary "Range checked for each record":
MariaDB [test]> explain
|
-> select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+ |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 64 | | |
| 1 | SIMPLE | t1 | range | i2 | i1 | 4 | NULL | 78 | Range checked for each record (index map: 0x2) | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------- |
This results in an extremely poor performance on the second query.
In interesting is that for the second query for each record actually the same range scan of t1 is used, and this is exactly the scan chosen for the first query.
The problem can be reproduced with the following test case:
create table t1( key1 int not null, INDEX i1(key1) ); |
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); |
insert into t1 select key1+8 from t1; |
insert into t1 select key1+16 from t1; |
insert into t1 select key1+32 from t1; |
insert into t1 select key1+64 from t1; |
insert into t1 select key1+128 from t1; |
insert into t1 select key1+256 from t1; |
insert into t1 select key1+512 from t1; |
|
|
alter table t1 add key2 int not null, add index i2(key2); |
update t1 set key2=key1; |
analyze table t1; |
|
|
create table t2 (a int); |
insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); |
insert into t2 select a+16 from t2; |
insert into t2 select a+32 from t2; |
insert into t2 select a+64 from t2; |
|
|
explain
|
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020); |
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020); |
explain
|
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; |
select * from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; |
|
|
drop table t1,t2; |
The problem is reproducible in any release of MariaDB of 5.1 and in any release of the higher versions.
The problem can be observed in any release of MySQL 5.1/5.5/5.6 as well
I even successfully reproduced the problem in MySQL 4.1
Attachments
Issue Links
- relates to
-
-
- Closed
-