Debian Codegraph @

mysql-test/suite/perfschema/include/idx_explain_test.inc

raw
1 ###########################################################################
2 # $select_column : Column to be fetched in SELECT #
3 # $column_count : Number of columns in the Index #
4 # $col1, $col2 ... : Columns' name in the Index #
5 # $col1_act, $col2_act ... : Actual existing values of columns in table #
6 # $table : Performance Schema table name #
7 ###########################################################################
8
9 --echo
10
11 IF ($column_count == 1)
12 {
13 --echo ====================================================================
14 --echo Testing index for columns $col1
15 --echo ====================================================================
16 --disable_warnings
17
18 --echo ############ Explain for Query ####################################
19 --replace_column 10 #
20 eval explain select $select_column
21 from $table
22 where $col1 = "impossible";
23
24 --echo ############ Explain for Query ####################################
25 --replace_column 10 #
26 eval explain select $select_column
27 from $table
28 where $col1 > "impossible";
29
30 --echo ############ Explain for Query ####################################
31 --replace_column 10 #
32 eval explain select $select_column
33 from $table
34 where $col1 < "2"; # 2 instead of impossible: to avoid constant folding away the where
35
36 --echo ############ Explain for Query ####################################
37 --replace_column 10 #
38 eval explain select $select_column
39 from $table
40 where $col1 = $col1_act;
41
42 --echo ############# Explain End #########################################
43 --enable_warnings
44
45 flush status;
46 let $count1 = query_get_value(show session status like "handler_read_key", Value, 1);
47
48 --replace_column 1 #
49 eval select $select_column
50 from $table
51 where $col1 = $col1_act;
52
53 let $count2 = query_get_value(show session status like "handler_read_key", Value, 1);
54
55 if ($count1 < $count2)
56 {
57 --echo OK: handler_read_key incremented
58 }
59 if ($count1 >= $count2)
60 {
61 --echo ***ERROR*** handler_read_key: Before= $count1, After= $count2
62 }
63 }
64
65 IF ($column_count == 2)
66 {
67 --echo ====================================================================
68 --echo Testing index for columns $col1, $col2
69 --echo ====================================================================
70 --disable_warnings
71
72 ############################
73 # For Col1 #
74 ############################
75
76 --echo ############ Explain for Query ####################################
77 --replace_column 10 #
78 eval explain select $select_column
79 from $table
80 where $col1 = "impossible";
81
82 --echo ############ Explain for Query ####################################
83 --replace_column 10 #
84 eval explain select $select_column
85 from $table
86 where $col1 > "impossible";
87
88 --echo ############ Explain for Query ####################################
89 --replace_column 10 #
90 eval explain select $select_column
91 from $table
92 where $col1 < "2"; # 2 instead of impossible: to avoid constant folding away the where
93
94 --echo ############ Explain for Query ####################################
95 --replace_column 10 #
96 eval explain select $select_column
97 from $table
98 where $col1 = $col1_act;
99
100 ############################
101 # For Col1 and col2 both #
102 ############################
103
104 --echo ############ Explain for Query ####################################
105 --replace_column 10 #
106 eval explain select $select_column
107 from $table
108 where $col1 = "impossible"
109 and $col2 = "impossible";
110
111 --echo ############ Explain for Query ####################################
112 --replace_column 10 #
113 eval explain select $select_column
114 from $table
115 where $col1 = $col1_act
116 and $col2 = "impossible";
117
118 --echo ############ Explain for Query ####################################
119 --replace_column 10 #
120 eval explain select $select_column
121 from $table
122 where $col1 = $col1_act
123 and $col2 > "impossible";
124
125 --echo ############ Explain for Query ####################################
126 --replace_column 10 #
127 eval explain select $select_column
128 from $table
129 where $col1 = $col1_act
130 and $col2 < "2"; # 2 instead of impossible: to avoid constant folding away the where
131
132 --echo ############ Explain for Query ####################################
133 --replace_column 10 #
134 eval explain select $select_column
135 from $table
136 where $col1 = $col1_act
137 and $col2 = $col2_act;
138
139 --echo ############# Explain End #########################################
140 --enable_warnings
141
142 flush status;
143 let $count1 = query_get_value(show session status like "handler_read_key", Value, 1);
144
145 --replace_column 1 #
146 eval select $select_column
147 from $table
148 where $col1 = $col1_act
149 and $col2 = $col2_act;
150
151 let $count2 = query_get_value(show session status like "handler_read_key", Value, 1);
152
153 if ($count1 < $count2)
154 {
155 --echo OK: handler_read_key incremented
156 }
157 if ($count1 >= $count2)
158 {
159 --echo ***ERROR*** handler_read_key: Before= $count1, After= $count2
160 }
161 }
162
163 IF ($column_count == 3)
164 {
165 --echo ====================================================================
166 --echo Testing index for columns $col1, $col2, $col3
167 --echo ====================================================================
168
169 --disable_warnings
170
171 ############################
172 # For Col1 #
173 ############################
174
175 --echo ############ Explain for Query ####################################
176 --replace_column 10 #
177 eval explain select $select_column
178 from $table
179 where $col1 = "impossible";
180
181 --echo ############ Explain for Query ####################################
182 --replace_column 10 #
183 eval explain select $select_column
184 from $table
185 where $col1 > "impossible";
186
187 --echo ############ Explain for Query ####################################
188 --replace_column 10 #
189 eval explain select $select_column
190 from $table
191 where $col1 < "2"; # 2 instead of impossible: to avoid constant folding away the where
192
193 --echo ############ Explain for Query ####################################
194 --replace_column 10 #
195 eval explain select $select_column
196 from $table
197 where $col1 = $col1_act;
198
199 ############################
200 # For Col1 col2 #
201 ############################
202
203 --echo ############ Explain for Query ####################################
204 --replace_column 10 #
205 eval explain select $select_column
206 from $table
207 where $col1 = "impossible"
208 and $col2 = "impossible";
209
210 --echo ############ Explain for Query ####################################
211 --replace_column 10 #
212 eval explain select $select_column
213 from $table
214 where $col1 = $col1_act
215 and $col2 = "impossible";
216
217 --echo ############ Explain for Query ####################################
218 --replace_column 10 #
219 eval explain select $select_column
220 from $table
221 where $col1 = $col1_act
222 and $col2 > "impossible";
223
224 --echo ############ Explain for Query ####################################
225 --replace_column 10 #
226 eval explain select $select_column
227 from $table
228 where $col1 = $col1_act
229 and $col2 < "2"; # 2 instead of impossible: to avoid constant folding away the where
230
231 --echo ############ Explain for Query ####################################
232 --replace_column 10 #
233 eval explain select $select_column
234 from $table
235 where $col1 = $col1_act
236 and $col2 = $col2_act;
237
238 ############################
239 # For Col1 col2 col3 #
240 ############################
241
242 --echo ############ Explain for Query ####################################
243 --replace_column 10 #
244 eval explain select $select_column
245 from $table
246 where $col1 = "impossible"
247 and $col2 = "impossible"
248 and $col3 = "impossible";
249
250 --echo ############ Explain for Query ####################################
251 --replace_column 10 #
252 eval explain select $select_column
253 from $table
254 where $col1 = $col1_act
255 and $col2 = $col2_act
256 and $col3 = "impossible";
257
258 --echo ############ Explain for Query ####################################
259 --replace_column 10 #
260 eval explain select $select_column
261 from $table
262 where $col1 = $col1_act
263 and $col2 = $col2_act
264 and $col3 > "impossible";
265
266 --echo ############ Explain for Query ####################################
267 --replace_column 10 #
268 eval explain select $select_column
269 from $table
270 where $col1 = $col1_act
271 and $col2 = $col2_act
272 and $col3 < "2"; # 2 instead of impossible: to avoid constant folding away the where
273
274 --echo ############ Explain for Query ####################################
275 --replace_column 10 #
276 eval explain select $select_column
277 from $table
278 where $col1 = $col1_act
279 and $col2 = $col2_act
280 and $col3 = $col3_act;
281
282 --echo ############# Explain End #########################################
283 --enable_warnings
284
285 flush status;
286 let $count1 = query_get_value(show session status like "handler_read_key", Value, 1);
287
288 --replace_column 1 #
289 eval select $select_column
290 from $table
291 where $col1 = $col1_act
292 and $col2 = $col2_act
293 and $col3 = $col3_act;
294
295 let $count2 = query_get_value(show session status like "handler_read_key", Value, 1);
296
297 if ($count1 < $count2)
298 {
299 --echo OK: handler_read_key incremented
300 }
301 if ($count1 >= $count2)
302 {
303 --echo ***ERROR*** handler_read_key: Before= $count1, After= $count2
304 }
305 }
306
307 IF ($column_count == 4)
308 {
309 --echo ====================================================================
310 --echo Testing index for columns $col1, $col2, $col3, $col4
311 --echo ====================================================================
312 --disable_warnings
313
314 ############################
315 # For Col1 #
316 ############################
317
318 --echo ############ Explain for Query ####################################
319 --replace_column 10 #
320 eval explain select $select_column
321 from $table
322 where $col1 = "impossible";
323
324 --echo ############ Explain for Query ####################################
325 --replace_column 10 #
326 eval explain select $select_column
327 from $table
328 where $col1 > "impossible";
329
330 --echo ############ Explain for Query ####################################
331 --replace_column 10 #
332 eval explain select $select_column
333 from $table
334 where $col1 < "2"; # 2 instead of impossible: to avoid constant folding away the where
335
336 --echo ############ Explain for Query ####################################
337 --replace_column 10 #
338 eval explain select $select_column
339 from $table
340 where $col1 = $col1_act;
341
342 ############################
343 # For Col1 col2 #
344 ############################
345
346 --echo ############ Explain for Query ####################################
347 --replace_column 10 #
348 eval explain select $select_column
349 from $table
350 where $col1 = "impossible"
351 and $col2 = "impossible";
352
353 --echo ############ Explain for Query ####################################
354 --replace_column 10 #
355 eval explain select $select_column
356 from $table
357 where $col1 = $col1_act
358 and $col2 = "impossible";
359
360 --echo ############ Explain for Query ####################################
361 --replace_column 10 #
362 eval explain select $select_column
363 from $table
364 where $col1 = $col1_act
365 and $col2 > "impossible";
366
367 --echo ############ Explain for Query ####################################
368 --replace_column 10 #
369 eval explain select $select_column
370 from $table
371 where $col1 = $col1_act
372 and $col2 < "2"; # 2 instead of impossible: to avoid constant folding away the where
373
374 --echo ############ Explain for Query ####################################
375 --replace_column 10 #
376 eval explain select $select_column
377 from $table
378 where $col1 = $col1_act
379 and $col2 = $col2_act;
380
381 ############################
382 # For Col1 col2 col3 #
383 ############################
384
385 --echo ############ Explain for Query ####################################
386 --replace_column 10 #
387 eval explain select $select_column
388 from $table
389 where $col1 = "impossible"
390 and $col2 = "impossible"
391 and $col3 = "impossible";
392
393 --echo ############ Explain for Query ####################################
394 --replace_column 10 #
395 eval explain select $select_column
396 from $table
397 where $col1 = $col1_act
398 and $col2 = $col2_act
399 and $col3 = "impossible";
400
401 --echo ############ Explain for Query ####################################
402 --replace_column 10 #
403 eval explain select $select_column
404 from $table
405 where $col1 = $col1_act
406 and $col2 = $col2_act
407 and $col3 > "impossible";
408
409 --echo ############ Explain for Query ####################################
410 --replace_column 10 #
411 eval explain select $select_column
412 from $table
413 where $col1 = $col1_act
414 and $col2 = $col2_act
415 and $col3 < "2"; # 2 instead of impossible: to avoid constant folding away the where
416
417 --echo ############ Explain for Query ####################################
418 --replace_column 10 #
419 eval explain select $select_column
420 from $table
421 where $col1 = $col1_act
422 and $col2 = $col2_act
423 and $col3 = $col3_act;
424
425 ############################
426 # For Col1 col2 col3 col4 #
427 ############################
428
429 --echo ############ Explain for Query ####################################
430 --replace_column 10 #
431 eval explain select $select_column
432 from $table
433 where $col1 = "impossible"
434 and $col2 = "impossible"
435 and $col3 = "impossible"
436 and $col4 = "impossible";
437
438 --echo ############ Explain for Query ####################################
439 --replace_column 10 #
440 eval explain select $select_column
441 from $table
442 where $col1 = $col1_act
443 and $col2 = $col2_act
444 and $col3 = $col3_act
445 and $col4 = "impossible";
446
447 --echo ############ Explain for Query ####################################
448 --replace_column 10 #
449 eval explain select $select_column
450 from $table
451 where $col1 = $col1_act
452 and $col2 = $col2_act
453 and $col3 = $col3_act
454 and $col4 > "impossible";
455
456 --echo ############ Explain for Query ####################################
457 --replace_column 10 #
458 eval explain select $select_column
459 from $table
460 where $col1 = $col1_act
461 and $col2 = $col2_act
462 and $col3 = $col3_act
463 and $col4 < "impossible";
464
465 --echo ############ Explain for Query ####################################
466 --replace_column 10 #
467 eval explain select $select_column
468 from $table
469 where $col1 = $col1_act
470 and $col2 = $col2_act
471 and $col3 = $col3_act
472 and $col4 = $col4_act;
473
474 --echo ############# Explain End #########################################
475 --enable_warnings
476
477 flush status;
478 let $count1 = query_get_value(show session status like "handler_read_key", Value, 1);
479
480 --replace_column 1 #
481 eval select $select_column
482 from $table
483 where $col1 = $col1_act
484 and $col2 = $col2_act
485 and $col3 = $col3_act
486 and $col4 = $col4_act;
487
488 let $count2 = query_get_value(show session status like "handler_read_key", Value, 1);
489
490 if ($count1 < $count2)
491 {
492 --echo OK: handler_read_key incremented
493 }
494 if ($count1 >= $count2)
495 {
496 --echo ***ERROR*** handler_read_key: Before= $count1, After= $count2
497 }
498 }
499
500 IF ($column_count == 5)
501 {
502 --echo Testing index for columns $col1, $col2, $col3, $col4, $col5
503 }