1
DROP TABLE IF EXISTS t1;
20
Table Op Msg_type Msg_text
21
test.t1 analyze status OK
22
DROP TABLE IF EXISTS t2;
23
CREATE TABLE t2 LIKE t1;
24
INSERT INTO t2 SELECT * FROM t1;
26
Table Op Msg_type Msg_text
27
test.t2 analyze status Table is already up to date
28
EXPLAIN UPDATE t1 SET key6=7 WHERE key1=80 OR key4=49;
29
id select_type table type possible_keys key key_len ref rows Extra
30
1 SIMPLE t1 index_merge i1,i4 i1,i4 4,4 NULL # Using union(i1,i4); Using where
31
SELECT COUNT(*), SUM(key6) FROM t1 WHERE key1=80 OR key4=49;
34
UPDATE t1 SET key6=7 WHERE key1=80 OR key4=49;
35
SELECT COUNT(*), SUM(key6) FROM t1 WHERE key1=80 OR key4=49;
38
EXPLAIN UPDATE t1 SET key1=18 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
39
id select_type table type possible_keys key key_len ref rows Extra
40
1 SIMPLE t1 index_merge i2,i3,i5,i6 i3,i5,i2 4,5,4 NULL # Using union(intersect(i3,i5),i2); Using where
41
SELECT COUNT(*), SUM(key1) FROM t1 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
44
UPDATE t1 SET key1=18 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
45
SELECT COUNT(*), SUM(key1) FROM t1 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
48
EXPLAIN UPDATE t1 SET key3=18 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
49
id select_type table type possible_keys key key_len ref rows Extra
50
1 SIMPLE t1 index_merge i1,i2,i4,i5,i6 i4,i5 4,5 NULL # Using union(i4,i5); Using where
51
SELECT COUNT(*), SUM(key3) FROM t1 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
54
UPDATE t1 SET key3=18 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
55
SELECT COUNT(*), SUM(key3) FROM t1 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
58
EXPLAIN UPDATE t1 SET key8=64 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
59
id select_type table type possible_keys key key_len ref rows Extra
60
1 SIMPLE t1 index_merge i2,i3 i2,i3 4,4 NULL # Using union(i2,i3); Using where
61
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
64
UPDATE t1 SET key8=64 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
65
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
68
EXPLAIN UPDATE t1 SET key8=18 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
69
id select_type table type possible_keys key key_len ref rows Extra
70
1 SIMPLE t1 index_merge i1,i2,i3,i4,i5,i6 i5,i6 5,5 NULL # Using sort_union(i5,i6); Using where
71
SELECT COUNT(*), SUM(key8) FROM t1 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
74
UPDATE t1 SET key8=18 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
75
SELECT COUNT(*), SUM(key8) FROM t1 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
78
EXPLAIN UPDATE t1 SET key3=32 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
79
id select_type table type possible_keys key key_len ref rows Extra
80
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
81
SELECT COUNT(*), SUM(key3) FROM t1 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
84
UPDATE t1 SET key3=32 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
85
SELECT COUNT(*), SUM(key3) FROM t1 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
88
EXPLAIN UPDATE t1 SET key7=25 WHERE key1 IN (15,70) OR key2 IN (15,70);
89
id select_type table type possible_keys key key_len ref rows Extra
90
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
91
SELECT COUNT(*), SUM(key7) FROM t1 WHERE key1 IN (15,70) OR key2 IN (15,70);
94
UPDATE t1 SET key7=25 WHERE key1 IN (15,70) OR key2 IN (15,70);
95
SELECT COUNT(*), SUM(key7) FROM t1 WHERE key1 IN (15,70) OR key2 IN (15,70);
98
EXPLAIN UPDATE t1 SET key1=10 WHERE key5<5 OR key8<10 ORDER BY key1;
99
id select_type table type possible_keys key key_len ref rows Extra
100
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where; Using filesort
101
SELECT COUNT(*), SUM(key1) FROM t1 WHERE key5<5 OR key8<10 ORDER BY key1;
104
UPDATE t1 SET key1=10 WHERE key5<5 OR key8<10 ORDER BY key1;
105
SELECT COUNT(*), SUM(key1) FROM t1 WHERE key5<5 OR key8<10 ORDER BY key1;
108
EXPLAIN UPDATE t1 left join t2 ON (t2.key1=t2.key1) SET t1.key4=15 WHERE t1.key5=40 OR t1.key4=15;
109
id select_type table type possible_keys key key_len ref rows Extra
110
1 SIMPLE t1 index_merge i4,i5 i5,i4 5,4 NULL # Using union(i5,i4); Using where
111
1 SIMPLE t2 index NULL i1 4 NULL # Using where; Using index; Using join buffer (Block Nested Loop)
112
SELECT COUNT(*), SUM(key4) FROM t1;
115
UPDATE t1 left join t2 ON (t2.key1=t2.key1) SET t1.key4=15 WHERE t1.key5=40 OR t1.key4=15;
116
SELECT COUNT(*), SUM(key4) FROM t1;
119
EXPLAIN UPDATE t2 SET key7=7 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
120
id select_type table type possible_keys key key_len ref rows Extra
121
1 PRIMARY t2 index_merge i3,i4 i3,i4 4,4 NULL # Using union(i3,i4); Using where
122
2 SUBQUERY t1 index_merge i1,i5 i1,i5 4,5 NULL # Using union(i1,i5); Using where
123
SELECT COUNT(*), SUM(key7) FROM t2 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
126
UPDATE t2 SET key7=7 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
127
SELECT COUNT(*), SUM(key7) FROM t2 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
130
EXPLAIN UPDATE t2 SET key2=2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
131
id select_type table type possible_keys key key_len ref rows Extra
132
1 PRIMARY t2 index_merge i1,i4 i1,i4 4,4 NULL # Using union(i1,i4); Using where
133
3 SUBQUERY t1 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where
134
2 SUBQUERY t1 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where
135
SELECT COUNT(*), SUM(key2) FROM t2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
138
UPDATE t2 SET key2=2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
139
SELECT COUNT(*), SUM(key2) FROM t2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
142
EXPLAIN UPDATE t2 SET key3=20 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
143
id select_type table type possible_keys key key_len ref rows Extra
144
1 PRIMARY t2 ALL NULL NULL NULL NULL # NULL
145
2 SUBQUERY t1 index_merge i4,i6 i6,i4 5,4 NULL # Using sort_union(i6,i4); Using where
146
SELECT COUNT(*), SUM(key3) FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
149
UPDATE t2 SET key3=20 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
150
SELECT COUNT(*), SUM(key3) FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
153
EXPLAIN UPDATE t1 SET key8=50 WHERE key5=80 OR key6=49;
154
id select_type table type possible_keys key key_len ref rows Extra
155
1 SIMPLE t1 index_merge i5,i6 i5,i6 5,5 NULL # Using union(i5,i6); Using where
156
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key5=80 OR key6=49;
159
UPDATE t1 SET key8=50 WHERE key5=80 OR key6=49;
160
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key5=80 OR key6=49;
163
DROP TABLE IF EXISTS t1;
184
Table Op Msg_type Msg_text
185
test.t1 analyze status OK
186
DROP TABLE IF EXISTS t2;
187
CREATE TABLE t2 LIKE t1;
188
INSERT INTO t2 SELECT * FROM t1;
190
Table Op Msg_type Msg_text
191
test.t2 analyze status Table is already up to date
192
EXPLAIN UPDATE t1 SET key6=7 WHERE key1=80 OR key4=49;
193
id select_type table type possible_keys key key_len ref rows Extra
194
1 SIMPLE t1 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where
195
SELECT COUNT(*), SUM(key6) FROM t1 WHERE key1=80 OR key4=49;
198
UPDATE t1 SET key6=7 WHERE key1=80 OR key4=49;
199
SELECT COUNT(*), SUM(key6) FROM t1 WHERE key1=80 OR key4=49;
202
EXPLAIN UPDATE t1 SET key1=18 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
203
id select_type table type possible_keys key key_len ref rows Extra
204
1 SIMPLE t1 index_merge i2,i3,i5,i6 i3,i2 4,4 NULL # Using sort_union(i3,i2); Using where; Using temporary
205
SELECT COUNT(*), SUM(key1) FROM t1 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
208
UPDATE t1 SET key1=18 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
209
SELECT COUNT(*), SUM(key1) FROM t1 WHERE (key3=80 AND key5 IS NULL) OR (key2=49 AND key6 IS NULL);
212
EXPLAIN UPDATE t1 SET key3=18 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
213
id select_type table type possible_keys key key_len ref rows Extra
214
1 SIMPLE t1 index_merge i1,i2,i4,i5,i6 i4,i5 4,5 NULL # Using sort_union(i4,i5); Using where; Using temporary
215
SELECT COUNT(*), SUM(key3) FROM t1 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
218
UPDATE t1 SET key3=18 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
219
SELECT COUNT(*), SUM(key3) FROM t1 WHERE (key4=42 AND key6 IS NOT NULL) OR (key1>4 AND key5 IS NULL) AND key2<>50;
222
EXPLAIN UPDATE t1 SET key8=64 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
223
id select_type table type possible_keys key key_len ref rows Extra
224
1 SIMPLE t1 index_merge i2,i3 i2,i3 4,4 NULL # Using sort_union(i2,i3); Using where
225
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
228
UPDATE t1 SET key8=64 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
229
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key2=80 OR key3=1025 OR key4 <=> NULL;
232
EXPLAIN UPDATE t1 SET key8=18 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
233
id select_type table type possible_keys key key_len ref rows Extra
234
1 SIMPLE t1 index_merge i1,i2,i3,i4,i5,i6 i5,i6 5,5 NULL # Using sort_union(i5,i6); Using where
235
SELECT COUNT(*), SUM(key8) FROM t1 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
238
UPDATE t1 SET key8=18 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
239
SELECT COUNT(*), SUM(key8) FROM t1 WHERE (key1 < 3 OR key2 < 2) AND (key3 < 3 OR key4 < 3) AND (key5 < 2 OR key6 < 2);
242
EXPLAIN UPDATE t1 SET key3=32 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
245
SELECT COUNT(*), SUM(key3) FROM t1 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
248
UPDATE t1 SET key3=32 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
249
SELECT COUNT(*), SUM(key3) FROM t1 WHERE key1 between 25 AND 40 OR key2 between 15 AND 49;
252
EXPLAIN UPDATE t1 SET key7=25 WHERE key1 IN (15,70) OR key2 IN (15,70);
253
id select_type table type possible_keys key key_len ref rows Extra
254
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where
255
SELECT COUNT(*), SUM(key7) FROM t1 WHERE key1 IN (15,70) OR key2 IN (15,70);
258
UPDATE t1 SET key7=25 WHERE key1 IN (15,70) OR key2 IN (15,70);
259
SELECT COUNT(*), SUM(key7) FROM t1 WHERE key1 IN (15,70) OR key2 IN (15,70);
262
EXPLAIN UPDATE t1 SET key1=10 WHERE key5<5 OR key8<10 ORDER BY key1;
263
id select_type table type possible_keys key key_len ref rows Extra
264
1 SIMPLE t1 index_merge i5,i8 i5,i8 5,4 NULL # Using sort_union(i5,i8); Using where; Using filesort
265
SELECT COUNT(*), SUM(key1) FROM t1 WHERE key5<5 OR key8<10 ORDER BY key1;
268
UPDATE t1 SET key1=10 WHERE key5<5 OR key8<10 ORDER BY key1;
269
SELECT COUNT(*), SUM(key1) FROM t1 WHERE key5<5 OR key8<10 ORDER BY key1;
272
EXPLAIN UPDATE t1 left join t2 ON (t2.key1=t2.key1) SET t1.key4=15 WHERE t1.key5=40 OR t1.key4=15;
273
id select_type table type possible_keys key key_len ref rows Extra
274
1 SIMPLE t1 index_merge i4,i5 i5,i4 5,4 NULL # Using sort_union(i5,i4); Using where
275
1 SIMPLE t2 index NULL i1 8 NULL # Using where; Using index; Using join buffer (Block Nested Loop)
276
SELECT COUNT(*), SUM(key4) FROM t1;
279
UPDATE t1 left join t2 ON (t2.key1=t2.key1) SET t1.key4=15 WHERE t1.key5=40 OR t1.key4=15;
280
SELECT COUNT(*), SUM(key4) FROM t1;
283
EXPLAIN UPDATE t2 SET key7=7 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
284
id select_type table type possible_keys key key_len ref rows Extra
285
1 PRIMARY t2 index_merge i3,i4 i3,i4 4,4 NULL # Using sort_union(i3,i4); Using where
286
2 SUBQUERY t1 index_merge i1,i5 i1,i5 4,5 NULL # Using sort_union(i1,i5); Using where
287
SELECT COUNT(*), SUM(key7) FROM t2 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
290
UPDATE t2 SET key7=7 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
291
SELECT COUNT(*), SUM(key7) FROM t2 WHERE (key3=28 OR key4=10) AND key6 < ANY (SELECT key6 FROM t1 WHERE key1=10 OR key5=3);
294
EXPLAIN UPDATE t2 SET key2=2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
295
id select_type table type possible_keys key key_len ref rows Extra
296
1 PRIMARY t2 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where; Using temporary
297
3 SUBQUERY t1 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where
298
2 SUBQUERY t1 index_merge i1,i4 i1,i4 4,4 NULL # Using sort_union(i1,i4); Using where
299
SELECT COUNT(*), SUM(key2) FROM t2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
302
UPDATE t2 SET key2=2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
303
SELECT COUNT(*), SUM(key2) FROM t2 WHERE key1=25 OR key4=40 AND (key4 < SOME (SELECT key4 FROM t1 WHERE key1<5 OR key4<8) OR key1 < ANY (SELECT key1 FROM t1 WHERE key1<5 OR key4<7));
306
EXPLAIN UPDATE t2 SET key3=20 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
307
id select_type table type possible_keys key key_len ref rows Extra
308
1 PRIMARY t2 ALL NULL NULL NULL NULL # NULL
309
2 SUBQUERY t1 index_merge i4,i6 i6,i4 5,4 NULL # Using sort_union(i6,i4); Using where
310
SELECT COUNT(*), SUM(key3) FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
313
UPDATE t2 SET key3=20 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
314
SELECT COUNT(*), SUM(key3) FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE key6<3 OR key4<10);
317
EXPLAIN UPDATE t1 SET key8=50 WHERE key5=80 OR key6=49;
318
id select_type table type possible_keys key key_len ref rows Extra
319
1 SIMPLE t1 index_merge i5,i6 i5,i6 5,5 NULL # Using sort_union(i5,i6); Using where
320
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key5=80 OR key6=49;
323
UPDATE t1 SET key8=50 WHERE key5=80 OR key6=49;
324
SELECT COUNT(*), SUM(key8) FROM t1 WHERE key5=80 OR key6=49;