Details
Description
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 |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
Attachments
Issue Links
- relates to
-
MDEV-22191 Range access is not picked when index_merge_sort_union is turned off
- Closed
-
MDEV-22160 SIGSEGV in st_join_table::save_explain_data on SELECT
- Closed