|
The problem can be reproduced with the following test case:
create table t0 (a int);
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
insert into t0 select a+10 from t0;
|
insert into t0 select a+20 from t0;
|
insert into t0 select a+40 from t0;
|
insert into t0 select a+80 from t0;
|
insert into t0 select a+160 from t0;
|
delete from t0 where a > 300;
|
|
create table t1 (
|
f1 int, f2 int, f3 int, f4 int,
|
primary key (f1), key (f3), key(f4)
|
) engine=myisam;
|
insert into t1 select a+100, a+100, a+100, a+100 from t0;
|
insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
|
analyze table t1;
|
|
set optimizer_switch='index_merge_sort_union=off';
|
set optimizer_switch='index_merge_union=on';
|
|
explain
|
select * from t1
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
insert into t1 values (52,0,1,0),(53,0,1,0);
|
insert into t1 values (50,0,1,0),(51,0,1,0);
|
insert into t1 values (48,0,1,0),(49,0,1,0);
|
insert into t1 values (46,0,1,0),(47,0,1,0);
|
insert into t1 values (44,0,1,0),(45,0,1,0);
|
analyze table t1;
|
|
explain
|
select * from t1
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
The first explain shows that a ROR union index merge has been chosen that would select 3 rows.
MariaDB [test]> explain
|
-> select * from t1
|
-> where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
-> (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
|
| 1 | SIMPLE | t1 | index_merge | PRIMARY,f3,f4 | f3,PRIMARY,f3 | 5,4,5 | NULL | 3 | Using union(f3,PRIMARY,f3); Using where |
|
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
|
The second explain shows that a table scan was used to access all 309 rows of table t1 though a plan with a ROR union index merge that would access less than 15 rows still could be used.
MariaDB [test]> explain
|
-> select * from t1
|
-> where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
-> (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | PRIMARY,f3,f4 | NULL | NULL | NULL | 313 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
|