2
# index_merge tests for statements using intersect algorithm
6
DROP TABLE IF EXISTS t1,t2;
10
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
24
# Inserting a lot of rows inorder to enable index_merge intersect
26
INSERT INTO t1(a,b,c,d) VALUES
27
( RAND()*5, RAND()*5, RAND()*5, RAND()*5 );
32
INSERT INTO t1(a,b,c,d) SELECT 6,6,6,6 FROM t1;
36
INSERT INTO t1(a,b,c,d) SELECT 6, RAND()*5, RAND()*5,
37
RAND()*5 FROM t1 LIMIT 3;
38
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, 6, RAND()*5,
39
RAND()*5 FROM t1 LIMIT 3;
40
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5, 6,
41
RAND()*5 FROM t1 LIMIT 3;
42
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5,
43
RAND()*5, 6 FROM t1 LIMIT 3;
48
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5,
49
RAND()*5, RAND()*5 FROM t1;
55
# The following statement analyzes and
56
# stores the key distribution for a table.
60
# DELETEs are not included as index merge intersection
61
# is disabled for DELETE statements.
65
--let $query = WHERE b=6 AND c=6
66
--replace_result "idx_c,idx_b" "idx_b,idx_c"
68
--eval EXPLAIN UPDATE t1 SET a=2 $query
69
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
70
--eval UPDATE t1 SET a=2 $query
71
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
73
--let $query = WHERE b=6 AND c=6 AND d=6
74
--replace_result idx_b idx_x idx_c idx_x idx_d idx_x
76
--eval EXPLAIN UPDATE t1 SET a=2 $query
77
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
78
--eval UPDATE t1 SET a=2 $query
79
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
81
--let $query = WHERE d=6 AND a IS NOT NULL AND b=6
82
--replace_result "idx_d,idx_b" "idx_b,idx_d"
84
--eval EXPLAIN UPDATE t1 SET c=6 $query
85
--eval SELECT COUNT(*), SUM(c) FROM t1 $query
86
--eval UPDATE t1 SET c=6 $query
87
--eval SELECT COUNT(*), SUM(c) FROM t1 $query
89
--let $query = WHERE d=6 AND a=6 AND c <> 6
91
--eval EXPLAIN UPDATE t1 SET b=0 $query
92
--eval SELECT COUNT(*), SUM(b) FROM t1 $query
93
--eval UPDATE t1 SET b=0 $query
94
--eval SELECT COUNT(*), SUM(b) FROM t1 $query
96
--let $query = WHERE d=6 AND a=6 AND c IN (1,2,3,4,5)
98
--eval EXPLAIN UPDATE t1 SET a=100 $query
99
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
100
--eval UPDATE t1 SET a=100 $query
101
--eval SELECT COUNT(*), SUM(a) FROM t1 $query
103
# uses range scan instead of index_merge
105
--let $query = WHERE a=5 AND b=4 AND d<3
107
--eval EXPLAIN UPDATE t1 SET a=2 $query
108
--eval UPDATE t1 SET a=2 $query
110
# Any range condition over a primary key of an InnoDB table.
113
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
125
INSERT INTO t2 SELECT * FROM t1;
128
--let $query = WHERE pk<2492 AND d=1
130
--eval EXPLAIN UPDATE t2 SET a=2 $query
131
--eval UPDATE t2 SET a=2 $query
133
# 2. REPLACE and INSERT
143
INSERT INTO t3(a,b,c,d,e) VALUES (3890,3890,3890,3890,3890);
144
INSERT INTO t3(a,b,c,d,e) VALUES (4000,4000,4000,4000,4000);
146
--let $query = INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6
147
--replace_result "idx_c,idx_b" "idx_b,idx_c"
149
--eval EXPLAIN REPLACE $query
150
--eval SELECT COUNT(*) FROM t3
151
--eval REPLACE $query
152
--eval SELECT COUNT(*) FROM t3
154
--let $query = INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6
156
--replace_result "idx_d,idx_b" "idx_b,idx_d"
157
--eval EXPLAIN INSERT $query
158
--eval SELECT COUNT(*) FROM t3
160
--eval SELECT COUNT(*) FROM t3
162
# Test case for multi column set-up.
165
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
175
INSERT INTO t4 SELECT * FROM t1;
177
# The following statement analyzes and
178
# stores the key distribution for a table.
182
--let $query = WHERE b=6 AND c=6 AND d=6
183
--replace_result idx_b idx_x idx_c idx_x idx_d idx_x
185
--eval EXPLAIN UPDATE t4 SET a=2 $query
186
--eval SELECT COUNT(*), SUM(a) FROM t4 $query
187
--eval UPDATE t4 SET a=2 $query
188
--eval SELECT COUNT(*), SUM(a) FROM t4 $query
190
DROP TABLE t1,t2,t3,t4;
193
# end of test cases for intersect index_merge optimization technique