1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
#
# Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK
#
CREATE TABLE t1
(a smallint,
b smallint,
c smallint,
KEY a (a),
KEY b (b)
) ENGINE=InnoDB
PARTITION BY HASH(c) PARTITIONS 3;
# c will simulate DB_ROW_ID + force to chosen partition.
# c is unique so we can identify rows in the test.
# Fillers to make the optimizer choose index_merge_intersect/union:
INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9);
# row N..N+3
INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21);
# More index matching rows for index_merge_intersect: N+4, N+5
INSERT INTO t1 VALUES (2,2,1+24);
INSERT INTO t1 VALUES (2,1,1+27);
CREATE TABLE t2 (a int primary key) ENGINE = InnoDB;
INSERT INTO t2 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
INSERT INTO t1 SELECT 1, 1, 97 FROM t2 LIMIT 10;
INSERT INTO t1 SELECT 2, 1, 98 FROM t2 LIMIT 4;
INSERT INTO t1 SELECT 1, 2, 99 FROM t2 LIMIT 4;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SET @old_opt_switch = @@session.optimizer_switch;
SET SESSION optimizer_switch="index_merge=on";
SET SESSION optimizer_switch="index_merge_intersection=on";
SET SESSION optimizer_switch="index_merge_sort_union=off";
SET SESSION optimizer_switch="index_merge_union=off";
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 index_merge a,b a,b 3,3 NULL 2 8.64 Using intersect(a,b); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` = 2) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
a b c
2 2 17
2 2 20
2 2 25
EXPLAIN SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p1 index_merge a,b a,b 3,3 NULL 1 30.00 Using intersect(a,b); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 2) and (`test`.`t1`.`a` = 2) and (`test`.`t1`.`c` in (13,25,28)))
SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
a b c
2 2 25
SET SESSION optimizer_switch="index_merge_intersection=off";
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
a b c
2 2 17
2 2 20
2 2 25
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 ref a,b a 3 const 8 3.57 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` = 2) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
# Adding more fillers to get index_merge_union
INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 32;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SET SESSION optimizer_switch="index_merge_union=on";
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 index_merge a,b b,a 3,3 NULL 16 11.11 Using union(b,a); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` = 2) or (`test`.`t1`.`a` = 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
a b c
1 2 13
1 2 24
1 2 99
1 2 99
1 2 99
1 2 99
2 1 28
2 1 98
2 1 98
2 1 98
2 1 98
2 2 17
2 2 20
2 2 25
SET SESSION optimizer_switch="index_merge_union=off";
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
a b c
1 2 13
1 2 24
1 2 99
1 2 99
1 2 99
1 2 99
2 1 28
2 1 98
2 1 98
2 1 98
2 1 98
2 2 17
2 2 20
2 2 25
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 ALL a,b NULL NULL NULL 60 7.22 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` = 2) or (`test`.`t1`.`a` = 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
Insert a few more rows to trigger sort_union
INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 48;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SET SESSION optimizer_switch="index_merge_sort_union=on";
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 index_merge a,b b,a 3,3 NULL 17 11.11 Using sort_union(b,a); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` >= 2) or (`test`.`t1`.`a` >= 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
# Not affected, added for completeness...
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
a b c
1 2 13
1 2 24
1 2 99
1 2 99
1 2 99
1 2 99
2 1 28
2 1 98
2 1 98
2 1 98
2 1 98
2 2 17
2 2 20
2 2 25
SET SESSION optimizer_switch="index_merge_sort_union=off";
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
a b c
1 2 13
1 2 24
1 2 99
1 2 99
1 2 99
1 2 99
2 1 28
2 1 98
2 1 98
2 1 98
2 1 98
2 2 17
2 2 20
2 2 25
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 p0,p1,p2 ALL a,b NULL NULL NULL 108 6.17 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`b` >= 2) or (`test`.`t1`.`a` >= 2)) and (`test`.`t1`.`c` > 0) and (`test`.`t1`.`c` < 100))
SET @@session.optimizer_switch = @old_opt_switch;
DROP TABLE t1, t2;
|
