Debian Codegraph @

mysql-test/suite/opt_trace/r/skip_scan.result

raw
1 CREATE TABLE t (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY(a, b, c, d), KEY(b, d)) ENGINE=innodb;
2 # Since ANALYZE TABLE only reads a subset of the data, the statistics for
3 # table t depends on the row order. And since the INSERT INTO ... SELECT
4 # may be executed using different execution plans, we've added ORDER BY
5 # to ensure that we rows has the same order every time. If not, the
6 # estimated number of rows in EXPLAIN may change on different platforms.
7 ANALYZE TABLE t;
8 Table Op Msg_type Msg_text
9 test.t analyze status OK
10 SET optimizer_switch = 'skip_scan=on';
11 SET optimizer_trace="enabled=on,one_line=off";
12 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND d >= 98 AND e = 2;
13 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
14 1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 3.33 Using where
15 Warnings:
16 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`e` = 2) and (`test`.`t`.`a` = 2) and (`test`.`t`.`d` >= 98))
17 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%query_references_nonkey_column%';
18 count(*)
19 1
20 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR b = 2 AND d >= 98;
21 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
22 1 SIMPLE t NULL index_merge PRIMARY,b b,PRIMARY 8,4 NULL 251 100.00 Using sort_union(b,PRIMARY); Using where
23 Warnings:
24 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) or ((`test`.`t`.`b` = 2) and (`test`.`t`.`d` >= 98)))
25 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
26 count(*)
27 1
28 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR b = 2 OR d >= 98;
29 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
30 1 SIMPLE t NULL index PRIMARY,b b 8 NULL 2500 52.00 Using where; Using index
31 Warnings:
32 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) or (`test`.`t`.`b` = 2) or (`test`.`t`.`d` >= 98))
33 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
34 count(*)
35 1
36 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 5 OR d >= 98;
37 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
38 1 SIMPLE t NULL index PRIMARY b 8 NULL 2500 40.00 Using where; Using index
39 Warnings:
40 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) or (`test`.`t`.`d` >= 98))
41 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%disjuntive_predicate_present%';
42 count(*)
43 1
44 EXPLAIN SELECT a, b, c, d FROM t WHERE ((a = 5 AND b = 2) OR a = 2) and d >= 98;
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE t NULL range PRIMARY,b PRIMARY 8 NULL 300 33.33 Using where; Using index
47 Warnings:
48 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((((`test`.`t`.`b` = 2) and (`test`.`t`.`a` = 5)) or (`test`.`t`.`a` = 2)) and (`test`.`t`.`d` >= 98))
49 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%keypart_in_disjunctive_query%';
50 count(*)
51 1
52 EXPLAIN SELECT a, b, c, d FROM t WHERE a > 2 AND d >= 98;
53 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
54 1 SIMPLE t NULL range PRIMARY,b b 8 NULL 833 80.00 Using where; Using index for skip scan
55 Warnings:
56 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` > 2) and (`test`.`t`.`d` >= 98))
57 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%prefix_not_const_equality%';
58 count(*)
59 1
60 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND (d >= 98 OR d < 2);
61 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE t NULL ref PRIMARY PRIMARY 4 const 250 55.55 Using where; Using index
63 Warnings:
64 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 2) and ((`test`.`t`.`d` >= 98) or (`test`.`t`.`d` < 2)))
65 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%range_predicate_too_complex%';
66 count(*)
67 1
68 EXPLAIN SELECT a, b, c, d FROM t WHERE a = 2 AND b = 2;
69 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
70 1 SIMPLE t NULL ref PRIMARY,b PRIMARY 8 const,const 50 100.00 Using index
71 Warnings:
72 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`b` = 2) and (`test`.`t`.`a` = 2))
73 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%no_range_predicate%';
74 count(*)
75 1
76 EXPLAIN SELECT a, b, c, d FROM t WHERE (a < 1 OR a = 4 OR a = 5) AND b IN (1, 2, 3) AND d >= 98;
77 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
78 1 SIMPLE t NULL range PRIMARY,b b 8 NULL 3 46.00 Using where; Using index
79 Warnings:
80 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where (((`test`.`t`.`a` < 1) or (`test`.`t`.`a` = 4) or (`test`.`t`.`a` = 5)) and (`test`.`t`.`b` in (1,2,3)) and (`test`.`t`.`d` >= 98))
81 SELECT count(*) FROM information_schema.optimizer_trace WHERE trace LIKE '%prefix_not_const_equality%';
82 count(*)
83 1
84 EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98;
85 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
86 1 SIMPLE t NULL range PRIMARY,b b 12 NULL 3 50.00 Using where; Using index
87 Warnings:
88 Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` in (1,2,3,4,5)) and (`test`.`t`.`b` in (1,2,3)) and (`test`.`t`.`d` >= 98))
89 SELECT * FROM information_schema.optimizer_trace;
90 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
91 EXPLAIN SELECT a, b, c, d FROM t WHERE a IN (1, 2, 3, 4, 5) AND b IN (1, 2, 3) AND d >= 98 {
92 "steps": [
93 {
94 "join_preparation": {
95 "select#": 1,
96 "steps": [
97 {
98 "IN_uses_bisection": true
99 },
100 {
101 "IN_uses_bisection": true
102 },
103 {
104 "expanded_query": "/* select#1 */ select `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `t` where ((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
105 }
106 ]
107 }
108 },
109 {
110 "join_optimization": {
111 "select#": 1,
112 "steps": [
113 {
114 "condition_processing": {
115 "condition": "WHERE",
116 "original_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))",
117 "steps": [
118 {
119 "transformation": "equality_propagation",
120 "resulting_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
121 },
122 {
123 "transformation": "constant_propagation",
124 "resulting_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
125 },
126 {
127 "transformation": "trivial_condition_removal",
128 "resulting_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
129 }
130 ]
131 }
132 },
133 {
134 "substitute_generated_columns": {
135 }
136 },
137 {
138 "table_dependencies": [
139 {
140 "table": "`t`",
141 "row_may_be_null": false,
142 "map_bit": 0,
143 "depends_on_map_bits": [
144 ]
145 }
146 ]
147 },
148 {
149 "ref_optimizer_key_uses": [
150 ]
151 },
152 {
153 "rows_estimation": [
154 {
155 "table": "`t`",
156 "range_analysis": {
157 "table_scan": {
158 "rows": 2500,
159 "cost": 254.1
160 },
161 "potential_range_indexes": [
162 {
163 "index": "PRIMARY",
164 "usable": true,
165 "key_parts": [
166 "a",
167 "b",
168 "c",
169 "d"
170 ]
171 },
172 {
173 "index": "b",
174 "usable": true,
175 "key_parts": [
176 "b",
177 "d",
178 "a",
179 "c"
180 ]
181 }
182 ],
183 "best_covering_index_scan": {
184 "index": "b",
185 "cost": 252.077,
186 "chosen": true
187 },
188 "setup_range_conditions": [
189 ],
190 "group_index_range": {
191 "chosen": false,
192 "cause": "not_group_by_or_distinct"
193 },
194 "skip_scan_range": {
195 "potential_skip_scan_indexes": [
196 {
197 "index": "PRIMARY",
198 "tree_travel_cost": 0.6,
199 "num_groups": 76,
200 "rows": 249,
201 "cost": 129.041
202 },
203 {
204 "index": "b",
205 "usable": false,
206 "cause": "prefix_not_const_equality"
207 }
208 ]
209 },
210 "best_skip_scan_summary": {
211 "type": "skip_scan",
212 "index": "PRIMARY",
213 "key_parts_used_for_access": [
214 "a",
215 "b",
216 "c",
217 "d"
218 ],
219 "prefix ranges": [
220 "a = 1 AND b = 1",
221 "a = 1 AND b = 2",
222 "a = 1 AND b = 3",
223 "a = 2 AND b = 1",
224 "a = 2 AND b = 2",
225 "a = 2 AND b = 3",
226 "a = 3 AND b = 1",
227 "a = 3 AND b = 2",
228 "a = 3 AND b = 3",
229 "a = 4 AND b = 1",
230 "a = 4 AND b = 2",
231 "a = 4 AND b = 3",
232 "a = 5 AND b = 1",
233 "a = 5 AND b = 2",
234 "a = 5 AND b = 3"
235 ],
236 "range": [
237 "98 <= d"
238 ],
239 "chosen": true
240 },
241 "analyzing_range_alternatives": {
242 "range_scan_alternatives": [
243 {
244 "index": "PRIMARY",
245 "ranges": [
246 "a = 1 AND b = 1",
247 "a = 1 AND b = 2",
248 "a = 1 AND b = 3",
249 "a = 2 AND b = 1",
250 "a = 2 AND b = 2",
251 "a = 2 AND b = 3",
252 "a = 3 AND b = 1",
253 "a = 3 AND b = 2",
254 "a = 3 AND b = 3",
255 "a = 4 AND b = 1",
256 "a = 4 AND b = 2",
257 "a = 4 AND b = 3",
258 "a = 5 AND b = 1",
259 "a = 5 AND b = 2",
260 "a = 5 AND b = 3"
261 ],
262 "index_dives_for_eq_ranges": true,
263 "rowid_ordered": true,
264 "using_mrr": false,
265 "index_only": true,
266 "in_memory": 1,
267 "rows": 750,
268 "cost": 79.0151,
269 "chosen": true
270 },
271 {
272 "index": "b",
273 "ranges": [
274 "b = 1 AND 98 <= d",
275 "b = 2 AND 98 <= d",
276 "b = 3 AND 98 <= d"
277 ],
278 "index_dives_for_eq_ranges": true,
279 "rowid_ordered": false,
280 "using_mrr": false,
281 "index_only": true,
282 "in_memory": 1,
283 "rows": 3,
284 "cost": 0.561462,
285 "chosen": true
286 }
287 ],
288 "analyzing_roworder_intersect": {
289 "usable": false,
290 "cause": "too_few_roworder_scans"
291 }
292 },
293 "chosen_range_access_summary": {
294 "range_access_plan": {
295 "type": "range_scan",
296 "index": "b",
297 "rows": 3,
298 "ranges": [
299 "b = 1 AND 98 <= d AND 1 <= a",
300 "b = 2 AND 98 <= d AND 1 <= a",
301 "b = 3 AND 98 <= d AND 1 <= a"
302 ]
303 },
304 "rows_for_plan": 3,
305 "cost_for_plan": 0.561462,
306 "chosen": true
307 }
308 }
309 }
310 ]
311 },
312 {
313 "considered_execution_plans": [
314 {
315 "plan_prefix": [
316 ],
317 "table": "`t`",
318 "best_access_path": {
319 "considered_access_paths": [
320 {
321 "rows_to_scan": 3,
322 "filtering_effect": [
323 ],
324 "final_filtering_effect": 0.5,
325 "access_type": "range",
326 "range_details": {
327 "used_index": "b"
328 },
329 "resulting_rows": 1.5,
330 "cost": 0.861462,
331 "chosen": true
332 }
333 ]
334 },
335 "condition_filtering_pct": 100,
336 "rows_for_plan": 1.5,
337 "cost_for_plan": 0.861462,
338 "chosen": true
339 }
340 ]
341 },
342 {
343 "attaching_conditions_to_tables": {
344 "original_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))",
345 "attached_conditions_computation": [
346 ],
347 "attached_conditions_summary": [
348 {
349 "table": "`t`",
350 "attached": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
351 }
352 ]
353 }
354 },
355 {
356 "finalizing_table_conditions": [
357 {
358 "table": "`t`",
359 "original_table_condition": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))",
360 "final_table_condition ": "((`t`.`a` in (1,2,3,4,5)) and (`t`.`b` in (1,2,3)) and (`t`.`d` >= 98))"
361 }
362 ]
363 },
364 {
365 "refine_plan": [
366 {
367 "table": "`t`"
368 }
369 ]
370 }
371 ]
372 }
373 },
374 {
375 "join_explain": {
376 "select#": 1,
377 "steps": [
378 ]
379 }
380 }
381 ]
382 } 0 0
383 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 4 AND d < 3;
384 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
385 1 SIMPLE t NULL range PRIMARY,b PRIMARY 12 NULL 83 33.33 Using where; Using index for skip scan
386 Warnings:
387 Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`c` > 4) and (`test`.`t`.`d` < 3))
388 SELECT * FROM information_schema.optimizer_trace;
389 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
390 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND c > 4 AND d < 3 {
391 "steps": [
392 {
393 "join_preparation": {
394 "select#": 1,
395 "steps": [
396 {
397 "expanded_query": "/* select#1 */ select `t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `t` where ((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))"
398 }
399 ]
400 }
401 },
402 {
403 "join_optimization": {
404 "select#": 1,
405 "steps": [
406 {
407 "condition_processing": {
408 "condition": "WHERE",
409 "original_condition": "((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))",
410 "steps": [
411 {
412 "transformation": "equality_propagation",
413 "resulting_condition": "((`t`.`c` > 4) and (`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
414 },
415 {
416 "transformation": "constant_propagation",
417 "resulting_condition": "((`t`.`c` > 4) and (`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
418 },
419 {
420 "transformation": "trivial_condition_removal",
421 "resulting_condition": "((`t`.`c` > 4) and (`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
422 }
423 ]
424 }
425 },
426 {
427 "substitute_generated_columns": {
428 }
429 },
430 {
431 "table_dependencies": [
432 {
433 "table": "`t`",
434 "row_may_be_null": false,
435 "map_bit": 0,
436 "depends_on_map_bits": [
437 ]
438 }
439 ]
440 },
441 {
442 "ref_optimizer_key_uses": [
443 {
444 "table": "`t`",
445 "field": "a",
446 "equals": "5",
447 "null_rejecting": true
448 }
449 ]
450 },
451 {
452 "rows_estimation": [
453 {
454 "table": "`t`",
455 "range_analysis": {
456 "table_scan": {
457 "rows": 2500,
458 "cost": 254.1
459 },
460 "potential_range_indexes": [
461 {
462 "index": "PRIMARY",
463 "usable": true,
464 "key_parts": [
465 "a",
466 "b",
467 "c",
468 "d"
469 ]
470 },
471 {
472 "index": "b",
473 "usable": true,
474 "key_parts": [
475 "b",
476 "d",
477 "a",
478 "c"
479 ]
480 }
481 ],
482 "best_covering_index_scan": {
483 "index": "b",
484 "cost": 252.077,
485 "chosen": true
486 },
487 "setup_range_conditions": [
488 ],
489 "group_index_range": {
490 "chosen": false,
491 "cause": "not_group_by_or_distinct"
492 },
493 "skip_scan_range": {
494 "potential_skip_scan_indexes": [
495 {
496 "index": "PRIMARY",
497 "tree_travel_cost": 0.6,
498 "num_groups": 6,
499 "rows": 83,
500 "cost": 19.9798
501 },
502 {
503 "index": "b",
504 "tree_travel_cost": 0.6,
505 "num_groups": 6,
506 "rows": 833,
507 "cost": 133.008
508 }
509 ]
510 },
511 "best_skip_scan_summary": {
512 "type": "skip_scan",
513 "index": "PRIMARY",
514 "key_parts_used_for_access": [
515 "a",
516 "b",
517 "c"
518 ],
519 "prefix ranges": [
520 "a = 5"
521 ],
522 "range": [
523 "4 < c"
524 ],
525 "chosen": true
526 },
527 "analyzing_range_alternatives": {
528 "range_scan_alternatives": [
529 {
530 "index": "PRIMARY",
531 "ranges": [
532 "a = 5"
533 ],
534 "index_dives_for_eq_ranges": true,
535 "rowid_ordered": true,
536 "using_mrr": false,
537 "index_only": true,
538 "in_memory": 1,
539 "rows": 250,
540 "cost": 25.345,
541 "chosen": false,
542 "cause": "cost"
543 },
544 {
545 "index": "b",
546 "chosen": false,
547 "cause": "no_valid_range_for_this_index"
548 }
549 ],
550 "analyzing_roworder_intersect": {
551 "usable": false,
552 "cause": "too_few_roworder_scans"
553 }
554 },
555 "chosen_range_access_summary": {
556 "range_access_plan": {
557 "type": "skip_scan",
558 "index": "PRIMARY",
559 "key_parts_used_for_access": [
560 "a",
561 "b",
562 "c"
563 ],
564 "prefix ranges": [
565 "a = 5"
566 ],
567 "range": [
568 "4 < c"
569 ]
570 },
571 "rows_for_plan": 83,
572 "cost_for_plan": 19.9798,
573 "chosen": true
574 }
575 }
576 }
577 ]
578 },
579 {
580 "considered_execution_plans": [
581 {
582 "plan_prefix": [
583 ],
584 "table": "`t`",
585 "best_access_path": {
586 "considered_access_paths": [
587 {
588 "access_type": "ref",
589 "index": "PRIMARY",
590 "rows": 250,
591 "cost": 25.4922,
592 "chosen": true
593 },
594 {
595 "rows_to_scan": 83,
596 "filtering_effect": [
597 ],
598 "final_filtering_effect": 0.3333,
599 "access_type": "range",
600 "range_details": {
601 "used_index": "index_for_skip_scan(PRIMARY)"
602 },
603 "resulting_rows": 27.6639,
604 "cost": 28.2798,
605 "chosen": false
606 }
607 ]
608 },
609 "condition_filtering_pct": 11.1089,
610 "rows_for_plan": 27.7722,
611 "cost_for_plan": 25.4922,
612 "chosen": true
613 }
614 ]
615 },
616 {
617 "access_type_changed": {
618 "table": "`t`",
619 "index": "PRIMARY",
620 "old_type": "ref",
621 "new_type": "range",
622 "cause": "uses_more_keyparts"
623 }
624 },
625 {
626 "attaching_conditions_to_tables": {
627 "original_condition": "((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))",
628 "attached_conditions_computation": [
629 ],
630 "attached_conditions_summary": [
631 {
632 "table": "`t`",
633 "attached": "((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))"
634 }
635 ]
636 }
637 },
638 {
639 "finalizing_table_conditions": [
640 {
641 "table": "`t`",
642 "original_table_condition": "((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))",
643 "final_table_condition ": "((`t`.`a` = 5) and (`t`.`c` > 4) and (`t`.`d` < 3))"
644 }
645 ]
646 },
647 {
648 "refine_plan": [
649 {
650 "table": "`t`"
651 }
652 ]
653 }
654 ]
655 }
656 },
657 {
658 "join_explain": {
659 "select#": 1,
660 "steps": [
661 ]
662 }
663 }
664 ]
665 } 0 0
666 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND d < 3;
667 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
668 1 SIMPLE t NULL ref PRIMARY,b PRIMARY 4 const 250 33.33 Using where; Using index
669 Warnings:
670 Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`,`test`.`t`.`d` AS `d` from `test`.`t` where ((`test`.`t`.`a` = 5) and (`test`.`t`.`d` < 3))
671 SELECT * FROM information_schema.optimizer_trace;
672 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
673 EXPLAIN SELECT b, c, d FROM t WHERE a = 5 AND d < 3 {
674 "steps": [
675 {
676 "join_preparation": {
677 "select#": 1,
678 "steps": [
679 {
680 "expanded_query": "/* select#1 */ select `t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `t` where ((`t`.`a` = 5) and (`t`.`d` < 3))"
681 }
682 ]
683 }
684 },
685 {
686 "join_optimization": {
687 "select#": 1,
688 "steps": [
689 {
690 "condition_processing": {
691 "condition": "WHERE",
692 "original_condition": "((`t`.`a` = 5) and (`t`.`d` < 3))",
693 "steps": [
694 {
695 "transformation": "equality_propagation",
696 "resulting_condition": "((`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
697 },
698 {
699 "transformation": "constant_propagation",
700 "resulting_condition": "((`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
701 },
702 {
703 "transformation": "trivial_condition_removal",
704 "resulting_condition": "((`t`.`d` < 3) and multiple equal(5, `t`.`a`))"
705 }
706 ]
707 }
708 },
709 {
710 "substitute_generated_columns": {
711 }
712 },
713 {
714 "table_dependencies": [
715 {
716 "table": "`t`",
717 "row_may_be_null": false,
718 "map_bit": 0,
719 "depends_on_map_bits": [
720 ]
721 }
722 ]
723 },
724 {
725 "ref_optimizer_key_uses": [
726 {
727 "table": "`t`",
728 "field": "a",
729 "equals": "5",
730 "null_rejecting": true
731 }
732 ]
733 },
734 {
735 "rows_estimation": [
736 {
737 "table": "`t`",
738 "range_analysis": {
739 "table_scan": {
740 "rows": 2500,
741 "cost": 254.1
742 },
743 "potential_range_indexes": [
744 {
745 "index": "PRIMARY",
746 "usable": true,
747 "key_parts": [
748 "a",
749 "b",
750 "c",
751 "d"
752 ]
753 },
754 {
755 "index": "b",
756 "usable": true,
757 "key_parts": [
758 "b",
759 "d",
760 "a",
761 "c"
762 ]
763 }
764 ],
765 "best_covering_index_scan": {
766 "index": "b",
767 "cost": 252.077,
768 "chosen": true
769 },
770 "setup_range_conditions": [
771 ],
772 "group_index_range": {
773 "chosen": false,
774 "cause": "not_group_by_or_distinct"
775 },
776 "skip_scan_range": {
777 "potential_skip_scan_indexes": [
778 {
779 "index": "PRIMARY",
780 "tree_travel_cost": 0.6,
781 "num_groups": 26,
782 "rows": 83,
783 "cost": 43.9798
784 },
785 {
786 "index": "b",
787 "tree_travel_cost": 0.6,
788 "num_groups": 6,
789 "rows": 833,
790 "cost": 133.008
791 }
792 ]
793 },
794 "best_skip_scan_summary": {
795 "type": "skip_scan",
796 "index": "PRIMARY",
797 "key_parts_used_for_access": [
798 "a",
799 "b",
800 "c",
801 "d"
802 ],
803 "prefix ranges": [
804 "a = 5"
805 ],
806 "range": [
807 "d < 3"
808 ],
809 "chosen": true
810 },
811 "analyzing_range_alternatives": {
812 "range_scan_alternatives": [
813 {
814 "index": "PRIMARY",
815 "ranges": [
816 "a = 5"
817 ],
818 "index_dives_for_eq_ranges": true,
819 "rowid_ordered": true,
820 "using_mrr": false,
821 "index_only": true,
822 "in_memory": 1,
823 "rows": 250,
824 "cost": 25.345,
825 "chosen": true
826 },
827 {
828 "index": "b",
829 "chosen": false,
830 "cause": "no_valid_range_for_this_index"
831 }
832 ],
833 "analyzing_roworder_intersect": {
834 "usable": false,
835 "cause": "too_few_roworder_scans"
836 }
837 },
838 "chosen_range_access_summary": {
839 "range_access_plan": {
840 "type": "range_scan",
841 "index": "PRIMARY",
842 "rows": 250,
843 "ranges": [
844 "a = 5"
845 ]
846 },
847 "rows_for_plan": 250,
848 "cost_for_plan": 25.345,
849 "chosen": true
850 }
851 }
852 }
853 ]
854 },
855 {
856 "considered_execution_plans": [
857 {
858 "plan_prefix": [
859 ],
860 "table": "`t`",
861 "best_access_path": {
862 "considered_access_paths": [
863 {
864 "access_type": "ref",
865 "index": "PRIMARY",
866 "rows": 250,
867 "cost": 25.4922,
868 "chosen": true
869 },
870 {
871 "access_type": "range",
872 "range_details": {
873 "used_index": "PRIMARY"
874 },
875 "chosen": false,
876 "cause": "heuristic_index_cheaper"
877 }
878 ]
879 },
880 "condition_filtering_pct": 33.33,
881 "rows_for_plan": 83.325,
882 "cost_for_plan": 25.4922,
883 "chosen": true
884 }
885 ]
886 },
887 {
888 "attaching_conditions_to_tables": {
889 "original_condition": "((`t`.`a` = 5) and (`t`.`d` < 3))",
890 "attached_conditions_computation": [
891 ],
892 "attached_conditions_summary": [
893 {
894 "table": "`t`",
895 "attached": "((`t`.`a` = 5) and (`t`.`d` < 3))"
896 }
897 ]
898 }
899 },
900 {
901 "finalizing_table_conditions": [
902 {
903 "table": "`t`",
904 "original_table_condition": "((`t`.`a` = 5) and (`t`.`d` < 3))",
905 "final_table_condition ": "(`t`.`d` < 3)"
906 }
907 ]
908 },
909 {
910 "refine_plan": [
911 {
912 "table": "`t`"
913 }
914 ]
915 }
916 ]
917 }
918 },
919 {
920 "join_explain": {
921 "select#": 1,
922 "steps": [
923 ]
924 }
925 }
926 ]
927 } 0 0
928 ANALYZE TABLE t UPDATE HISTOGRAM ON d WITH 10 BUCKETS;
929 Table Op Msg_type Msg_text
930 test.t histogram status Histogram statistics created for column 'd'.
931 EXPLAIN SELECT b FROM t WHERE d < 2;
932 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
933 1 SIMPLE t NULL range PRIMARY,b b 8 NULL 250 100.00 Using where; Using index for skip scan
934 Warnings:
935 Note 1003 /* select#1 */ select `test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`d` < 2)
936 SELECT * FROM information_schema.optimizer_trace;
937 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
938 EXPLAIN SELECT b FROM t WHERE d < 2 {
939 "steps": [
940 {
941 "join_preparation": {
942 "select#": 1,
943 "steps": [
944 {
945 "expanded_query": "/* select#1 */ select `t`.`b` AS `b` from `t` where (`t`.`d` < 2)"
946 }
947 ]
948 }
949 },
950 {
951 "join_optimization": {
952 "select#": 1,
953 "steps": [
954 {
955 "condition_processing": {
956 "condition": "WHERE",
957 "original_condition": "(`t`.`d` < 2)",
958 "steps": [
959 {
960 "transformation": "equality_propagation",
961 "resulting_condition": "(`t`.`d` < 2)"
962 },
963 {
964 "transformation": "constant_propagation",
965 "resulting_condition": "(`t`.`d` < 2)"
966 },
967 {
968 "transformation": "trivial_condition_removal",
969 "resulting_condition": "(`t`.`d` < 2)"
970 }
971 ]
972 }
973 },
974 {
975 "substitute_generated_columns": {
976 }
977 },
978 {
979 "table_dependencies": [
980 {
981 "table": "`t`",
982 "row_may_be_null": false,
983 "map_bit": 0,
984 "depends_on_map_bits": [
985 ]
986 }
987 ]
988 },
989 {
990 "ref_optimizer_key_uses": [
991 ]
992 },
993 {
994 "rows_estimation": [
995 {
996 "table": "`t`",
997 "range_analysis": {
998 "table_scan": {
999 "rows": 2500,
1000 "cost": 254.1
1001 },
1002 "potential_range_indexes": [
1003 {
1004 "index": "PRIMARY",
1005 "usable": true,
1006 "key_parts": [
1007 "a",
1008 "b",
1009 "c",
1010 "d"
1011 ]
1012 },
1013 {
1014 "index": "b",
1015 "usable": true,
1016 "key_parts": [
1017 "b",
1018 "d",
1019 "a",
1020 "c"
1021 ]
1022 }
1023 ],
1024 "best_covering_index_scan": {
1025 "index": "b",
1026 "cost": 252.077,
1027 "chosen": true
1028 },
1029 "setup_range_conditions": [
1030 ],
1031 "group_index_range": {
1032 "chosen": false,
1033 "cause": "not_group_by_or_distinct"
1034 },
1035 "skip_scan_range": {
1036 "potential_skip_scan_indexes": [
1037 {
1038 "index": "PRIMARY",
1039 "histogram_selectivity": {
1040 "condition": "(`t`.`d` < 2)",
1041 "histogram_selectivity": 0.1
1042 },
1043 "tree_travel_cost": 0.6,
1044 "num_groups": 251,
1045 "rows": 250,
1046 "cost": 339.192
1047 },
1048 {
1049 "index": "b",
1050 "histogram_selectivity": {
1051 "condition": "(`t`.`d` < 2)",
1052 "histogram_selectivity": 0.1
1053 },
1054 "tree_travel_cost": 0.6,
1055 "num_groups": 6,
1056 "rows": 250,
1057 "cost": 45.132
1058 }
1059 ]
1060 },
1061 "best_skip_scan_summary": {
1062 "type": "skip_scan",
1063 "index": "b",
1064 "key_parts_used_for_access": [
1065 "b",
1066 "d"
1067 ],
1068 "range": [
1069 "d < 2"
1070 ],
1071 "chosen": true
1072 },
1073 "analyzing_range_alternatives": {
1074 "range_scan_alternatives": [
1075 {
1076 "index": "PRIMARY",
1077 "chosen": false,
1078 "cause": "no_valid_range_for_this_index"
1079 },
1080 {
1081 "index": "b",
1082 "chosen": false,
1083 "cause": "no_valid_range_for_this_index"
1084 }
1085 ],
1086 "analyzing_roworder_intersect": {
1087 "usable": false,
1088 "cause": "too_few_roworder_scans"
1089 }
1090 },
1091 "chosen_range_access_summary": {
1092 "range_access_plan": {
1093 "type": "skip_scan",
1094 "index": "b",
1095 "key_parts_used_for_access": [
1096 "b",
1097 "d"
1098 ],
1099 "range": [
1100 "d < 2"
1101 ]
1102 },
1103 "rows_for_plan": 250,
1104 "cost_for_plan": 45.132,
1105 "chosen": true
1106 }
1107 }
1108 }
1109 ]
1110 },
1111 {
1112 "considered_execution_plans": [
1113 {
1114 "plan_prefix": [
1115 ],
1116 "table": "`t`",
1117 "best_access_path": {
1118 "considered_access_paths": [
1119 {
1120 "rows_to_scan": 250,
1121 "filtering_effect": [
1122 ],
1123 "final_filtering_effect": 1,
1124 "access_type": "range",
1125 "range_details": {
1126 "used_index": "index_for_skip_scan(b)"
1127 },
1128 "resulting_rows": 250,
1129 "cost": 70.132,
1130 "chosen": true
1131 }
1132 ]
1133 },
1134 "condition_filtering_pct": 100,
1135 "rows_for_plan": 250,
1136 "cost_for_plan": 70.132,
1137 "chosen": true
1138 }
1139 ]
1140 },
1141 {
1142 "attaching_conditions_to_tables": {
1143 "original_condition": "(`t`.`d` < 2)",
1144 "attached_conditions_computation": [
1145 ],
1146 "attached_conditions_summary": [
1147 {
1148 "table": "`t`",
1149 "attached": "(`t`.`d` < 2)"
1150 }
1151 ]
1152 }
1153 },
1154 {
1155 "finalizing_table_conditions": [
1156 {
1157 "table": "`t`",
1158 "original_table_condition": "(`t`.`d` < 2)",
1159 "final_table_condition ": "(`t`.`d` < 2)"
1160 }
1161 ]
1162 },
1163 {
1164 "refine_plan": [
1165 {
1166 "table": "`t`"
1167 }
1168 ]
1169 }
1170 ]
1171 }
1172 },
1173 {
1174 "join_explain": {
1175 "select#": 1,
1176 "steps": [
1177 ]
1178 }
1179 }
1180 ]
1181 } 0 0
1182 SET optimizer_trace="enabled=off";
1183 DROP TABLE t;
1184 SET optimizer_switch = 'skip_scan=on';
1185 SET optimizer_trace="enabled=on,one_line=off";
1186 CREATE TABLE t1 (f1 VARCHAR(10), f2 CHAR(10), f3 INT, KEY(f1, f2, f3));
1187 INSERT INTO t1 VALUES ('a', 'aaaa', 1);
1188 EXPLAIN SELECT /*+ SKIP_SCAN(t1) */ count(*) FROM t1 WHERE f1 BETWEEN 'a' and 'a ' AND f3 > 0;
1189 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1190 1 SIMPLE t1 NULL index f1 f1 89 NULL 1 100.00 Using where; Using index
1191 Warnings:
1192 Note 1003 /* select#1 */ select /*+ SKIP_SCAN(`t1`@`select#1`) */ count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`f1` between 'a' and 'a ') and (`test`.`t1`.`f3` > 0))
1193 SELECT count(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE trace LIKE '%prefix_not_const_equality%';
1194 count(*)
1195 1
1196 DROP TABLE t1;
1197 SET optimizer_trace="enabled=off";