|
For the following table:
create table t2(
|
d int, e int, key idx1(d), key idx2(e), f varchar(32)
|
) engine=myisam;
|
insert into t2 values
|
(4,5,'a'), (7,8,'b'), (4,3,'c'), (1,2,'d'), (2,1,'e'), (5,5,'f'),
|
(2,3,'g'), (7,7,'h'), (3,3,'i'), (3,2,'j'), (2,2,'k'), (5,5,'l'),
|
(4,5,'aa'), (7,8,'bb'), (4,3,'cc'), (1,2,'dd'), (2,1,'ee'), (5,5,'ff'),
|
(2,3,'gg'), (7,7,'hh'), (3,3,'ii'), (3,2,'jj'), (2,2,'kk'), (5,5,'ll'),
|
(4,5,'aaa'), (7,8,'bbb'), (4,3,'ccc'), (1,2,'ddd'), (2,1,'eee'), (5,5,'fff'),
|
(2,3,'ggg'), (7,7,'hhh'), (3,3,'iii'), (3,2,'jjj'), (2,2,'kkk'), (5,5,'lll'),
|
(14,15,'a'), (17,18,'b'), (14,13,'c'), (11,12,'d'), (12,11,'e'), (15,15,'f'),
|
(12,13,'g'), (17,17,'h'), (13,13,'i'), (13,12,'j'), (12,12,'k'), (15,15,'l'),
|
(24,25,'a'), (27,28,'b'), (24,23,'c'), (21,22,'d'), (22,21,'e'), (25,25,'f'),
|
(22,23,'g'), (27,27,'h'), (23,23,'i'), (23,22,'j'), (22,22,'k'), (25,25,'l'),
|
(34,35,'a'), (37,38,'b'), (34,33,'c'), (31,32,'d'), (32,31,'e'), (35,35,'f'),
|
(32,33,'g'), (37,37,'h'), (33,33,'i'), (33,32,'j'), (32,32,'k'), (35,35,'l'),
|
(44,45,'a'), (47,48,'b'), (44,43,'c'), (41,42,'d'), (42,41,'e'), (45,45,'f'),
|
(42,43,'g'), (47,47,'h'), (43,43,'i'), (43,42,'j'), (42,42,'k'), (45,45,'l');
|
this query
select * from t2 where d=3 and e=3 or d=7 and e=7;
|
is executed by the plan that utilizes index merge:
MariaDB [test]> explain select * from t2 where d=3 and e=3 or d=7 and e=7;
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
| 1 | SIMPLE | t2 | index_merge | idx1,idx2 | idx1,idx2 | 5,5 | NULL | 9 | Using union(idx1,idx2); Using where |
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
If we prohibit to use index merge
MariaDB [test]> set optimizer_switch='index_merge=off';
|
then we see that there is a range scan over the index idx1 used by the index merge.
MariaDB [test]> explain select * from t2 where d=3 and e=3 or d=7 and e=7;
-----
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
-----
| 1 |
SIMPLE |
t2 |
range |
idx1,idx2 |
idx1 |
5 |
NULL |
12 |
Using index condition; Using where |
-----
This range scan cannot read more records than the index merge, though the explain outputs
mislead us here..
|