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
|
#
# 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);
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 type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge a,b a,b 3,3 NULL 1 Using intersect(a,b); Using where
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 PARTITIONS 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 Extra
1 SIMPLE t1 p1 index_merge a,b a,b 3,3 NULL 1 Using intersect(a,b); Using where
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 25
2 2 17
2 2 20
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 3 const 2 Using where
# Adding more fillers to get index_merge_union
INSERT INTO t1 VALUES (1,1,1+30), (1,1,1+33), (1,1,1+36), (1,1,1+39),
(1,1,1+42), (1,1,1+45), (1,1,1+48), (1,1,1+51), (1,1,1+54), (1,1,1+57),
(1,1,1+60), (1,1,1+63), (1,1,1+66), (1,1,1+69), (1,1,1+72), (1,1,1+75),
(1,1,1+78);
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 type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 4 Using union(b,a); Using where
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
a b c
1 2 13
2 2 17
2 2 20
1 2 24
2 2 25
2 1 28
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 24
1 2 13
2 2 25
2 1 28
2 2 17
2 2 20
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a,b NULL NULL NULL 27 Using where
Insert a few more rows to trigger sort_union
INSERT INTO t1 VALUES (1,1,1+81), (1,1,1+84), (1,1,1+87), (1,1,1+90),
(1,1,1+93);
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 type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge a,b b,a 3,3 NULL 4 Using sort_union(b,a); Using where
# 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
2 2 17
2 2 20
1 2 24
2 2 25
2 1 28
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 24
1 2 13
2 2 25
2 1 28
2 2 17
2 2 20
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a,b NULL NULL NULL 32 Using where
SET @@session.optimizer_switch = @old_opt_switch;
DROP TABLE t1;
|
