Details
Description
For the table
CREATE TABLE t1 ( |
id int(10) unsigned NOT NULL AUTO_INCREMENT, |
p char(32) DEFAULT NULL, |
es tinyint(3) unsigned NOT NULL DEFAULT 0, |
er tinyint(3) unsigned NOT NULL DEFAULT 0, |
x mediumint(8) unsigned NOT NULL DEFAULT 0, |
PRIMARY KEY (id), |
INDEX es (es), |
INDEX x (x), |
INDEX er (er,x), |
INDEX p (p) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
populated with the statements:
insert into t1(es,er) select 0, 1 from seq_1_to_45; |
insert into t1(es,er) select 0, 2 from seq_1_to_49; |
insert into t1(es,er) select 0, 3 from seq_1_to_951; |
insert into t1(es,er) select 0, 3 from seq_1_to_1054; |
insert into t1(es,er) select 0, 6 from seq_1_to_25; |
insert into t1(es,er) select 0, 11 from seq_1_to_1; |
insert into t1(es,er) select 1, 1 from seq_1_to_45; |
insert into t1(es,er) select 1, 2 from seq_1_to_16; |
insert into t1(es,er) select 1, 3 from seq_1_to_511; |
insert into t1(es,er) select 1, 4 from seq_1_to_687; |
insert into t1(es,er) select 1, 6 from seq_1_to_50; |
insert into t1(es,er) select 1, 7 from seq_1_to_4; |
insert into t1(es,er) select 1, 11 from seq_1_to_1; |
insert into t1(es,er) select 2, 1 from seq_1_to_82; |
insert into t1(es,er) select 2, 2 from seq_1_to_82; |
insert into t1(es,er) select 2, 3 from seq_1_to_1626; |
insert into t1(es,er) select 2, 4 from seq_1_to_977; |
insert into t1(es,er) select 2, 6 from seq_1_to_33; |
insert into t1(es,er) select 2, 11 from seq_1_to_1; |
insert into t1(es,er) select 3, 1 from seq_1_to_245; |
insert into t1(es,er) select 3, 2 from seq_1_to_81; |
insert into t1(es,er) select 3, 3 from seq_1_to_852; |
insert into t1(es,er) select 3, 4 from seq_1_to_2243; |
insert into t1(es,er) select 3, 6 from seq_1_to_44; |
insert into t1(es,er) select 3, 11 from seq_1_to_1; |
insert into t1(es,er) select 4, 1 from seq_1_to_91; |
insert into t1(es,er) select 4, 2 from seq_1_to_83; |
insert into t1(es,er) select 4, 3 from seq_1_to_297; |
insert into t1(es,er) select 4, 4 from seq_1_to_2456; |
insert into t1(es,er) select 4, 6 from seq_1_to_19; |
insert into t1(es,er) select 4, 11 from seq_1_to_1; |
update t1 set p='foobar'; |
update t1 set x=0; |
the following execution plan is chosen for the query
SELECT * FROM t1 |
WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; |
if the optimizer switch index_merge_sort_intersection is set to 'on'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) limit 2;
|
+------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
|
| 1 | SIMPLE | t1 | index_merge | es,er,p | er,es | 0,1 | NULL | 1852 | 100.00 | Using sort_intersect(er,es); Using where |
|
+------+-------------+-------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+
|
As the range condition for the index 'er' (er!=4 OR er=4) is always true this index is of no use for index intersection.
Attachments
Issue Links
- causes
-
MDEV-26446 Crash on st_join_table::save_explain_data
- Closed