Debian Codegraph @

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

raw
1 SET optimizer_trace_max_mem_size=1048576;
2 SET end_markers_in_json=on;
3 SET optimizer_trace="enabled=on,one_line=off";
4 CREATE TABLE t1
5 (
6 key1 INT NOT NULL,
7 INDEX i1(key1)
8 );
9 Inserting 1024 records into t1
10 ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
11 ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
12 ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
13 ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
14 ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
15 ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
16 ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
17 UPDATE t1 SET
18 key2=key1,
19 key3=key1,
20 key4=key1,
21 key5=key1,
22 key6=key1,
23 key7=key1,
24 key8=1024-key1;
25 CREATE TABLE t2 (
26 key1a INT NOT NULL,
27 key1b INT NOT NULL,
28 key2 INT NOT NULL,
29 key2_1 INT NOT NULL,
30 key2_2 INT NOT NULL,
31 key3 INT NOT NULL,
32 primary key i1a (key1a, key1b),
33 INDEX i1b (key1b, key1a),
34 INDEX i2_1(key2, key2_1),
35 INDEX i2_2(key2, key2_1)
36 );
37 Warnings:
38 Warning 1831 Duplicate index 'i2_2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
39 INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
40
41 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
42 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
43 1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition
44 Warnings:
45 Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))
46
47 SELECT * FROM information_schema.OPTIMIZER_TRACE;
48 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
49 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
50 "steps": [
51 {
52 "join_preparation": {
53 "select#": 1,
54 "steps": [
55 {
56 "expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
57 }
58 ] /* steps */
59 } /* join_preparation */
60 },
61 {
62 "join_optimization": {
63 "select#": 1,
64 "steps": [
65 {
66 "condition_processing": {
67 "condition": "WHERE",
68 "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
69 "steps": [
70 {
71 "transformation": "equality_propagation",
72 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
73 },
74 {
75 "transformation": "constant_propagation",
76 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
77 },
78 {
79 "transformation": "trivial_condition_removal",
80 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
81 }
82 ] /* steps */
83 } /* condition_processing */
84 },
85 {
86 "substitute_generated_columns": {
87 } /* substitute_generated_columns */
88 },
89 {
90 "table_dependencies": [
91 {
92 "table": "`t1`",
93 "row_may_be_null": false,
94 "map_bit": 0,
95 "depends_on_map_bits": [
96 ] /* depends_on_map_bits */
97 }
98 ] /* table_dependencies */
99 },
100 {
101 "ref_optimizer_key_uses": [
102 ] /* ref_optimizer_key_uses */
103 },
104 {
105 "rows_estimation": [
106 {
107 "table": "`t1`",
108 "range_analysis": {
109 "table_scan": {
110 "rows": 1024,
111 "cost": 107.06
112 } /* table_scan */,
113 "potential_range_indexes": [
114 {
115 "index": "i1",
116 "usable": false,
117 "cause": "not_applicable"
118 },
119 {
120 "index": "i2",
121 "usable": true,
122 "key_parts": [
123 "key2"
124 ] /* key_parts */
125 },
126 {
127 "index": "i3",
128 "usable": false,
129 "cause": "not_applicable"
130 },
131 {
132 "index": "i4",
133 "usable": false,
134 "cause": "not_applicable"
135 },
136 {
137 "index": "i5",
138 "usable": false,
139 "cause": "not_applicable"
140 },
141 {
142 "index": "i6",
143 "usable": false,
144 "cause": "not_applicable"
145 },
146 {
147 "index": "i7",
148 "usable": false,
149 "cause": "not_applicable"
150 },
151 {
152 "index": "i8",
153 "usable": false,
154 "cause": "not_applicable"
155 }
156 ] /* potential_range_indexes */,
157 "setup_range_conditions": [
158 ] /* setup_range_conditions */,
159 "group_index_range": {
160 "chosen": false,
161 "cause": "not_group_by_or_distinct"
162 } /* group_index_range */,
163 "skip_scan_range": {
164 "potential_skip_scan_indexes": [
165 {
166 "index": "i2",
167 "usable": false,
168 "cause": "query_references_nonkey_column"
169 }
170 ] /* potential_skip_scan_indexes */
171 } /* skip_scan_range */,
172 "analyzing_range_alternatives": {
173 "range_scan_alternatives": [
174 {
175 "index": "i2",
176 "ranges": [
177 "key2 < 5",
178 "1020 < key2"
179 ] /* ranges */,
180 "index_dives_for_eq_ranges": true,
181 "rowid_ordered": false,
182 "using_mrr": false,
183 "index_only": false,
184 "in_memory": -1,
185 "rows": 47,
186 "cost": 16.96,
187 "chosen": true
188 }
189 ] /* range_scan_alternatives */,
190 "analyzing_roworder_intersect": {
191 "usable": false,
192 "cause": "too_few_roworder_scans"
193 } /* analyzing_roworder_intersect */
194 } /* analyzing_range_alternatives */,
195 "chosen_range_access_summary": {
196 "range_access_plan": {
197 "type": "range_scan",
198 "index": "i2",
199 "rows": 47,
200 "ranges": [
201 "key2 < 5",
202 "1020 < key2"
203 ] /* ranges */
204 } /* range_access_plan */,
205 "rows_for_plan": 47,
206 "cost_for_plan": 16.96,
207 "chosen": true
208 } /* chosen_range_access_summary */
209 } /* range_analysis */
210 }
211 ] /* rows_estimation */
212 },
213 {
214 "considered_execution_plans": [
215 {
216 "plan_prefix": [
217 ] /* plan_prefix */,
218 "table": "`t1`",
219 "best_access_path": {
220 "considered_access_paths": [
221 {
222 "rows_to_scan": 47,
223 "filtering_effect": [
224 ] /* filtering_effect */,
225 "final_filtering_effect": 1,
226 "access_type": "range",
227 "range_details": {
228 "used_index": "i2"
229 } /* range_details */,
230 "resulting_rows": 47,
231 "cost": 21.66,
232 "chosen": true
233 }
234 ] /* considered_access_paths */
235 } /* best_access_path */,
236 "condition_filtering_pct": 100,
237 "rows_for_plan": 47,
238 "cost_for_plan": 21.66,
239 "chosen": true
240 }
241 ] /* considered_execution_plans */
242 },
243 {
244 "attaching_conditions_to_tables": {
245 "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
246 "attached_conditions_computation": [
247 ] /* attached_conditions_computation */,
248 "attached_conditions_summary": [
249 {
250 "table": "`t1`",
251 "attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
252 }
253 ] /* attached_conditions_summary */
254 } /* attaching_conditions_to_tables */
255 },
256 {
257 "finalizing_table_conditions": [
258 {
259 "table": "`t1`",
260 "original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
261 "final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
262 }
263 ] /* finalizing_table_conditions */
264 },
265 {
266 "refine_plan": [
267 {
268 "table": "`t1`",
269 "pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
270 "table_condition_attached": null
271 }
272 ] /* refine_plan */
273 }
274 ] /* steps */
275 } /* join_optimization */
276 },
277 {
278 "join_explain": {
279 "select#": 1,
280 "steps": [
281 ] /* steps */
282 } /* join_explain */
283 }
284 ] /* steps */
285 } 0 0
286 set @@optimizer_trace_features="range_optimizer=off";
287
288 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
289 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
290 1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition
291 Warnings:
292 Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))
293
294 SELECT * FROM information_schema.OPTIMIZER_TRACE;
295 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
296 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
297 "steps": [
298 {
299 "join_preparation": {
300 "select#": 1,
301 "steps": [
302 {
303 "expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
304 }
305 ] /* steps */
306 } /* join_preparation */
307 },
308 {
309 "join_optimization": {
310 "select#": 1,
311 "steps": [
312 {
313 "condition_processing": {
314 "condition": "WHERE",
315 "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
316 "steps": [
317 {
318 "transformation": "equality_propagation",
319 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
320 },
321 {
322 "transformation": "constant_propagation",
323 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
324 },
325 {
326 "transformation": "trivial_condition_removal",
327 "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
328 }
329 ] /* steps */
330 } /* condition_processing */
331 },
332 {
333 "substitute_generated_columns": {
334 } /* substitute_generated_columns */
335 },
336 {
337 "table_dependencies": [
338 {
339 "table": "`t1`",
340 "row_may_be_null": false,
341 "map_bit": 0,
342 "depends_on_map_bits": [
343 ] /* depends_on_map_bits */
344 }
345 ] /* table_dependencies */
346 },
347 {
348 "ref_optimizer_key_uses": [
349 ] /* ref_optimizer_key_uses */
350 },
351 {
352 "rows_estimation": [
353 {
354 "table": "`t1`",
355 "range_analysis": {
356 "table_scan": {
357 "rows": 1024,
358 "cost": 107.06
359 } /* table_scan */,
360 "potential_range_indexes": "...",
361 "setup_range_conditions": [
362 ] /* setup_range_conditions */,
363 "group_index_range": "...",
364 "skip_scan_range": "...",
365 "analyzing_range_alternatives": "...",
366 "chosen_range_access_summary": {
367 "range_access_plan": {
368 "type": "range_scan",
369 "index": "i2",
370 "rows": 47,
371 "ranges": [
372 "key2 < 5",
373 "1020 < key2"
374 ] /* ranges */
375 } /* range_access_plan */,
376 "rows_for_plan": 47,
377 "cost_for_plan": 16.96,
378 "chosen": true
379 } /* chosen_range_access_summary */
380 } /* range_analysis */
381 }
382 ] /* rows_estimation */
383 },
384 {
385 "considered_execution_plans": [
386 {
387 "plan_prefix": [
388 ] /* plan_prefix */,
389 "table": "`t1`",
390 "best_access_path": {
391 "considered_access_paths": [
392 {
393 "rows_to_scan": 47,
394 "filtering_effect": [
395 ] /* filtering_effect */,
396 "final_filtering_effect": 1,
397 "access_type": "range",
398 "range_details": {
399 "used_index": "i2"
400 } /* range_details */,
401 "resulting_rows": 47,
402 "cost": 21.66,
403 "chosen": true
404 }
405 ] /* considered_access_paths */
406 } /* best_access_path */,
407 "condition_filtering_pct": 100,
408 "rows_for_plan": 47,
409 "cost_for_plan": 21.66,
410 "chosen": true
411 }
412 ] /* considered_execution_plans */
413 },
414 {
415 "attaching_conditions_to_tables": {
416 "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
417 "attached_conditions_computation": [
418 ] /* attached_conditions_computation */,
419 "attached_conditions_summary": [
420 {
421 "table": "`t1`",
422 "attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
423 }
424 ] /* attached_conditions_summary */
425 } /* attaching_conditions_to_tables */
426 },
427 {
428 "finalizing_table_conditions": [
429 {
430 "table": "`t1`",
431 "original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
432 "final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
433 }
434 ] /* finalizing_table_conditions */
435 },
436 {
437 "refine_plan": [
438 {
439 "table": "`t1`",
440 "pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
441 "table_condition_attached": null
442 }
443 ] /* refine_plan */
444 }
445 ] /* steps */
446 } /* join_optimization */
447 },
448 {
449 "join_explain": {
450 "select#": 1,
451 "steps": [
452 ] /* steps */
453 } /* join_explain */
454 }
455 ] /* steps */
456 } 0 0
457 set @@optimizer_trace_features="range_optimizer=on";
458
459 EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
460 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
461 1 SIMPLE t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 45 100.00 Using sort_union(i1,i2); Using where
462 Warnings:
463 Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))
464
465 SELECT * FROM information_schema.OPTIMIZER_TRACE;
466 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
467 EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020 {
468 "steps": [
469 {
470 "join_preparation": {
471 "select#": 1,
472 "steps": [
473 {
474 "expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
475 }
476 ] /* steps */
477 } /* join_preparation */
478 },
479 {
480 "join_optimization": {
481 "select#": 1,
482 "steps": [
483 {
484 "condition_processing": {
485 "condition": "WHERE",
486 "original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
487 "steps": [
488 {
489 "transformation": "equality_propagation",
490 "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
491 },
492 {
493 "transformation": "constant_propagation",
494 "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
495 },
496 {
497 "transformation": "trivial_condition_removal",
498 "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
499 }
500 ] /* steps */
501 } /* condition_processing */
502 },
503 {
504 "substitute_generated_columns": {
505 } /* substitute_generated_columns */
506 },
507 {
508 "table_dependencies": [
509 {
510 "table": "`t1`",
511 "row_may_be_null": false,
512 "map_bit": 0,
513 "depends_on_map_bits": [
514 ] /* depends_on_map_bits */
515 }
516 ] /* table_dependencies */
517 },
518 {
519 "ref_optimizer_key_uses": [
520 ] /* ref_optimizer_key_uses */
521 },
522 {
523 "rows_estimation": [
524 {
525 "table": "`t1`",
526 "range_analysis": {
527 "table_scan": {
528 "rows": 1024,
529 "cost": 107.06
530 } /* table_scan */,
531 "potential_range_indexes": [
532 {
533 "index": "i1",
534 "usable": true,
535 "key_parts": [
536 "key1"
537 ] /* key_parts */
538 },
539 {
540 "index": "i2",
541 "usable": true,
542 "key_parts": [
543 "key2"
544 ] /* key_parts */
545 },
546 {
547 "index": "i3",
548 "usable": false,
549 "cause": "not_applicable"
550 },
551 {
552 "index": "i4",
553 "usable": false,
554 "cause": "not_applicable"
555 },
556 {
557 "index": "i5",
558 "usable": false,
559 "cause": "not_applicable"
560 },
561 {
562 "index": "i6",
563 "usable": false,
564 "cause": "not_applicable"
565 },
566 {
567 "index": "i7",
568 "usable": false,
569 "cause": "not_applicable"
570 },
571 {
572 "index": "i8",
573 "usable": false,
574 "cause": "not_applicable"
575 }
576 ] /* potential_range_indexes */,
577 "setup_range_conditions": [
578 ] /* setup_range_conditions */,
579 "group_index_range": {
580 "chosen": false,
581 "cause": "not_group_by_or_distinct"
582 } /* group_index_range */,
583 "skip_scan_range": {
584 "potential_skip_scan_indexes": [
585 {
586 "index": "i1",
587 "usable": false,
588 "cause": "query_references_nonkey_column"
589 },
590 {
591 "index": "i2",
592 "usable": false,
593 "cause": "query_references_nonkey_column"
594 }
595 ] /* potential_skip_scan_indexes */
596 } /* skip_scan_range */,
597 "analyzing_range_alternatives": {
598 "range_scan_alternatives": [
599 ] /* range_scan_alternatives */,
600 "analyzing_roworder_intersect": {
601 "usable": false,
602 "cause": "too_few_roworder_scans"
603 } /* analyzing_roworder_intersect */
604 } /* analyzing_range_alternatives */,
605 "analyzing_index_merge_union": [
606 {
607 "indexes_to_merge": [
608 {
609 "range_scan_alternatives": [
610 {
611 "index": "i1",
612 "ranges": [
613 "key1 < 3"
614 ] /* ranges */,
615 "index_dives_for_eq_ranges": true,
616 "rowid_ordered": false,
617 "using_mrr": false,
618 "index_only": true,
619 "in_memory": -1,
620 "rows": 3,
621 "cost": 0.57,
622 "chosen": true
623 }
624 ] /* range_scan_alternatives */,
625 "index_to_merge": "i1",
626 "cumulated_cost": 0.57
627 },
628 {
629 "range_scan_alternatives": [
630 {
631 "index": "i2",
632 "ranges": [
633 "1020 < key2"
634 ] /* ranges */,
635 "index_dives_for_eq_ranges": true,
636 "rowid_ordered": false,
637 "using_mrr": false,
638 "index_only": true,
639 "in_memory": -1,
640 "rows": 42,
641 "cost": 4.68,
642 "chosen": true
643 }
644 ] /* range_scan_alternatives */,
645 "index_to_merge": "i2",
646 "cumulated_cost": 5.25
647 }
648 ] /* indexes_to_merge */,
649 "cost_of_reading_ranges": 5.25,
650 "cost_sort_rowid_and_read_disk": 2.24,
651 "cost_duplicate_removal": 19.18,
652 "total_cost": 26.67
653 }
654 ] /* analyzing_index_merge_union */,
655 "chosen_range_access_summary": {
656 "range_access_plan": {
657 "type": "index_merge",
658 "index_merge_of": [
659 {
660 "type": "range_scan",
661 "index": "i1",
662 "rows": 3,
663 "ranges": [
664 "key1 < 3"
665 ] /* ranges */
666 },
667 {
668 "type": "range_scan",
669 "index": "i2",
670 "rows": 42,
671 "ranges": [
672 "1020 < key2"
673 ] /* ranges */
674 }
675 ] /* index_merge_of */
676 } /* range_access_plan */,
677 "rows_for_plan": 45,
678 "cost_for_plan": 26.67,
679 "chosen": true
680 } /* chosen_range_access_summary */
681 } /* range_analysis */
682 }
683 ] /* rows_estimation */
684 },
685 {
686 "considered_execution_plans": [
687 {
688 "plan_prefix": [
689 ] /* plan_prefix */,
690 "table": "`t1`",
691 "best_access_path": {
692 "considered_access_paths": [
693 {
694 "rows_to_scan": 45,
695 "filtering_effect": [
696 ] /* filtering_effect */,
697 "final_filtering_effect": 1,
698 "access_type": "range",
699 "range_details": {
700 "used_index": "sort_union(i1,i2)"
701 } /* range_details */,
702 "resulting_rows": 45,
703 "cost": 31.17,
704 "chosen": true
705 }
706 ] /* considered_access_paths */
707 } /* best_access_path */,
708 "condition_filtering_pct": 100,
709 "rows_for_plan": 45,
710 "cost_for_plan": 31.17,
711 "chosen": true
712 }
713 ] /* considered_execution_plans */
714 },
715 {
716 "attaching_conditions_to_tables": {
717 "original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
718 "attached_conditions_computation": [
719 ] /* attached_conditions_computation */,
720 "attached_conditions_summary": [
721 {
722 "table": "`t1`",
723 "attached": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
724 }
725 ] /* attached_conditions_summary */
726 } /* attaching_conditions_to_tables */
727 },
728 {
729 "finalizing_table_conditions": [
730 {
731 "table": "`t1`",
732 "original_table_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
733 "final_table_condition ": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
734 }
735 ] /* finalizing_table_conditions */
736 },
737 {
738 "refine_plan": [
739 {
740 "table": "`t1`"
741 }
742 ] /* refine_plan */
743 }
744 ] /* steps */
745 } /* join_optimization */
746 },
747 {
748 "join_explain": {
749 "select#": 1,
750 "steps": [
751 ] /* steps */
752 } /* join_explain */
753 }
754 ] /* steps */
755 } 0 0
756
757 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
758 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
759 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by
760 Warnings:
761 Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` group by `test`.`t2`.`key2`
762
763 SELECT * FROM information_schema.OPTIMIZER_TRACE;
764 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
765 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2 {
766 "steps": [
767 {
768 "join_preparation": {
769 "select#": 1,
770 "steps": [
771 {
772 "expanded_query": "/* select#1 */ select `t2`.`key2` AS `key2`,min(`t2`.`key2_1`) AS `MIN(key2_1)` from `t2` group by `t2`.`key2`"
773 }
774 ] /* steps */
775 } /* join_preparation */
776 },
777 {
778 "join_optimization": {
779 "select#": 1,
780 "steps": [
781 {
782 "substitute_generated_columns": {
783 } /* substitute_generated_columns */
784 },
785 {
786 "table_dependencies": [
787 {
788 "table": "`t2`",
789 "row_may_be_null": false,
790 "map_bit": 0,
791 "depends_on_map_bits": [
792 ] /* depends_on_map_bits */
793 }
794 ] /* table_dependencies */
795 },
796 {
797 "rows_estimation": [
798 {
799 "table": "`t2`",
800 "const_keys_added": {
801 "keys": [
802 "i2_1",
803 "i2_2"
804 ] /* keys */,
805 "cause": "group_by"
806 } /* const_keys_added */,
807 "range_analysis": {
808 "table_scan": {
809 "rows": 1024,
810 "cost": 106.56
811 } /* table_scan */,
812 "potential_range_indexes": [
813 {
814 "index": "PRIMARY",
815 "usable": false,
816 "cause": "not_applicable"
817 },
818 {
819 "index": "i1b",
820 "usable": false,
821 "cause": "not_applicable"
822 },
823 {
824 "index": "i2_1",
825 "usable": true,
826 "key_parts": [
827 "key2",
828 "key2_1"
829 ] /* key_parts */
830 },
831 {
832 "index": "i2_2",
833 "usable": true,
834 "key_parts": [
835 "key2",
836 "key2_1"
837 ] /* key_parts */
838 }
839 ] /* potential_range_indexes */,
840 "best_covering_index_scan": {
841 "index": "i2_1",
842 "cost": 109.96,
843 "chosen": false,
844 "cause": "cost"
845 } /* best_covering_index_scan */,
846 "group_index_range": {
847 "potential_group_range_indexes": [
848 {
849 "index": "i2_1",
850 "covering": true,
851 "rows": 103,
852 "cost": 28.1
853 },
854 {
855 "index": "i2_2",
856 "covering": true,
857 "rows": 103,
858 "cost": 28.1
859 }
860 ] /* potential_group_range_indexes */
861 } /* group_index_range */,
862 "best_group_range_summary": {
863 "type": "index_group",
864 "index": "i2_1",
865 "group_attribute": "key2_1",
866 "min_aggregate": true,
867 "max_aggregate": false,
868 "distinct_aggregate": false,
869 "rows": 103,
870 "cost": 28.1,
871 "key_parts_used_for_access": [
872 "key2"
873 ] /* key_parts_used_for_access */,
874 "ranges": [
875 ] /* ranges */,
876 "chosen": true
877 } /* best_group_range_summary */,
878 "skip_scan_range": {
879 "chosen": false,
880 "cause": "has_group_by"
881 } /* skip_scan_range */,
882 "chosen_range_access_summary": {
883 "range_access_plan": {
884 "type": "index_group",
885 "index": "i2_1",
886 "group_attribute": "key2_1",
887 "min_aggregate": true,
888 "max_aggregate": false,
889 "distinct_aggregate": false,
890 "rows": 103,
891 "cost": 28.1,
892 "key_parts_used_for_access": [
893 "key2"
894 ] /* key_parts_used_for_access */,
895 "ranges": [
896 ] /* ranges */
897 } /* range_access_plan */,
898 "rows_for_plan": 103,
899 "cost_for_plan": 28.1,
900 "chosen": true
901 } /* chosen_range_access_summary */
902 } /* range_analysis */
903 }
904 ] /* rows_estimation */
905 },
906 {
907 "considered_execution_plans": [
908 {
909 "plan_prefix": [
910 ] /* plan_prefix */,
911 "table": "`t2`",
912 "best_access_path": {
913 "considered_access_paths": [
914 {
915 "rows_to_scan": 103,
916 "access_type": "range",
917 "range_details": {
918 "used_index": "index_for_group_by(i2_1)"
919 } /* range_details */,
920 "resulting_rows": 103,
921 "cost": 38.4,
922 "chosen": true,
923 "use_tmp_table": true
924 }
925 ] /* considered_access_paths */
926 } /* best_access_path */,
927 "condition_filtering_pct": 100,
928 "rows_for_plan": 103,
929 "cost_for_plan": 38.4,
930 "sort_cost": 103,
931 "new_cost_for_plan": 141.4,
932 "chosen": true
933 }
934 ] /* considered_execution_plans */
935 },
936 {
937 "attaching_conditions_to_tables": {
938 "original_condition": null,
939 "attached_conditions_computation": [
940 ] /* attached_conditions_computation */,
941 "attached_conditions_summary": [
942 {
943 "table": "`t2`",
944 "attached": null
945 }
946 ] /* attached_conditions_summary */
947 } /* attaching_conditions_to_tables */
948 },
949 {
950 "optimizing_distinct_group_by_order_by": {
951 "simplifying_group_by": {
952 "original_clause": "`t2`.`key2`",
953 "items": [
954 {
955 "item": "`t2`.`key2`"
956 }
957 ] /* items */,
958 "resulting_clause_is_simple": true,
959 "resulting_clause": "`t2`.`key2`"
960 } /* simplifying_group_by */
961 } /* optimizing_distinct_group_by_order_by */
962 },
963 {
964 "reconsidering_access_paths_for_index_ordering": {
965 "clause": "GROUP BY",
966 "steps": [
967 ] /* steps */,
968 "index_order_summary": {
969 "table": "`t2`",
970 "index_provides_order": true,
971 "order_direction": "asc",
972 "index": "i2_1",
973 "plan_changed": false
974 } /* index_order_summary */
975 } /* reconsidering_access_paths_for_index_ordering */
976 },
977 {
978 "finalizing_table_conditions": [
979 ] /* finalizing_table_conditions */
980 },
981 {
982 "refine_plan": [
983 {
984 "table": "`t2`"
985 }
986 ] /* refine_plan */
987 },
988 {
989 "considering_tmp_tables": [
990 ] /* considering_tmp_tables */
991 }
992 ] /* steps */
993 } /* join_optimization */
994 },
995 {
996 "join_explain": {
997 "select#": 1,
998 "steps": [
999 ] /* steps */
1000 } /* join_explain */
1001 }
1002 ] /* steps */
1003 } 0 0
1004 EXPLAIN SELECT DISTINCT key2 FROM t2;
1005 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1006 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by
1007 Warnings:
1008 Note 1003 /* select#1 */ select distinct `test`.`t2`.`key2` AS `key2` from `test`.`t2`
1009
1010 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1011 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1012 EXPLAIN SELECT DISTINCT key2 FROM t2 {
1013 "steps": [
1014 {
1015 "join_preparation": {
1016 "select#": 1,
1017 "steps": [
1018 {
1019 "expanded_query": "/* select#1 */ select distinct `t2`.`key2` AS `key2` from `t2`"
1020 }
1021 ] /* steps */
1022 } /* join_preparation */
1023 },
1024 {
1025 "join_optimization": {
1026 "select#": 1,
1027 "steps": [
1028 {
1029 "table_dependencies": [
1030 {
1031 "table": "`t2`",
1032 "row_may_be_null": false,
1033 "map_bit": 0,
1034 "depends_on_map_bits": [
1035 ] /* depends_on_map_bits */
1036 }
1037 ] /* table_dependencies */
1038 },
1039 {
1040 "rows_estimation": [
1041 {
1042 "table": "`t2`",
1043 "const_keys_added": {
1044 "keys": [
1045 "i2_1",
1046 "i2_2"
1047 ] /* keys */,
1048 "cause": "distinct"
1049 } /* const_keys_added */,
1050 "range_analysis": {
1051 "table_scan": {
1052 "rows": 1024,
1053 "cost": 106.56
1054 } /* table_scan */,
1055 "potential_range_indexes": [
1056 {
1057 "index": "PRIMARY",
1058 "usable": false,
1059 "cause": "not_applicable"
1060 },
1061 {
1062 "index": "i1b",
1063 "usable": false,
1064 "cause": "not_applicable"
1065 },
1066 {
1067 "index": "i2_1",
1068 "usable": true,
1069 "key_parts": [
1070 "key2",
1071 "key2_1"
1072 ] /* key_parts */
1073 },
1074 {
1075 "index": "i2_2",
1076 "usable": true,
1077 "key_parts": [
1078 "key2",
1079 "key2_1"
1080 ] /* key_parts */
1081 }
1082 ] /* potential_range_indexes */,
1083 "best_covering_index_scan": {
1084 "index": "i2_1",
1085 "cost": 109.96,
1086 "chosen": false,
1087 "cause": "cost"
1088 } /* best_covering_index_scan */,
1089 "group_index_range": {
1090 "distinct_query": true,
1091 "potential_group_range_indexes": [
1092 {
1093 "index": "i2_1",
1094 "covering": true,
1095 "rows": 103,
1096 "cost": 28.1
1097 },
1098 {
1099 "index": "i2_2",
1100 "covering": true,
1101 "rows": 103,
1102 "cost": 28.1
1103 }
1104 ] /* potential_group_range_indexes */
1105 } /* group_index_range */,
1106 "best_group_range_summary": {
1107 "type": "index_group",
1108 "index": "i2_1",
1109 "group_attribute": null,
1110 "min_aggregate": false,
1111 "max_aggregate": false,
1112 "distinct_aggregate": false,
1113 "rows": 103,
1114 "cost": 28.1,
1115 "key_parts_used_for_access": [
1116 "key2"
1117 ] /* key_parts_used_for_access */,
1118 "ranges": [
1119 ] /* ranges */,
1120 "chosen": true
1121 } /* best_group_range_summary */,
1122 "skip_scan_range": {
1123 "chosen": false,
1124 "cause": "disjuntive_predicate_present"
1125 } /* skip_scan_range */,
1126 "chosen_range_access_summary": {
1127 "range_access_plan": {
1128 "type": "index_group",
1129 "index": "i2_1",
1130 "group_attribute": null,
1131 "min_aggregate": false,
1132 "max_aggregate": false,
1133 "distinct_aggregate": false,
1134 "rows": 103,
1135 "cost": 28.1,
1136 "key_parts_used_for_access": [
1137 "key2"
1138 ] /* key_parts_used_for_access */,
1139 "ranges": [
1140 ] /* ranges */
1141 } /* range_access_plan */,
1142 "rows_for_plan": 103,
1143 "cost_for_plan": 28.1,
1144 "chosen": true
1145 } /* chosen_range_access_summary */
1146 } /* range_analysis */
1147 }
1148 ] /* rows_estimation */
1149 },
1150 {
1151 "considered_execution_plans": [
1152 {
1153 "plan_prefix": [
1154 ] /* plan_prefix */,
1155 "table": "`t2`",
1156 "best_access_path": {
1157 "considered_access_paths": [
1158 {
1159 "rows_to_scan": 103,
1160 "access_type": "range",
1161 "range_details": {
1162 "used_index": "index_for_group_by(i2_1)"
1163 } /* range_details */,
1164 "resulting_rows": 103,
1165 "cost": 38.4,
1166 "chosen": true
1167 }
1168 ] /* considered_access_paths */
1169 } /* best_access_path */,
1170 "condition_filtering_pct": 100,
1171 "rows_for_plan": 103,
1172 "cost_for_plan": 38.4,
1173 "chosen": true
1174 }
1175 ] /* considered_execution_plans */
1176 },
1177 {
1178 "attaching_conditions_to_tables": {
1179 "original_condition": null,
1180 "attached_conditions_computation": [
1181 ] /* attached_conditions_computation */,
1182 "attached_conditions_summary": [
1183 {
1184 "table": "`t2`",
1185 "attached": null
1186 }
1187 ] /* attached_conditions_summary */
1188 } /* attaching_conditions_to_tables */
1189 },
1190 {
1191 "optimizing_distinct_group_by_order_by": {
1192 "changed_distinct_to_group_by": true,
1193 "simplifying_group_by": {
1194 "original_clause": "`t2`.`key2`",
1195 "items": [
1196 {
1197 "item": "`t2`.`key2`"
1198 }
1199 ] /* items */,
1200 "resulting_clause_is_simple": true,
1201 "resulting_clause": "`t2`.`key2`"
1202 } /* simplifying_group_by */
1203 } /* optimizing_distinct_group_by_order_by */
1204 },
1205 {
1206 "reconsidering_access_paths_for_index_ordering": {
1207 "clause": "GROUP BY",
1208 "steps": [
1209 ] /* steps */,
1210 "index_order_summary": {
1211 "table": "`t2`",
1212 "index_provides_order": true,
1213 "order_direction": "asc",
1214 "index": "i2_1",
1215 "plan_changed": false
1216 } /* index_order_summary */
1217 } /* reconsidering_access_paths_for_index_ordering */
1218 },
1219 {
1220 "finalizing_table_conditions": [
1221 ] /* finalizing_table_conditions */
1222 },
1223 {
1224 "refine_plan": [
1225 {
1226 "table": "`t2`"
1227 }
1228 ] /* refine_plan */
1229 },
1230 {
1231 "considering_tmp_tables": [
1232 ] /* considering_tmp_tables */
1233 }
1234 ] /* steps */
1235 } /* join_optimization */
1236 },
1237 {
1238 "join_explain": {
1239 "select#": 1,
1240 "steps": [
1241 ] /* steps */
1242 } /* join_explain */
1243 }
1244 ] /* steps */
1245 } 0 0
1246
1247 EXPLAIN SELECT key2, MIN(key2_1) FROM t2
1248 WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1
1249 GROUP BY key2;
1250 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1251 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 47 100.00 Using where; Using index
1252 Warnings:
1253 Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` where ((`test`.`t2`.`key2` = 5) or (`test`.`t2`.`key2` = 4) or (`test`.`t2`.`key2` = 3) or (`test`.`t2`.`key2` = 2) or (`test`.`t2`.`key2` = 1)) group by `test`.`t2`.`key2`
1254
1255 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1256 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1257 EXPLAIN SELECT key2, MIN(key2_1) FROM t2
1258 WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1
1259 GROUP BY key2 {
1260 "steps": [
1261 {
1262 "join_preparation": {
1263 "select#": 1,
1264 "steps": [
1265 {
1266 "expanded_query": "/* select#1 */ select `t2`.`key2` AS `key2`,min(`t2`.`key2_1`) AS `MIN(key2_1)` from `t2` where ((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1)) group by `t2`.`key2`"
1267 }
1268 ] /* steps */
1269 } /* join_preparation */
1270 },
1271 {
1272 "join_optimization": {
1273 "select#": 1,
1274 "steps": [
1275 {
1276 "condition_processing": {
1277 "condition": "WHERE",
1278 "original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
1279 "steps": [
1280 {
1281 "transformation": "equality_propagation",
1282 "resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
1283 },
1284 {
1285 "transformation": "constant_propagation",
1286 "resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
1287 },
1288 {
1289 "transformation": "trivial_condition_removal",
1290 "resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
1291 }
1292 ] /* steps */
1293 } /* condition_processing */
1294 },
1295 {
1296 "substitute_generated_columns": {
1297 } /* substitute_generated_columns */
1298 },
1299 {
1300 "table_dependencies": [
1301 {
1302 "table": "`t2`",
1303 "row_may_be_null": false,
1304 "map_bit": 0,
1305 "depends_on_map_bits": [
1306 ] /* depends_on_map_bits */
1307 }
1308 ] /* table_dependencies */
1309 },
1310 {
1311 "ref_optimizer_key_uses": [
1312 ] /* ref_optimizer_key_uses */
1313 },
1314 {
1315 "rows_estimation": [
1316 {
1317 "table": "`t2`",
1318 "range_analysis": {
1319 "table_scan": {
1320 "rows": 1024,
1321 "cost": 106.56
1322 } /* table_scan */,
1323 "potential_range_indexes": [
1324 {
1325 "index": "PRIMARY",
1326 "usable": false,
1327 "cause": "not_applicable"
1328 },
1329 {
1330 "index": "i1b",
1331 "usable": false,
1332 "cause": "not_applicable"
1333 },
1334 {
1335 "index": "i2_1",
1336 "usable": true,
1337 "key_parts": [
1338 "key2",
1339 "key2_1"
1340 ] /* key_parts */
1341 },
1342 {
1343 "index": "i2_2",
1344 "usable": true,
1345 "key_parts": [
1346 "key2",
1347 "key2_1"
1348 ] /* key_parts */
1349 }
1350 ] /* potential_range_indexes */,
1351 "best_covering_index_scan": {
1352 "index": "i2_1",
1353 "cost": 109.96,
1354 "chosen": false,
1355 "cause": "cost"
1356 } /* best_covering_index_scan */,
1357 "setup_range_conditions": [
1358 ] /* setup_range_conditions */,
1359 "group_index_range": {
1360 "potential_group_range_indexes": [
1361 {
1362 "index": "i2_1",
1363 "covering": true,
1364 "index_dives_for_eq_ranges": true,
1365 "ranges": [
1366 "key2 = 1",
1367 "key2 = 2",
1368 "key2 = 3",
1369 "key2 = 4",
1370 "key2 = 5"
1371 ] /* ranges */,
1372 "rows": 5,
1373 "cost": 8.5
1374 },
1375 {
1376 "index": "i2_2",
1377 "covering": true,
1378 "index_dives_for_eq_ranges": true,
1379 "ranges": [
1380 "key2 = 1",
1381 "key2 = 2",
1382 "key2 = 3",
1383 "key2 = 4",
1384 "key2 = 5"
1385 ] /* ranges */,
1386 "rows": 5,
1387 "cost": 8.5
1388 }
1389 ] /* potential_group_range_indexes */
1390 } /* group_index_range */,
1391 "best_group_range_summary": {
1392 "type": "index_group",
1393 "index": "i2_1",
1394 "group_attribute": "key2_1",
1395 "min_aggregate": true,
1396 "max_aggregate": false,
1397 "distinct_aggregate": false,
1398 "rows": 5,
1399 "cost": 8.5,
1400 "key_parts_used_for_access": [
1401 "key2"
1402 ] /* key_parts_used_for_access */,
1403 "ranges": [
1404 "key2 = 1",
1405 "key2 = 2",
1406 "key2 = 3",
1407 "key2 = 4",
1408 "key2 = 5"
1409 ] /* ranges */,
1410 "chosen": true
1411 } /* best_group_range_summary */,
1412 "skip_scan_range": {
1413 "chosen": false,
1414 "cause": "has_group_by"
1415 } /* skip_scan_range */,
1416 "analyzing_range_alternatives": {
1417 "range_scan_alternatives": [
1418 {
1419 "index": "i2_1",
1420 "ranges": [
1421 "key2 = 1",
1422 "key2 = 2",
1423 "key2 = 3",
1424 "key2 = 4",
1425 "key2 = 5"
1426 ] /* ranges */,
1427 "index_dives_for_eq_ranges": true,
1428 "rowid_ordered": false,
1429 "using_mrr": false,
1430 "index_only": true,
1431 "in_memory": -1,
1432 "rows": 47,
1433 "cost": 5.29,
1434 "chosen": true
1435 },
1436 {
1437 "index": "i2_2",
1438 "ranges": [
1439 "key2 = 1",
1440 "key2 = 2",
1441 "key2 = 3",
1442 "key2 = 4",
1443 "key2 = 5"
1444 ] /* ranges */,
1445 "index_dives_for_eq_ranges": true,
1446 "rowid_ordered": false,
1447 "using_mrr": false,
1448 "index_only": true,
1449 "in_memory": -1,
1450 "rows": 47,
1451 "cost": 5.29,
1452 "chosen": false,
1453 "cause": "cost"
1454 }
1455 ] /* range_scan_alternatives */,
1456 "analyzing_roworder_intersect": {
1457 "usable": false,
1458 "cause": "too_few_roworder_scans"
1459 } /* analyzing_roworder_intersect */
1460 } /* analyzing_range_alternatives */,
1461 "chosen_range_access_summary": {
1462 "range_access_plan": {
1463 "type": "range_scan",
1464 "index": "i2_1",
1465 "rows": 47,
1466 "ranges": [
1467 "key2 = 1",
1468 "key2 = 2",
1469 "key2 = 3",
1470 "key2 = 4",
1471 "key2 = 5"
1472 ] /* ranges */
1473 } /* range_access_plan */,
1474 "rows_for_plan": 47,
1475 "cost_for_plan": 5.29,
1476 "chosen": true
1477 } /* chosen_range_access_summary */
1478 } /* range_analysis */
1479 }
1480 ] /* rows_estimation */
1481 },
1482 {
1483 "considered_execution_plans": [
1484 {
1485 "plan_prefix": [
1486 ] /* plan_prefix */,
1487 "table": "`t2`",
1488 "best_access_path": {
1489 "considered_access_paths": [
1490 {
1491 "rows_to_scan": 47,
1492 "filtering_effect": [
1493 ] /* filtering_effect */,
1494 "final_filtering_effect": 1,
1495 "access_type": "range",
1496 "range_details": {
1497 "used_index": "i2_1"
1498 } /* range_details */,
1499 "resulting_rows": 47,
1500 "cost": 9.99,
1501 "chosen": true,
1502 "use_tmp_table": true
1503 }
1504 ] /* considered_access_paths */
1505 } /* best_access_path */,
1506 "condition_filtering_pct": 100,
1507 "rows_for_plan": 47,
1508 "cost_for_plan": 9.99,
1509 "sort_cost": 47,
1510 "new_cost_for_plan": 56.99,
1511 "chosen": true
1512 }
1513 ] /* considered_execution_plans */
1514 },
1515 {
1516 "attaching_conditions_to_tables": {
1517 "original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
1518 "attached_conditions_computation": [
1519 ] /* attached_conditions_computation */,
1520 "attached_conditions_summary": [
1521 {
1522 "table": "`t2`",
1523 "attached": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))"
1524 }
1525 ] /* attached_conditions_summary */
1526 } /* attaching_conditions_to_tables */
1527 },
1528 {
1529 "optimizing_distinct_group_by_order_by": {
1530 "simplifying_group_by": {
1531 "original_clause": "`t2`.`key2`",
1532 "items": [
1533 {
1534 "item": "`t2`.`key2`"
1535 }
1536 ] /* items */,
1537 "resulting_clause_is_simple": true,
1538 "resulting_clause": "`t2`.`key2`"
1539 } /* simplifying_group_by */
1540 } /* optimizing_distinct_group_by_order_by */
1541 },
1542 {
1543 "reconsidering_access_paths_for_index_ordering": {
1544 "clause": "GROUP BY",
1545 "steps": [
1546 ] /* steps */,
1547 "index_order_summary": {
1548 "table": "`t2`",
1549 "index_provides_order": true,
1550 "order_direction": "asc",
1551 "index": "i2_1",
1552 "plan_changed": false
1553 } /* index_order_summary */
1554 } /* reconsidering_access_paths_for_index_ordering */
1555 },
1556 {
1557 "finalizing_table_conditions": [
1558 {
1559 "table": "`t2`",
1560 "original_table_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
1561 "final_table_condition ": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))"
1562 }
1563 ] /* finalizing_table_conditions */
1564 },
1565 {
1566 "refine_plan": [
1567 {
1568 "table": "`t2`"
1569 }
1570 ] /* refine_plan */
1571 },
1572 {
1573 "considering_tmp_tables": [
1574 ] /* considering_tmp_tables */
1575 }
1576 ] /* steps */
1577 } /* join_optimization */
1578 },
1579 {
1580 "join_explain": {
1581 "select#": 1,
1582 "steps": [
1583 ] /* steps */
1584 } /* join_explain */
1585 }
1586 ] /* steps */
1587 } 0 0
1588
1589 EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
1590 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1591 1 SIMPLE t2 NULL ref i2_1,i2_2 i2_1 4 const 10 19.00 Using where
1592 Warnings:
1593 Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))
1594
1595 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1596 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1597 EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5) {
1598 "steps": [
1599 {
1600 "join_preparation": {
1601 "select#": 1,
1602 "steps": [
1603 {
1604 "expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))"
1605 }
1606 ] /* steps */
1607 } /* join_preparation */
1608 },
1609 {
1610 "join_optimization": {
1611 "select#": 1,
1612 "steps": [
1613 {
1614 "condition_processing": {
1615 "condition": "WHERE",
1616 "original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
1617 "steps": [
1618 {
1619 "transformation": "equality_propagation",
1620 "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
1621 },
1622 {
1623 "transformation": "constant_propagation",
1624 "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
1625 },
1626 {
1627 "transformation": "trivial_condition_removal",
1628 "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
1629 }
1630 ] /* steps */
1631 } /* condition_processing */
1632 },
1633 {
1634 "substitute_generated_columns": {
1635 } /* substitute_generated_columns */
1636 },
1637 {
1638 "table_dependencies": [
1639 {
1640 "table": "`t2`",
1641 "row_may_be_null": false,
1642 "map_bit": 0,
1643 "depends_on_map_bits": [
1644 ] /* depends_on_map_bits */
1645 }
1646 ] /* table_dependencies */
1647 },
1648 {
1649 "ref_optimizer_key_uses": [
1650 {
1651 "table": "`t2`",
1652 "field": "key2",
1653 "equals": "1",
1654 "null_rejecting": true
1655 },
1656 {
1657 "table": "`t2`",
1658 "field": "key2",
1659 "equals": "1",
1660 "null_rejecting": true
1661 }
1662 ] /* ref_optimizer_key_uses */
1663 },
1664 {
1665 "rows_estimation": [
1666 {
1667 "table": "`t2`",
1668 "range_analysis": {
1669 "table_scan": {
1670 "rows": 1024,
1671 "cost": 106.56
1672 } /* table_scan */,
1673 "potential_range_indexes": [
1674 {
1675 "index": "PRIMARY",
1676 "usable": false,
1677 "cause": "not_applicable"
1678 },
1679 {
1680 "index": "i1b",
1681 "usable": false,
1682 "cause": "not_applicable"
1683 },
1684 {
1685 "index": "i2_1",
1686 "usable": true,
1687 "key_parts": [
1688 "key2",
1689 "key2_1"
1690 ] /* key_parts */
1691 },
1692 {
1693 "index": "i2_2",
1694 "usable": true,
1695 "key_parts": [
1696 "key2",
1697 "key2_1"
1698 ] /* key_parts */
1699 }
1700 ] /* potential_range_indexes */,
1701 "setup_range_conditions": [
1702 ] /* setup_range_conditions */,
1703 "group_index_range": {
1704 "chosen": false,
1705 "cause": "not_group_by_or_distinct"
1706 } /* group_index_range */,
1707 "skip_scan_range": {
1708 "potential_skip_scan_indexes": [
1709 {
1710 "index": "i2_1",
1711 "usable": false,
1712 "cause": "query_references_nonkey_column"
1713 },
1714 {
1715 "index": "i2_2",
1716 "usable": false,
1717 "cause": "query_references_nonkey_column"
1718 }
1719 ] /* potential_skip_scan_indexes */
1720 } /* skip_scan_range */,
1721 "analyzing_range_alternatives": {
1722 "range_scan_alternatives": [
1723 {
1724 "index": "i2_1",
1725 "ranges": [
1726 "key2 = 1"
1727 ] /* ranges */,
1728 "index_dives_for_eq_ranges": true,
1729 "rowid_ordered": false,
1730 "using_mrr": false,
1731 "index_only": false,
1732 "in_memory": -1,
1733 "rows": 10,
1734 "cost": 3.76,
1735 "chosen": true
1736 },
1737 {
1738 "index": "i2_2",
1739 "ranges": [
1740 "key2 = 1"
1741 ] /* ranges */,
1742 "index_dives_for_eq_ranges": true,
1743 "rowid_ordered": false,
1744 "using_mrr": false,
1745 "index_only": false,
1746 "in_memory": -1,
1747 "rows": 10,
1748 "cost": 3.76,
1749 "chosen": false,
1750 "cause": "cost"
1751 }
1752 ] /* range_scan_alternatives */,
1753 "analyzing_roworder_intersect": {
1754 "usable": false,
1755 "cause": "too_few_roworder_scans"
1756 } /* analyzing_roworder_intersect */
1757 } /* analyzing_range_alternatives */,
1758 "chosen_range_access_summary": {
1759 "range_access_plan": {
1760 "type": "range_scan",
1761 "index": "i2_1",
1762 "rows": 10,
1763 "ranges": [
1764 "key2 = 1"
1765 ] /* ranges */
1766 } /* range_access_plan */,
1767 "rows_for_plan": 10,
1768 "cost_for_plan": 3.76,
1769 "chosen": true
1770 } /* chosen_range_access_summary */
1771 } /* range_analysis */
1772 }
1773 ] /* rows_estimation */
1774 },
1775 {
1776 "considered_execution_plans": [
1777 {
1778 "plan_prefix": [
1779 ] /* plan_prefix */,
1780 "table": "`t2`",
1781 "best_access_path": {
1782 "considered_access_paths": [
1783 {
1784 "access_type": "ref",
1785 "index": "i2_1",
1786 "rows": 10,
1787 "cost": 3.5,
1788 "chosen": true
1789 },
1790 {
1791 "access_type": "ref",
1792 "index": "i2_2",
1793 "rows": 10,
1794 "cost": 3.5,
1795 "chosen": false
1796 },
1797 {
1798 "access_type": "range",
1799 "range_details": {
1800 "used_index": "i2_1"
1801 } /* range_details */,
1802 "chosen": false,
1803 "cause": "heuristic_index_cheaper"
1804 }
1805 ] /* considered_access_paths */
1806 } /* best_access_path */,
1807 "condition_filtering_pct": 19,
1808 "rows_for_plan": 1.9,
1809 "cost_for_plan": 3.5,
1810 "chosen": true
1811 }
1812 ] /* considered_execution_plans */
1813 },
1814 {
1815 "attaching_conditions_to_tables": {
1816 "original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
1817 "attached_conditions_computation": [
1818 ] /* attached_conditions_computation */,
1819 "attached_conditions_summary": [
1820 {
1821 "table": "`t2`",
1822 "attached": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))"
1823 }
1824 ] /* attached_conditions_summary */
1825 } /* attaching_conditions_to_tables */
1826 },
1827 {
1828 "finalizing_table_conditions": [
1829 {
1830 "table": "`t2`",
1831 "original_table_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
1832 "final_table_condition ": "((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5))"
1833 }
1834 ] /* finalizing_table_conditions */
1835 },
1836 {
1837 "refine_plan": [
1838 {
1839 "table": "`t2`"
1840 }
1841 ] /* refine_plan */
1842 }
1843 ] /* steps */
1844 } /* join_optimization */
1845 },
1846 {
1847 "join_explain": {
1848 "select#": 1,
1849 "steps": [
1850 ] /* steps */
1851 } /* join_explain */
1852 }
1853 ] /* steps */
1854 } 0 0
1855
1856 EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
1857 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1858 1 SIMPLE t1 NULL index_merge i2,i3,i4 i2,i3 4,4 NULL 2 100.00 Using union(i2,i3); Using where
1859 Warnings:
1860 Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))
1861
1862 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1863 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1864 EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null {
1865 "steps": [
1866 {
1867 "join_preparation": {
1868 "select#": 1,
1869 "steps": [
1870 {
1871 "expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
1872 }
1873 ] /* steps */
1874 } /* join_preparation */
1875 },
1876 {
1877 "join_optimization": {
1878 "select#": 1,
1879 "steps": [
1880 {
1881 "condition_processing": {
1882 "condition": "WHERE",
1883 "original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
1884 "steps": [
1885 {
1886 "transformation": "equality_propagation",
1887 "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
1888 },
1889 {
1890 "transformation": "constant_propagation",
1891 "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
1892 },
1893 {
1894 "transformation": "trivial_condition_removal",
1895 "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
1896 }
1897 ] /* steps */
1898 } /* condition_processing */
1899 },
1900 {
1901 "substitute_generated_columns": {
1902 } /* substitute_generated_columns */
1903 },
1904 {
1905 "table_dependencies": [
1906 {
1907 "table": "`t1`",
1908 "row_may_be_null": false,
1909 "map_bit": 0,
1910 "depends_on_map_bits": [
1911 ] /* depends_on_map_bits */
1912 }
1913 ] /* table_dependencies */
1914 },
1915 {
1916 "ref_optimizer_key_uses": [
1917 ] /* ref_optimizer_key_uses */
1918 },
1919 {
1920 "rows_estimation": [
1921 {
1922 "table": "`t1`",
1923 "range_analysis": {
1924 "table_scan": {
1925 "rows": 1024,
1926 "cost": 107.06
1927 } /* table_scan */,
1928 "potential_range_indexes": [
1929 {
1930 "index": "i1",
1931 "usable": false,
1932 "cause": "not_applicable"
1933 },
1934 {
1935 "index": "i2",
1936 "usable": true,
1937 "key_parts": [
1938 "key2"
1939 ] /* key_parts */
1940 },
1941 {
1942 "index": "i3",
1943 "usable": true,
1944 "key_parts": [
1945 "key3"
1946 ] /* key_parts */
1947 },
1948 {
1949 "index": "i4",
1950 "usable": true,
1951 "key_parts": [
1952 "key4"
1953 ] /* key_parts */
1954 },
1955 {
1956 "index": "i5",
1957 "usable": false,
1958 "cause": "not_applicable"
1959 },
1960 {
1961 "index": "i6",
1962 "usable": false,
1963 "cause": "not_applicable"
1964 },
1965 {
1966 "index": "i7",
1967 "usable": false,
1968 "cause": "not_applicable"
1969 },
1970 {
1971 "index": "i8",
1972 "usable": false,
1973 "cause": "not_applicable"
1974 }
1975 ] /* potential_range_indexes */,
1976 "setup_range_conditions": [
1977 {
1978 "impossible_condition": {
1979 "cause": "null_field_in_non_null_column"
1980 } /* impossible_condition */
1981 }
1982 ] /* setup_range_conditions */,
1983 "group_index_range": {
1984 "chosen": false,
1985 "cause": "not_group_by_or_distinct"
1986 } /* group_index_range */,
1987 "skip_scan_range": {
1988 "potential_skip_scan_indexes": [
1989 {
1990 "index": "i2",
1991 "usable": false,
1992 "cause": "query_references_nonkey_column"
1993 },
1994 {
1995 "index": "i3",
1996 "usable": false,
1997 "cause": "query_references_nonkey_column"
1998 },
1999 {
2000 "index": "i4",
2001 "usable": false,
2002 "cause": "query_references_nonkey_column"
2003 }
2004 ] /* potential_skip_scan_indexes */
2005 } /* skip_scan_range */,
2006 "analyzing_range_alternatives": {
2007 "range_scan_alternatives": [
2008 ] /* range_scan_alternatives */,
2009 "analyzing_roworder_intersect": {
2010 "usable": false,
2011 "cause": "too_few_roworder_scans"
2012 } /* analyzing_roworder_intersect */
2013 } /* analyzing_range_alternatives */,
2014 "analyzing_index_merge_union": [
2015 {
2016 "indexes_to_merge": [
2017 {
2018 "range_scan_alternatives": [
2019 {
2020 "index": "i2",
2021 "ranges": [
2022 "key2 = 10"
2023 ] /* ranges */,
2024 "index_dives_for_eq_ranges": true,
2025 "rowid_ordered": true,
2026 "using_mrr": false,
2027 "index_only": true,
2028 "in_memory": -1,
2029 "rows": 1,
2030 "cost": 0.36,
2031 "chosen": true
2032 }
2033 ] /* range_scan_alternatives */,
2034 "index_to_merge": "i2",
2035 "cumulated_cost": 0.36
2036 },
2037 {
2038 "range_scan_alternatives": [
2039 {
2040 "index": "i3",
2041 "ranges": [
2042 "key3 = 3"
2043 ] /* ranges */,
2044 "index_dives_for_eq_ranges": true,
2045 "rowid_ordered": true,
2046 "using_mrr": false,
2047 "index_only": true,
2048 "in_memory": -1,
2049 "rows": 1,
2050 "cost": 0.36,
2051 "chosen": true
2052 }
2053 ] /* range_scan_alternatives */,
2054 "index_to_merge": "i3",
2055 "cumulated_cost": 0.72
2056 }
2057 ] /* indexes_to_merge */,
2058 "cost_of_reading_ranges": 0.72,
2059 "use_roworder_union": true,
2060 "cause": "always_cheaper_than_not_roworder_retrieval",
2061 "analyzing_roworder_scans": [
2062 {
2063 "type": "range_scan",
2064 "index": "i2",
2065 "rows": 1,
2066 "ranges": [
2067 "key2 = 10"
2068 ] /* ranges */,
2069 "analyzing_roworder_intersect": {
2070 "usable": false,
2071 "cause": "too_few_roworder_scans"
2072 } /* analyzing_roworder_intersect */
2073 },
2074 {
2075 "type": "range_scan",
2076 "index": "i3",
2077 "rows": 1,
2078 "ranges": [
2079 "key3 = 3"
2080 ] /* ranges */,
2081 "analyzing_roworder_intersect": {
2082 "usable": false,
2083 "cause": "too_few_roworder_scans"
2084 } /* analyzing_roworder_intersect */
2085 }
2086 ] /* analyzing_roworder_scans */,
2087 "index_roworder_union_cost": 1.29,
2088 "members": 2,
2089 "chosen": true
2090 }
2091 ] /* analyzing_index_merge_union */,
2092 "chosen_range_access_summary": {
2093 "range_access_plan": {
2094 "type": "index_roworder_union",
2095 "union_of": [
2096 {
2097 "type": "range_scan",
2098 "index": "i2",
2099 "rows": 1,
2100 "ranges": [
2101 "key2 = 10"
2102 ] /* ranges */
2103 },
2104 {
2105 "type": "range_scan",
2106 "index": "i3",
2107 "rows": 1,
2108 "ranges": [
2109 "key3 = 3"
2110 ] /* ranges */
2111 }
2112 ] /* union_of */
2113 } /* range_access_plan */,
2114 "rows_for_plan": 2,
2115 "cost_for_plan": 1.29,
2116 "chosen": true
2117 } /* chosen_range_access_summary */
2118 } /* range_analysis */
2119 }
2120 ] /* rows_estimation */
2121 },
2122 {
2123 "considered_execution_plans": [
2124 {
2125 "plan_prefix": [
2126 ] /* plan_prefix */,
2127 "table": "`t1`",
2128 "best_access_path": {
2129 "considered_access_paths": [
2130 {
2131 "rows_to_scan": 2,
2132 "filtering_effect": [
2133 ] /* filtering_effect */,
2134 "final_filtering_effect": 1,
2135 "access_type": "range",
2136 "range_details": {
2137 "used_index": "union(i2,i3)"
2138 } /* range_details */,
2139 "resulting_rows": 2,
2140 "cost": 1.49,
2141 "chosen": true
2142 }
2143 ] /* considered_access_paths */
2144 } /* best_access_path */,
2145 "condition_filtering_pct": 100,
2146 "rows_for_plan": 2,
2147 "cost_for_plan": 1.49,
2148 "chosen": true
2149 }
2150 ] /* considered_execution_plans */
2151 },
2152 {
2153 "attaching_conditions_to_tables": {
2154 "original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
2155 "attached_conditions_computation": [
2156 ] /* attached_conditions_computation */,
2157 "attached_conditions_summary": [
2158 {
2159 "table": "`t1`",
2160 "attached": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
2161 }
2162 ] /* attached_conditions_summary */
2163 } /* attaching_conditions_to_tables */
2164 },
2165 {
2166 "finalizing_table_conditions": [
2167 {
2168 "table": "`t1`",
2169 "original_table_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
2170 "final_table_condition ": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
2171 }
2172 ] /* finalizing_table_conditions */
2173 },
2174 {
2175 "refine_plan": [
2176 {
2177 "table": "`t1`"
2178 }
2179 ] /* refine_plan */
2180 }
2181 ] /* steps */
2182 } /* join_optimization */
2183 },
2184 {
2185 "join_explain": {
2186 "select#": 1,
2187 "steps": [
2188 ] /* steps */
2189 } /* join_explain */
2190 }
2191 ] /* steps */
2192 } 0 0
2193
2194 EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
2195 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2196 1 SIMPLE t2 NULL ALL i2_1,i2_2 NULL NULL NULL 1024 33.98 Using where
2197 Warnings:
2198 Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))
2199
2200 SELECT * FROM information_schema.OPTIMIZER_TRACE;
2201 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2202 EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2 {
2203 "steps": [
2204 {
2205 "join_preparation": {
2206 "select#": 1,
2207 "steps": [
2208 {
2209 "expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
2210 }
2211 ] /* steps */
2212 } /* join_preparation */
2213 },
2214 {
2215 "join_optimization": {
2216 "select#": 1,
2217 "steps": [
2218 {
2219 "condition_processing": {
2220 "condition": "WHERE",
2221 "original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
2222 "steps": [
2223 {
2224 "transformation": "equality_propagation",
2225 "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
2226 },
2227 {
2228 "transformation": "constant_propagation",
2229 "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
2230 },
2231 {
2232 "transformation": "trivial_condition_removal",
2233 "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
2234 }
2235 ] /* steps */
2236 } /* condition_processing */
2237 },
2238 {
2239 "substitute_generated_columns": {
2240 } /* substitute_generated_columns */
2241 },
2242 {
2243 "table_dependencies": [
2244 {
2245 "table": "`t2`",
2246 "row_may_be_null": false,
2247 "map_bit": 0,
2248 "depends_on_map_bits": [
2249 ] /* depends_on_map_bits */
2250 }
2251 ] /* table_dependencies */
2252 },
2253 {
2254 "ref_optimizer_key_uses": [
2255 ] /* ref_optimizer_key_uses */
2256 },
2257 {
2258 "rows_estimation": [
2259 {
2260 "table": "`t2`",
2261 "range_analysis": {
2262 "table_scan": {
2263 "rows": 1024,
2264 "cost": 106.56
2265 } /* table_scan */,
2266 "potential_range_indexes": [
2267 {
2268 "index": "PRIMARY",
2269 "usable": false,
2270 "cause": "not_applicable"
2271 },
2272 {
2273 "index": "i1b",
2274 "usable": false,
2275 "cause": "not_applicable"
2276 },
2277 {
2278 "index": "i2_1",
2279 "usable": true,
2280 "key_parts": [
2281 "key2",
2282 "key2_1"
2283 ] /* key_parts */
2284 },
2285 {
2286 "index": "i2_2",
2287 "usable": true,
2288 "key_parts": [
2289 "key2",
2290 "key2_1"
2291 ] /* key_parts */
2292 }
2293 ] /* potential_range_indexes */,
2294 "setup_range_conditions": [
2295 ] /* setup_range_conditions */,
2296 "range_scan_possible": false,
2297 "cause": "condition_always_true",
2298 "group_index_range": {
2299 "chosen": false,
2300 "cause": "not_group_by_or_distinct"
2301 } /* group_index_range */,
2302 "skip_scan_range": {
2303 "chosen": false,
2304 "cause": "disjuntive_predicate_present"
2305 } /* skip_scan_range */
2306 } /* range_analysis */
2307 }
2308 ] /* rows_estimation */
2309 },
2310 {
2311 "considered_execution_plans": [
2312 {
2313 "plan_prefix": [
2314 ] /* plan_prefix */,
2315 "table": "`t2`",
2316 "best_access_path": {
2317 "considered_access_paths": [
2318 {
2319 "rows_to_scan": 1024,
2320 "filtering_effect": [
2321 ] /* filtering_effect */,
2322 "final_filtering_effect": 0.34,
2323 "access_type": "scan",
2324 "resulting_rows": 347.97,
2325 "cost": 104.46,
2326 "chosen": true
2327 }
2328 ] /* considered_access_paths */
2329 } /* best_access_path */,
2330 "condition_filtering_pct": 100,
2331 "rows_for_plan": 347.97,
2332 "cost_for_plan": 104.46,
2333 "chosen": true
2334 }
2335 ] /* considered_execution_plans */
2336 },
2337 {
2338 "attaching_conditions_to_tables": {
2339 "original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
2340 "attached_conditions_computation": [
2341 ] /* attached_conditions_computation */,
2342 "attached_conditions_summary": [
2343 {
2344 "table": "`t2`",
2345 "attached": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
2346 }
2347 ] /* attached_conditions_summary */
2348 } /* attaching_conditions_to_tables */
2349 },
2350 {
2351 "finalizing_table_conditions": [
2352 {
2353 "table": "`t2`",
2354 "original_table_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
2355 "final_table_condition ": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
2356 }
2357 ] /* finalizing_table_conditions */
2358 },
2359 {
2360 "refine_plan": [
2361 {
2362 "table": "`t2`"
2363 }
2364 ] /* refine_plan */
2365 }
2366 ] /* steps */
2367 } /* join_optimization */
2368 },
2369 {
2370 "join_explain": {
2371 "select#": 1,
2372 "steps": [
2373 ] /* steps */
2374 } /* join_explain */
2375 }
2376 ] /* steps */
2377 } 0 0
2378
2379 EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
2380 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2381 1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 1 100.00 Using index condition
2382 Warnings:
2383 Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))
2384
2385 SELECT * FROM information_schema.OPTIMIZER_TRACE;
2386 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2387 EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10 {
2388 "steps": [
2389 {
2390 "join_preparation": {
2391 "select#": 1,
2392 "steps": [
2393 {
2394 "expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
2395 }
2396 ] /* steps */
2397 } /* join_preparation */
2398 },
2399 {
2400 "join_optimization": {
2401 "select#": 1,
2402 "steps": [
2403 {
2404 "condition_processing": {
2405 "condition": "WHERE",
2406 "original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
2407 "steps": [
2408 {
2409 "transformation": "equality_propagation",
2410 "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
2411 },
2412 {
2413 "transformation": "constant_propagation",
2414 "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
2415 },
2416 {
2417 "transformation": "trivial_condition_removal",
2418 "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
2419 }
2420 ] /* steps */
2421 } /* condition_processing */
2422 },
2423 {
2424 "substitute_generated_columns": {
2425 } /* substitute_generated_columns */
2426 },
2427 {
2428 "table_dependencies": [
2429 {
2430 "table": "`t2`",
2431 "row_may_be_null": false,
2432 "map_bit": 0,
2433 "depends_on_map_bits": [
2434 ] /* depends_on_map_bits */
2435 }
2436 ] /* table_dependencies */
2437 },
2438 {
2439 "ref_optimizer_key_uses": [
2440 {
2441 "table": "`t2`",
2442 "field": "key1a",
2443 "equals": "5",
2444 "null_rejecting": true
2445 }
2446 ] /* ref_optimizer_key_uses */
2447 },
2448 {
2449 "rows_estimation": [
2450 {
2451 "table": "`t2`",
2452 "range_analysis": {
2453 "table_scan": {
2454 "rows": 1024,
2455 "cost": 106.56
2456 } /* table_scan */,
2457 "potential_range_indexes": [
2458 {
2459 "index": "PRIMARY",
2460 "usable": true,
2461 "key_parts": [
2462 "key1a",
2463 "key1b"
2464 ] /* key_parts */
2465 },
2466 {
2467 "index": "i1b",
2468 "usable": true,
2469 "key_parts": [
2470 "key1b",
2471 "key1a"
2472 ] /* key_parts */
2473 },
2474 {
2475 "index": "i2_1",
2476 "usable": false,
2477 "cause": "not_applicable"
2478 },
2479 {
2480 "index": "i2_2",
2481 "usable": false,
2482 "cause": "not_applicable"
2483 }
2484 ] /* potential_range_indexes */,
2485 "setup_range_conditions": [
2486 ] /* setup_range_conditions */,
2487 "group_index_range": {
2488 "chosen": false,
2489 "cause": "not_group_by_or_distinct"
2490 } /* group_index_range */,
2491 "skip_scan_range": {
2492 "potential_skip_scan_indexes": [
2493 {
2494 "index": "PRIMARY",
2495 "usable": false,
2496 "cause": "query_references_nonkey_column"
2497 },
2498 {
2499 "index": "i1b",
2500 "usable": false,
2501 "cause": "query_references_nonkey_column"
2502 }
2503 ] /* potential_skip_scan_indexes */
2504 } /* skip_scan_range */,
2505 "analyzing_range_alternatives": {
2506 "range_scan_alternatives": [
2507 {
2508 "index": "PRIMARY",
2509 "ranges": [
2510 "key1a = 5 AND key1b < 10"
2511 ] /* ranges */,
2512 "index_dives_for_eq_ranges": true,
2513 "rowid_ordered": false,
2514 "using_mrr": false,
2515 "index_only": false,
2516 "in_memory": -1,
2517 "rows": 1,
2518 "cost": 0.61,
2519 "chosen": true
2520 },
2521 {
2522 "index": "i1b",
2523 "ranges": [
2524 "key1b < 10"
2525 ] /* ranges */,
2526 "index_dives_for_eq_ranges": true,
2527 "rowid_ordered": false,
2528 "using_mrr": false,
2529 "index_only": false,
2530 "in_memory": -1,
2531 "rows": 9,
2532 "cost": 3.41,
2533 "chosen": false,
2534 "cause": "cost"
2535 }
2536 ] /* range_scan_alternatives */,
2537 "analyzing_roworder_intersect": {
2538 "usable": false,
2539 "cause": "too_few_roworder_scans"
2540 } /* analyzing_roworder_intersect */
2541 } /* analyzing_range_alternatives */,
2542 "chosen_range_access_summary": {
2543 "range_access_plan": {
2544 "type": "range_scan",
2545 "index": "PRIMARY",
2546 "rows": 1,
2547 "ranges": [
2548 "key1a = 5 AND key1b < 10"
2549 ] /* ranges */
2550 } /* range_access_plan */,
2551 "rows_for_plan": 1,
2552 "cost_for_plan": 0.61,
2553 "chosen": true
2554 } /* chosen_range_access_summary */
2555 } /* range_analysis */
2556 }
2557 ] /* rows_estimation */
2558 },
2559 {
2560 "considered_execution_plans": [
2561 {
2562 "plan_prefix": [
2563 ] /* plan_prefix */,
2564 "table": "`t2`",
2565 "best_access_path": {
2566 "considered_access_paths": [
2567 {
2568 "access_type": "ref",
2569 "index": "PRIMARY",
2570 "rows": 10,
2571 "cost": 3.5,
2572 "chosen": true
2573 },
2574 {
2575 "rows_to_scan": 1,
2576 "filtering_effect": [
2577 ] /* filtering_effect */,
2578 "final_filtering_effect": 1,
2579 "access_type": "range",
2580 "range_details": {
2581 "used_index": "PRIMARY"
2582 } /* range_details */,
2583 "resulting_rows": 1,
2584 "cost": 0.71,
2585 "chosen": true
2586 }
2587 ] /* considered_access_paths */
2588 } /* best_access_path */,
2589 "condition_filtering_pct": 100,
2590 "rows_for_plan": 1,
2591 "cost_for_plan": 0.71,
2592 "chosen": true
2593 }
2594 ] /* considered_execution_plans */
2595 },
2596 {
2597 "attaching_conditions_to_tables": {
2598 "original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
2599 "attached_conditions_computation": [
2600 ] /* attached_conditions_computation */,
2601 "attached_conditions_summary": [
2602 {
2603 "table": "`t2`",
2604 "attached": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
2605 }
2606 ] /* attached_conditions_summary */
2607 } /* attaching_conditions_to_tables */
2608 },
2609 {
2610 "finalizing_table_conditions": [
2611 {
2612 "table": "`t2`",
2613 "original_table_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
2614 "final_table_condition ": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
2615 }
2616 ] /* finalizing_table_conditions */
2617 },
2618 {
2619 "refine_plan": [
2620 {
2621 "table": "`t2`",
2622 "pushed_index_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
2623 "table_condition_attached": null
2624 }
2625 ] /* refine_plan */
2626 }
2627 ] /* steps */
2628 } /* join_optimization */
2629 },
2630 {
2631 "join_explain": {
2632 "select#": 1,
2633 "steps": [
2634 ] /* steps */
2635 } /* join_explain */
2636 }
2637 ] /* steps */
2638 } 0 0
2639
2640 EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
2641 (key1a = 4 and key1b < 7 and key1b > 3);
2642 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2643 1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 2 100.00 Using index condition
2644 Warnings:
2645 Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))
2646
2647 SELECT * FROM information_schema.OPTIMIZER_TRACE;
2648 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2649 EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
2650 (key1a = 4 and key1b < 7 and key1b > 3) {
2651 "steps": [
2652 {
2653 "join_preparation": {
2654 "select#": 1,
2655 "steps": [
2656 {
2657 "expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where (((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
2658 }
2659 ] /* steps */
2660 } /* join_preparation */
2661 },
2662 {
2663 "join_optimization": {
2664 "select#": 1,
2665 "steps": [
2666 {
2667 "condition_processing": {
2668 "condition": "WHERE",
2669 "original_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
2670 "steps": [
2671 {
2672 "transformation": "equality_propagation",
2673 "resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
2674 },
2675 {
2676 "transformation": "constant_propagation",
2677 "resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
2678 },
2679 {
2680 "transformation": "trivial_condition_removal",
2681 "resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
2682 }
2683 ] /* steps */
2684 } /* condition_processing */
2685 },
2686 {
2687 "substitute_generated_columns": {
2688 } /* substitute_generated_columns */
2689 },
2690 {
2691 "table_dependencies": [
2692 {
2693 "table": "`t2`",
2694 "row_may_be_null": false,
2695 "map_bit": 0,
2696 "depends_on_map_bits": [
2697 ] /* depends_on_map_bits */
2698 }
2699 ] /* table_dependencies */
2700 },
2701 {
2702 "ref_optimizer_key_uses": [
2703 ] /* ref_optimizer_key_uses */
2704 },
2705 {
2706 "rows_estimation": [
2707 {
2708 "table": "`t2`",
2709 "range_analysis": {
2710 "table_scan": {
2711 "rows": 1024,
2712 "cost": 106.56
2713 } /* table_scan */,
2714 "potential_range_indexes": [
2715 {
2716 "index": "PRIMARY",
2717 "usable": true,
2718 "key_parts": [
2719 "key1a",
2720 "key1b"
2721 ] /* key_parts */
2722 },
2723 {
2724 "index": "i1b",
2725 "usable": true,
2726 "key_parts": [
2727 "key1b",
2728 "key1a"
2729 ] /* key_parts */
2730 },
2731 {
2732 "index": "i2_1",
2733 "usable": false,
2734 "cause": "not_applicable"
2735 },
2736 {
2737 "index": "i2_2",
2738 "usable": false,
2739 "cause": "not_applicable"
2740 }
2741 ] /* potential_range_indexes */,
2742 "setup_range_conditions": [
2743 ] /* setup_range_conditions */,
2744 "group_index_range": {
2745 "chosen": false,
2746 "cause": "not_group_by_or_distinct"
2747 } /* group_index_range */,
2748 "skip_scan_range": {
2749 "potential_skip_scan_indexes": [
2750 {
2751 "index": "PRIMARY",
2752 "usable": false,
2753 "cause": "query_references_nonkey_column"
2754 },
2755 {
2756 "index": "i1b",
2757 "usable": false,
2758 "cause": "query_references_nonkey_column"
2759 }
2760 ] /* potential_skip_scan_indexes */
2761 } /* skip_scan_range */,
2762 "analyzing_range_alternatives": {
2763 "range_scan_alternatives": [
2764 {
2765 "index": "PRIMARY",
2766 "ranges": [
2767 "key1a = 4 AND 3 < key1b < 7",
2768 "key1a = 5 AND 2 < key1b < 10"
2769 ] /* ranges */,
2770 "index_dives_for_eq_ranges": true,
2771 "rowid_ordered": false,
2772 "using_mrr": false,
2773 "index_only": false,
2774 "in_memory": -1,
2775 "rows": 2,
2776 "cost": 1.21,
2777 "chosen": true
2778 },
2779 {
2780 "index": "i1b",
2781 "ranges": [
2782 "2 < key1b <= 3",
2783 "3 < key1b < 7",
2784 "7 <= key1b < 10"
2785 ] /* ranges */,
2786 "index_dives_for_eq_ranges": true,
2787 "rowid_ordered": false,
2788 "using_mrr": false,
2789 "index_only": false,
2790 "in_memory": -1,
2791 "rows": 6,
2792 "cost": 2.86,
2793 "chosen": false,
2794 "cause": "cost"
2795 }
2796 ] /* range_scan_alternatives */,
2797 "analyzing_roworder_intersect": {
2798 "usable": false,
2799 "cause": "too_few_roworder_scans"
2800 } /* analyzing_roworder_intersect */
2801 } /* analyzing_range_alternatives */,
2802 "chosen_range_access_summary": {
2803 "range_access_plan": {
2804 "type": "range_scan",
2805 "index": "PRIMARY",
2806 "rows": 2,
2807 "ranges": [
2808 "key1a = 4 AND 3 < key1b < 7",
2809 "key1a = 5 AND 2 < key1b < 10"
2810 ] /* ranges */
2811 } /* range_access_plan */,
2812 "rows_for_plan": 2,
2813 "cost_for_plan": 1.21,
2814 "chosen": true
2815 } /* chosen_range_access_summary */
2816 } /* range_analysis */
2817 }
2818 ] /* rows_estimation */
2819 },
2820 {
2821 "considered_execution_plans": [
2822 {
2823 "plan_prefix": [
2824 ] /* plan_prefix */,
2825 "table": "`t2`",
2826 "best_access_path": {
2827 "considered_access_paths": [
2828 {
2829 "rows_to_scan": 2,
2830 "filtering_effect": [
2831 ] /* filtering_effect */,
2832 "final_filtering_effect": 1,
2833 "access_type": "range",
2834 "range_details": {
2835 "used_index": "PRIMARY"
2836 } /* range_details */,
2837 "resulting_rows": 2,
2838 "cost": 1.41,
2839 "chosen": true
2840 }
2841 ] /* considered_access_paths */
2842 } /* best_access_path */,
2843 "condition_filtering_pct": 100,
2844 "rows_for_plan": 2,
2845 "cost_for_plan": 1.41,
2846 "chosen": true
2847 }
2848 ] /* considered_execution_plans */
2849 },
2850 {
2851 "attaching_conditions_to_tables": {
2852 "original_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
2853 "attached_conditions_computation": [
2854 ] /* attached_conditions_computation */,
2855 "attached_conditions_summary": [
2856 {
2857 "table": "`t2`",
2858 "attached": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
2859 }
2860 ] /* attached_conditions_summary */
2861 } /* attaching_conditions_to_tables */
2862 },
2863 {
2864 "finalizing_table_conditions": [
2865 {
2866 "table": "`t2`",
2867 "original_table_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
2868 "final_table_condition ": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
2869 }
2870 ] /* finalizing_table_conditions */
2871 },
2872 {
2873 "refine_plan": [
2874 {
2875 "table": "`t2`",
2876 "pushed_index_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
2877 "table_condition_attached": null
2878 }
2879 ] /* refine_plan */
2880 }
2881 ] /* steps */
2882 } /* join_optimization */
2883 },
2884 {
2885 "join_explain": {
2886 "select#": 1,
2887 "steps": [
2888 ] /* steps */
2889 } /* join_explain */
2890 }
2891 ] /* steps */
2892 } 0 0
2893
2894 EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and
2895 (key1a = 4 or key1a = 5);
2896 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2897 1 SIMPLE t2 NULL range PRIMARY,i1b i1b 4 NULL 2 19.00 Using index condition
2898 Warnings:
2899 Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))
2900
2901 SELECT * FROM information_schema.OPTIMIZER_TRACE;
2902 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2903 EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and
2904 (key1a = 4 or key1a = 5) {
2905 "steps": [
2906 {
2907 "join_preparation": {
2908 "select#": 1,
2909 "steps": [
2910 {
2911 "expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
2912 }
2913 ] /* steps */
2914 } /* join_preparation */
2915 },
2916 {
2917 "join_optimization": {
2918 "select#": 1,
2919 "steps": [
2920 {
2921 "condition_processing": {
2922 "condition": "WHERE",
2923 "original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
2924 "steps": [
2925 {
2926 "transformation": "equality_propagation",
2927 "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
2928 },
2929 {
2930 "transformation": "constant_propagation",
2931 "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
2932 },
2933 {
2934 "transformation": "trivial_condition_removal",
2935 "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
2936 }
2937 ] /* steps */
2938 } /* condition_processing */
2939 },
2940 {
2941 "substitute_generated_columns": {
2942 } /* substitute_generated_columns */
2943 },
2944 {
2945 "table_dependencies": [
2946 {
2947 "table": "`t2`",
2948 "row_may_be_null": false,
2949 "map_bit": 0,
2950 "depends_on_map_bits": [
2951 ] /* depends_on_map_bits */
2952 }
2953 ] /* table_dependencies */
2954 },
2955 {
2956 "ref_optimizer_key_uses": [
2957 ] /* ref_optimizer_key_uses */
2958 },
2959 {
2960 "rows_estimation": [
2961 {
2962 "table": "`t2`",
2963 "range_analysis": {
2964 "table_scan": {
2965 "rows": 1024,
2966 "cost": 106.56
2967 } /* table_scan */,
2968 "potential_range_indexes": [
2969 {
2970 "index": "PRIMARY",
2971 "usable": true,
2972 "key_parts": [
2973 "key1a",
2974 "key1b"
2975 ] /* key_parts */
2976 },
2977 {
2978 "index": "i1b",
2979 "usable": true,
2980 "key_parts": [
2981 "key1b",
2982 "key1a"
2983 ] /* key_parts */
2984 },
2985 {
2986 "index": "i2_1",
2987 "usable": false,
2988 "cause": "not_applicable"
2989 },
2990 {
2991 "index": "i2_2",
2992 "usable": false,
2993 "cause": "not_applicable"
2994 }
2995 ] /* potential_range_indexes */,
2996 "setup_range_conditions": [
2997 ] /* setup_range_conditions */,
2998 "group_index_range": {
2999 "chosen": false,
3000 "cause": "not_group_by_or_distinct"
3001 } /* group_index_range */,
3002 "skip_scan_range": {
3003 "potential_skip_scan_indexes": [
3004 {
3005 "index": "PRIMARY",
3006 "usable": false,
3007 "cause": "query_references_nonkey_column"
3008 },
3009 {
3010 "index": "i1b",
3011 "usable": false,
3012 "cause": "query_references_nonkey_column"
3013 }
3014 ] /* potential_skip_scan_indexes */
3015 } /* skip_scan_range */,
3016 "analyzing_range_alternatives": {
3017 "range_scan_alternatives": [
3018 {
3019 "index": "PRIMARY",
3020 "ranges": [
3021 "key1a = 4 AND 7 < key1b < 10",
3022 "key1a = 5 AND 7 < key1b < 10"
3023 ] /* ranges */,
3024 "index_dives_for_eq_ranges": true,
3025 "rowid_ordered": false,
3026 "using_mrr": false,
3027 "index_only": false,
3028 "in_memory": -1,
3029 "rows": 2,
3030 "cost": 1.21,
3031 "chosen": true
3032 },
3033 {
3034 "index": "i1b",
3035 "ranges": [
3036 "7 < key1b < 10"
3037 ] /* ranges */,
3038 "index_dives_for_eq_ranges": true,
3039 "rowid_ordered": false,
3040 "using_mrr": false,
3041 "index_only": false,
3042 "in_memory": -1,
3043 "rows": 2,
3044 "cost": 0.96,
3045 "chosen": true
3046 }
3047 ] /* range_scan_alternatives */,
3048 "analyzing_roworder_intersect": {
3049 "usable": false,
3050 "cause": "too_few_roworder_scans"
3051 } /* analyzing_roworder_intersect */
3052 } /* analyzing_range_alternatives */,
3053 "chosen_range_access_summary": {
3054 "range_access_plan": {
3055 "type": "range_scan",
3056 "index": "i1b",
3057 "rows": 2,
3058 "ranges": [
3059 "7 < key1b < 10"
3060 ] /* ranges */
3061 } /* range_access_plan */,
3062 "rows_for_plan": 2,
3063 "cost_for_plan": 0.96,
3064 "chosen": true
3065 } /* chosen_range_access_summary */
3066 } /* range_analysis */
3067 }
3068 ] /* rows_estimation */
3069 },
3070 {
3071 "considered_execution_plans": [
3072 {
3073 "plan_prefix": [
3074 ] /* plan_prefix */,
3075 "table": "`t2`",
3076 "best_access_path": {
3077 "considered_access_paths": [
3078 {
3079 "rows_to_scan": 2,
3080 "filtering_effect": [
3081 ] /* filtering_effect */,
3082 "final_filtering_effect": 0.19,
3083 "access_type": "range",
3084 "range_details": {
3085 "used_index": "i1b"
3086 } /* range_details */,
3087 "resulting_rows": 0.38,
3088 "cost": 1.16,
3089 "chosen": true
3090 }
3091 ] /* considered_access_paths */
3092 } /* best_access_path */,
3093 "condition_filtering_pct": 100,
3094 "rows_for_plan": 0.38,
3095 "cost_for_plan": 1.16,
3096 "chosen": true
3097 }
3098 ] /* considered_execution_plans */
3099 },
3100 {
3101 "attaching_conditions_to_tables": {
3102 "original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
3103 "attached_conditions_computation": [
3104 ] /* attached_conditions_computation */,
3105 "attached_conditions_summary": [
3106 {
3107 "table": "`t2`",
3108 "attached": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
3109 }
3110 ] /* attached_conditions_summary */
3111 } /* attaching_conditions_to_tables */
3112 },
3113 {
3114 "finalizing_table_conditions": [
3115 {
3116 "table": "`t2`",
3117 "original_table_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
3118 "final_table_condition ": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
3119 }
3120 ] /* finalizing_table_conditions */
3121 },
3122 {
3123 "refine_plan": [
3124 {
3125 "table": "`t2`",
3126 "pushed_index_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
3127 "table_condition_attached": null
3128 }
3129 ] /* refine_plan */
3130 }
3131 ] /* steps */
3132 } /* join_optimization */
3133 },
3134 {
3135 "join_explain": {
3136 "select#": 1,
3137 "steps": [
3138 ] /* steps */
3139 } /* join_explain */
3140 }
3141 ] /* steps */
3142 } 0 0
3143
3144 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
3145 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3146 1 SIMPLE t1 NULL ALL i1,i2 NULL NULL NULL 1024 55.55 Using where
3147 Warnings:
3148 Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))
3149
3150 SELECT * FROM information_schema.OPTIMIZER_TRACE;
3151 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
3152 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2) {
3153 "steps": [
3154 {
3155 "join_preparation": {
3156 "select#": 1,
3157 "steps": [
3158 {
3159 "expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3160 }
3161 ] /* steps */
3162 } /* join_preparation */
3163 },
3164 {
3165 "join_optimization": {
3166 "select#": 1,
3167 "steps": [
3168 {
3169 "condition_processing": {
3170 "condition": "WHERE",
3171 "original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
3172 "steps": [
3173 {
3174 "transformation": "equality_propagation",
3175 "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3176 },
3177 {
3178 "transformation": "constant_propagation",
3179 "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3180 },
3181 {
3182 "transformation": "trivial_condition_removal",
3183 "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3184 }
3185 ] /* steps */
3186 } /* condition_processing */
3187 },
3188 {
3189 "substitute_generated_columns": {
3190 } /* substitute_generated_columns */
3191 },
3192 {
3193 "table_dependencies": [
3194 {
3195 "table": "`t1`",
3196 "row_may_be_null": false,
3197 "map_bit": 0,
3198 "depends_on_map_bits": [
3199 ] /* depends_on_map_bits */
3200 }
3201 ] /* table_dependencies */
3202 },
3203 {
3204 "ref_optimizer_key_uses": [
3205 ] /* ref_optimizer_key_uses */
3206 },
3207 {
3208 "rows_estimation": [
3209 {
3210 "table": "`t1`",
3211 "range_analysis": {
3212 "table_scan": {
3213 "rows": 1024,
3214 "cost": 107.06
3215 } /* table_scan */,
3216 "potential_range_indexes": [
3217 {
3218 "index": "i1",
3219 "usable": true,
3220 "key_parts": [
3221 "key1"
3222 ] /* key_parts */
3223 },
3224 {
3225 "index": "i2",
3226 "usable": true,
3227 "key_parts": [
3228 "key2"
3229 ] /* key_parts */
3230 },
3231 {
3232 "index": "i3",
3233 "usable": false,
3234 "cause": "not_applicable"
3235 },
3236 {
3237 "index": "i4",
3238 "usable": false,
3239 "cause": "not_applicable"
3240 },
3241 {
3242 "index": "i5",
3243 "usable": false,
3244 "cause": "not_applicable"
3245 },
3246 {
3247 "index": "i6",
3248 "usable": false,
3249 "cause": "not_applicable"
3250 },
3251 {
3252 "index": "i7",
3253 "usable": false,
3254 "cause": "not_applicable"
3255 },
3256 {
3257 "index": "i8",
3258 "usable": false,
3259 "cause": "not_applicable"
3260 }
3261 ] /* potential_range_indexes */,
3262 "setup_range_conditions": [
3263 ] /* setup_range_conditions */,
3264 "group_index_range": {
3265 "chosen": false,
3266 "cause": "not_group_by_or_distinct"
3267 } /* group_index_range */,
3268 "skip_scan_range": {
3269 "potential_skip_scan_indexes": [
3270 {
3271 "index": "i1",
3272 "usable": false,
3273 "cause": "query_references_nonkey_column"
3274 },
3275 {
3276 "index": "i2",
3277 "usable": false,
3278 "cause": "query_references_nonkey_column"
3279 }
3280 ] /* potential_skip_scan_indexes */
3281 } /* skip_scan_range */,
3282 "analyzing_range_alternatives": {
3283 "range_scan_alternatives": [
3284 ] /* range_scan_alternatives */,
3285 "analyzing_roworder_intersect": {
3286 "usable": false,
3287 "cause": "too_few_roworder_scans"
3288 } /* analyzing_roworder_intersect */
3289 } /* analyzing_range_alternatives */,
3290 "analyzing_index_merge_union": [
3291 {
3292 "indexes_to_merge": [
3293 {
3294 "range_scan_alternatives": [
3295 {
3296 "index": "i1",
3297 "ranges": [
3298 "1 < key1"
3299 ] /* ranges */,
3300 "index_dives_for_eq_ranges": true,
3301 "rowid_ordered": false,
3302 "using_mrr": false,
3303 "index_only": true,
3304 "in_memory": -1,
3305 "rows": 1023,
3306 "cost": 108,
3307 "chosen": false,
3308 "cause": "cost"
3309 }
3310 ] /* range_scan_alternatives */,
3311 "chosen": false,
3312 "cause": "cost"
3313 },
3314 {
3315 "range_scan_alternatives": [
3316 {
3317 "index": "i2",
3318 "ranges": [
3319 "2 < key2"
3320 ] /* ranges */,
3321 "index_dives_for_eq_ranges": true,
3322 "rowid_ordered": false,
3323 "using_mrr": false,
3324 "index_only": true,
3325 "in_memory": -1,
3326 "rows": 1022,
3327 "cost": 107.89,
3328 "chosen": false,
3329 "cause": "cost"
3330 }
3331 ] /* range_scan_alternatives */,
3332 "chosen": false,
3333 "cause": "cost"
3334 }
3335 ] /* indexes_to_merge */,
3336 "cost_of_reading_ranges": 0,
3337 "chosen": false,
3338 "cause": "cost"
3339 }
3340 ] /* analyzing_index_merge_union */
3341 } /* range_analysis */
3342 }
3343 ] /* rows_estimation */
3344 },
3345 {
3346 "considered_execution_plans": [
3347 {
3348 "plan_prefix": [
3349 ] /* plan_prefix */,
3350 "table": "`t1`",
3351 "best_access_path": {
3352 "considered_access_paths": [
3353 {
3354 "rows_to_scan": 1024,
3355 "filtering_effect": [
3356 ] /* filtering_effect */,
3357 "final_filtering_effect": 0.56,
3358 "access_type": "scan",
3359 "resulting_rows": 568.84,
3360 "cost": 104.96,
3361 "chosen": true
3362 }
3363 ] /* considered_access_paths */
3364 } /* best_access_path */,
3365 "condition_filtering_pct": 100,
3366 "rows_for_plan": 568.84,
3367 "cost_for_plan": 104.96,
3368 "chosen": true
3369 }
3370 ] /* considered_execution_plans */
3371 },
3372 {
3373 "attaching_conditions_to_tables": {
3374 "original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
3375 "attached_conditions_computation": [
3376 ] /* attached_conditions_computation */,
3377 "attached_conditions_summary": [
3378 {
3379 "table": "`t1`",
3380 "attached": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3381 }
3382 ] /* attached_conditions_summary */
3383 } /* attaching_conditions_to_tables */
3384 },
3385 {
3386 "finalizing_table_conditions": [
3387 {
3388 "table": "`t1`",
3389 "original_table_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
3390 "final_table_condition ": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
3391 }
3392 ] /* finalizing_table_conditions */
3393 },
3394 {
3395 "refine_plan": [
3396 {
3397 "table": "`t1`"
3398 }
3399 ] /* refine_plan */
3400 }
3401 ] /* steps */
3402 } /* join_optimization */
3403 },
3404 {
3405 "join_explain": {
3406 "select#": 1,
3407 "steps": [
3408 ] /* steps */
3409 } /* join_explain */
3410 }
3411 ] /* steps */
3412 } 0 0
3413
3414 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
3415 WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
3416 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3417 1 SIMPLE t1 NULL range i1,i2 i2 4 NULL 42 100.00 Using index condition
3418 1 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.key1 10 100.00 NULL
3419 Warnings:
3420 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8`,`test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))
3421
3422 SELECT * FROM information_schema.OPTIMIZER_TRACE;
3423 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
3424 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
3425 WHERE t1.key1=t2.key1a AND t1.key2 > 1020 {
3426 "steps": [
3427 {
3428 "join_preparation": {
3429 "select#": 1,
3430 "steps": [
3431 {
3432 "expanded_query": "/* select#1 */ select straight_join `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8`,`t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t1` join `t2` where ((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))"
3433 }
3434 ] /* steps */
3435 } /* join_preparation */
3436 },
3437 {
3438 "join_optimization": {
3439 "select#": 1,
3440 "steps": [
3441 {
3442 "condition_processing": {
3443 "condition": "WHERE",
3444 "original_condition": "((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))",
3445 "steps": [
3446 {
3447 "transformation": "equality_propagation",
3448 "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
3449 },
3450 {
3451 "transformation": "constant_propagation",
3452 "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
3453 },
3454 {
3455 "transformation": "trivial_condition_removal",
3456 "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
3457 }
3458 ] /* steps */
3459 } /* condition_processing */
3460 },
3461 {
3462 "substitute_generated_columns": {
3463 } /* substitute_generated_columns */
3464 },
3465 {
3466 "table_dependencies": [
3467 {
3468 "table": "`t1`",
3469 "row_may_be_null": false,
3470 "map_bit": 0,
3471 "depends_on_map_bits": [
3472 ] /* depends_on_map_bits */
3473 },
3474 {
3475 "table": "`t2`",
3476 "row_may_be_null": false,
3477 "map_bit": 1,
3478 "depends_on_map_bits": [
3479 0
3480 ] /* depends_on_map_bits */
3481 }
3482 ] /* table_dependencies */
3483 },
3484 {
3485 "ref_optimizer_key_uses": [
3486 {
3487 "table": "`t1`",
3488 "field": "key1",
3489 "equals": "`t2`.`key1a`",
3490 "null_rejecting": true
3491 },
3492 {
3493 "table": "`t2`",
3494 "field": "key1a",
3495 "equals": "`t1`.`key1`",
3496 "null_rejecting": true
3497 }
3498 ] /* ref_optimizer_key_uses */
3499 },
3500 {
3501 "rows_estimation": [
3502 {
3503 "table": "`t1`",
3504 "range_analysis": {
3505 "table_scan": {
3506 "rows": 1024,
3507 "cost": 107.06
3508 } /* table_scan */,
3509 "potential_range_indexes": [
3510 {
3511 "index": "i1",
3512 "usable": false,
3513 "cause": "not_applicable"
3514 },
3515 {
3516 "index": "i2",
3517 "usable": true,
3518 "key_parts": [
3519 "key2"
3520 ] /* key_parts */
3521 },
3522 {
3523 "index": "i3",
3524 "usable": false,
3525 "cause": "not_applicable"
3526 },
3527 {
3528 "index": "i4",
3529 "usable": false,
3530 "cause": "not_applicable"
3531 },
3532 {
3533 "index": "i5",
3534 "usable": false,
3535 "cause": "not_applicable"
3536 },
3537 {
3538 "index": "i6",
3539 "usable": false,
3540 "cause": "not_applicable"
3541 },
3542 {
3543 "index": "i7",
3544 "usable": false,
3545 "cause": "not_applicable"
3546 },
3547 {
3548 "index": "i8",
3549 "usable": false,
3550 "cause": "not_applicable"
3551 }
3552 ] /* potential_range_indexes */,
3553 "setup_range_conditions": [
3554 ] /* setup_range_conditions */,
3555 "group_index_range": {
3556 "chosen": false,
3557 "cause": "not_single_table"
3558 } /* group_index_range */,
3559 "skip_scan_range": {
3560 "chosen": false,
3561 "cause": "not_single_table"
3562 } /* skip_scan_range */,
3563 "analyzing_range_alternatives": {
3564 "range_scan_alternatives": [
3565 {
3566 "index": "i2",
3567 "ranges": [
3568 "1020 < key2"
3569 ] /* ranges */,
3570 "index_dives_for_eq_ranges": true,
3571 "rowid_ordered": false,
3572 "using_mrr": false,
3573 "index_only": false,
3574 "in_memory": -1,
3575 "rows": 42,
3576 "cost": 14.96,
3577 "chosen": true
3578 }
3579 ] /* range_scan_alternatives */,
3580 "analyzing_roworder_intersect": {
3581 "usable": false,
3582 "cause": "too_few_roworder_scans"
3583 } /* analyzing_roworder_intersect */
3584 } /* analyzing_range_alternatives */,
3585 "chosen_range_access_summary": {
3586 "range_access_plan": {
3587 "type": "range_scan",
3588 "index": "i2",
3589 "rows": 42,
3590 "ranges": [
3591 "1020 < key2"
3592 ] /* ranges */
3593 } /* range_access_plan */,
3594 "rows_for_plan": 42,
3595 "cost_for_plan": 14.96,
3596 "chosen": true
3597 } /* chosen_range_access_summary */
3598 } /* range_analysis */
3599 },
3600 {
3601 "table": "`t2`",
3602 "table_scan": {
3603 "rows": 1024,
3604 "cost": 2.06
3605 } /* table_scan */
3606 }
3607 ] /* rows_estimation */
3608 },
3609 {
3610 "considered_execution_plans": [
3611 {
3612 "plan_prefix": [
3613 ] /* plan_prefix */,
3614 "table": "`t1`",
3615 "best_access_path": {
3616 "considered_access_paths": [
3617 {
3618 "access_type": "ref",
3619 "index": "i1",
3620 "usable": false,
3621 "chosen": false
3622 },
3623 {
3624 "rows_to_scan": 42,
3625 "filtering_effect": [
3626 ] /* filtering_effect */,
3627 "final_filtering_effect": 1,
3628 "access_type": "range",
3629 "range_details": {
3630 "used_index": "i2"
3631 } /* range_details */,
3632 "resulting_rows": 42,
3633 "cost": 19.16,
3634 "chosen": true
3635 }
3636 ] /* considered_access_paths */
3637 } /* best_access_path */,
3638 "condition_filtering_pct": 100,
3639 "rows_for_plan": 42,
3640 "cost_for_plan": 19.16
3641 },
3642 {
3643 "plan_prefix": [
3644 "`t1`"
3645 ] /* plan_prefix */,
3646 "table": "`t2`",
3647 "best_access_path": {
3648 "considered_access_paths": [
3649 {
3650 "access_type": "ref",
3651 "index": "PRIMARY",
3652 "rows": 10,
3653 "cost": 147,
3654 "chosen": true
3655 },
3656 {
3657 "rows_to_scan": 1024,
3658 "filtering_effect": [
3659 ] /* filtering_effect */,
3660 "final_filtering_effect": 1,
3661 "access_type": "scan",
3662 "using_join_cache": true,
3663 "buffers_needed": 1,
3664 "resulting_rows": 1024,
3665 "cost": 4302.87,
3666 "chosen": false
3667 }
3668 ] /* considered_access_paths */
3669 } /* best_access_path */,
3670 "condition_filtering_pct": 100,
3671 "rows_for_plan": 420,
3672 "cost_for_plan": 166.16
3673 }
3674 ] /* considered_execution_plans */
3675 },
3676 {
3677 "attaching_conditions_to_tables": {
3678 "original_condition": "((`t2`.`key1a` = `t1`.`key1`) and (`t1`.`key2` > 1020))",
3679 "attached_conditions_computation": [
3680 ] /* attached_conditions_computation */,
3681 "attached_conditions_summary": [
3682 {
3683 "table": "`t1`",
3684 "attached": "(`t1`.`key2` > 1020)"
3685 },
3686 {
3687 "table": "`t2`",
3688 "attached": "(`t2`.`key1a` = `t1`.`key1`)"
3689 }
3690 ] /* attached_conditions_summary */
3691 } /* attaching_conditions_to_tables */
3692 },
3693 {
3694 "finalizing_table_conditions": [
3695 {
3696 "table": "`t1`",
3697 "original_table_condition": "(`t1`.`key2` > 1020)",
3698 "final_table_condition ": "(`t1`.`key2` > 1020)"
3699 },
3700 {
3701 "table": "`t2`",
3702 "original_table_condition": "(`t2`.`key1a` = `t1`.`key1`)",
3703 "final_table_condition ": null
3704 }
3705 ] /* finalizing_table_conditions */
3706 },
3707 {
3708 "refine_plan": [
3709 {
3710 "table": "`t1`",
3711 "pushed_index_condition": "(`t1`.`key2` > 1020)",
3712 "table_condition_attached": null
3713 },
3714 {
3715 "table": "`t2`"
3716 }
3717 ] /* refine_plan */
3718 }
3719 ] /* steps */
3720 } /* join_optimization */
3721 },
3722 {
3723 "join_explain": {
3724 "select#": 1,
3725 "steps": [
3726 ] /* steps */
3727 } /* join_explain */
3728 }
3729 ] /* steps */
3730 } 0 0
3731 DROP TABLE t1,t2;
3732 CREATE TABLE t1 (
3733 cola char(3) not null,
3734 colb char(3) not null,
3735 filler char(200),
3736 key(cola),
3737 key(colb)
3738 ) CHARSET utf8mb4;
3739 INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
3740 Inserting records
3741
3742 EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
3743 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3744 1 SIMPLE t1 NULL index_merge cola,colb cola,colb 12,12 NULL 29 99.37 Using intersect(cola,colb); Using where
3745 Warnings:
3746 Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
3747
3748 SELECT * FROM information_schema.OPTIMIZER_TRACE;
3749 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
3750 EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
3751 "steps": [
3752 {
3753 "join_preparation": {
3754 "select#": 1,
3755 "steps": [
3756 {
3757 "expanded_query": "/* select#1 */ select `t1`.`cola` AS `cola`,`t1`.`colb` AS `colb`,`t1`.`filler` AS `filler` from `t1` where ((`t1`.`cola` = 'foo') and (`t1`.`colb` = 'bar'))"
3758 }
3759 ] /* steps */
3760 } /* join_preparation */
3761 },
3762 {
3763 "join_optimization": {
3764 "select#": 1,
3765 "steps": [
3766 {
3767 "condition_processing": {
3768 "condition": "WHERE",
3769 "original_condition": "((`t1`.`cola` = 'foo') and (`t1`.`colb` = 'bar'))",
3770 "steps": [
3771 {
3772 "transformation": "equality_propagation",
3773 "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
3774 },
3775 {
3776 "transformation": "constant_propagation",
3777 "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
3778 },
3779 {
3780 "transformation": "trivial_condition_removal",
3781 "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
3782 }
3783 ] /* steps */
3784 } /* condition_processing */
3785 },
3786 {
3787 "substitute_generated_columns": {
3788 } /* substitute_generated_columns */
3789 },
3790 {
3791 "table_dependencies": [
3792 {
3793 "table": "`t1`",
3794 "row_may_be_null": false,
3795 "map_bit": 0,
3796 "depends_on_map_bits": [
3797 ] /* depends_on_map_bits */
3798 }
3799 ] /* table_dependencies */
3800 },
3801 {
3802 "ref_optimizer_key_uses": [
3803 {
3804 "table": "`t1`",
3805 "field": "cola",
3806 "equals": "'foo'",
3807 "null_rejecting": true
3808 },
3809 {
3810 "table": "`t1`",
3811 "field": "colb",
3812 "equals": "'bar'",
3813 "null_rejecting": true
3814 }
3815 ] /* ref_optimizer_key_uses */
3816 },
3817 {
3818 "rows_estimation": [
3819 {
3820 "table": "`t1`",
3821 "range_analysis": {
3822 "table_scan": {
3823 "rows": 8704,
3824 "cost": 1311.28
3825 } /* table_scan */,
3826 "potential_range_indexes": [
3827 {
3828 "index": "cola",
3829 "usable": true,
3830 "key_parts": [
3831 "cola"
3832 ] /* key_parts */
3833 },
3834 {
3835 "index": "colb",
3836 "usable": true,
3837 "key_parts": [
3838 "colb"
3839 ] /* key_parts */
3840 }
3841 ] /* potential_range_indexes */,
3842 "setup_range_conditions": [
3843 ] /* setup_range_conditions */,
3844 "group_index_range": {
3845 "chosen": false,
3846 "cause": "not_group_by_or_distinct"
3847 } /* group_index_range */,
3848 "skip_scan_range": {
3849 "potential_skip_scan_indexes": [
3850 {
3851 "index": "cola",
3852 "usable": false,
3853 "cause": "query_references_nonkey_column"
3854 },
3855 {
3856 "index": "colb",
3857 "usable": false,
3858 "cause": "query_references_nonkey_column"
3859 }
3860 ] /* potential_skip_scan_indexes */
3861 } /* skip_scan_range */,
3862 "analyzing_range_alternatives": {
3863 "range_scan_alternatives": [
3864 {
3865 "index": "cola",
3866 "ranges": [
3867 "cola = 'foo'"
3868 ] /* ranges */,
3869 "index_dives_for_eq_ranges": true,
3870 "rowid_ordered": true,
3871 "using_mrr": false,
3872 "index_only": false,
3873 "in_memory": -1,
3874 "rows": 504,
3875 "cost": 176.66,
3876 "chosen": true
3877 },
3878 {
3879 "index": "colb",
3880 "ranges": [
3881 "colb = 'bar'"
3882 ] /* ranges */,
3883 "index_dives_for_eq_ranges": true,
3884 "rowid_ordered": true,
3885 "using_mrr": false,
3886 "index_only": false,
3887 "in_memory": -1,
3888 "rows": 504,
3889 "cost": 176.66,
3890 "chosen": false,
3891 "cause": "cost"
3892 }
3893 ] /* range_scan_alternatives */,
3894 "analyzing_roworder_intersect": {
3895 "intersecting_indexes": [
3896 {
3897 "index": "cola",
3898 "index_scan_cost": 4.90741,
3899 "cumulated_index_scan_cost": 4.90741,
3900 "disk_sweep_cost": 109.54,
3901 "cumulated_total_cost": 114.448,
3902 "usable": true,
3903 "matching_rows_now": 504,
3904 "isect_covering_with_this_index": false,
3905 "chosen": true
3906 },
3907 {
3908 "index": "colb",
3909 "index_scan_cost": 4.90741,
3910 "cumulated_index_scan_cost": 9.81481,
3911 "disk_sweep_cost": 7.19243,
3912 "cumulated_total_cost": 17.0072,
3913 "usable": true,
3914 "matching_rows_now": 29.1838,
3915 "isect_covering_with_this_index": false,
3916 "chosen": true
3917 }
3918 ] /* intersecting_indexes */,
3919 "clustered_pk": {
3920 "clustered_pk_added_to_intersect": false,
3921 "cause": "no_clustered_pk_index"
3922 } /* clustered_pk */,
3923 "rows": 29.1838,
3924 "cost": 17.0072,
3925 "covering": false,
3926 "chosen": true
3927 } /* analyzing_roworder_intersect */
3928 } /* analyzing_range_alternatives */,
3929 "chosen_range_access_summary": {
3930 "range_access_plan": {
3931 "type": "index_roworder_intersect",
3932 "rows": 29.1838,
3933 "cost": 17.0072,
3934 "covering": false,
3935 "clustered_pk_scan": false,
3936 "intersect_of": [
3937 {
3938 "type": "range_scan",
3939 "index": "cola",
3940 "rows": 504,
3941 "ranges": [
3942 "cola = 'foo'"
3943 ] /* ranges */
3944 },
3945 {
3946 "type": "range_scan",
3947 "index": "colb",
3948 "rows": 504,
3949 "ranges": [
3950 "colb = 'bar'"
3951 ] /* ranges */
3952 }
3953 ] /* intersect_of */
3954 } /* range_access_plan */,
3955 "rows_for_plan": 29.1838,
3956 "cost_for_plan": 17.0072,
3957 "chosen": true
3958 } /* chosen_range_access_summary */
3959 } /* range_analysis */
3960 }
3961 ] /* rows_estimation */
3962 },
3963 {
3964 "considered_execution_plans": [
3965 {
3966 "plan_prefix": [
3967 ] /* plan_prefix */,
3968 "table": "`t1`",
3969 "best_access_path": {
3970 "considered_access_paths": [
3971 {
3972 "access_type": "ref",
3973 "index": "cola",
3974 "rows": 504,
3975 "cost": 176.4,
3976 "chosen": true
3977 },
3978 {
3979 "access_type": "ref",
3980 "index": "colb",
3981 "rows": 504,
3982 "cost": 176.4,
3983 "chosen": false
3984 },
3985 {
3986 "rows_to_scan": 29,
3987 "filtering_effect": [
3988 ] /* filtering_effect */,
3989 "final_filtering_effect": 1,
3990 "access_type": "range",
3991 "range_details": {
3992 "used_index": "intersect(cola,colb)"
3993 } /* range_details */,
3994 "resulting_rows": 29,
3995 "cost": 19.9072,
3996 "chosen": true
3997 }
3998 ] /* considered_access_paths */
3999 } /* best_access_path */,
4000 "condition_filtering_pct": 100,
4001 "rows_for_plan": 29,
4002 "cost_for_plan": 19.9072,
4003 "chosen": true
4004 }
4005 ] /* considered_execution_plans */
4006 },
4007 {
4008 "attaching_conditions_to_tables": {
4009 "original_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))",
4010 "attached_conditions_computation": [
4011 ] /* attached_conditions_computation */,
4012 "attached_conditions_summary": [
4013 {
4014 "table": "`t1`",
4015 "attached": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))"
4016 }
4017 ] /* attached_conditions_summary */
4018 } /* attaching_conditions_to_tables */
4019 },
4020 {
4021 "finalizing_table_conditions": [
4022 {
4023 "table": "`t1`",
4024 "original_table_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))",
4025 "final_table_condition ": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))"
4026 }
4027 ] /* finalizing_table_conditions */
4028 },
4029 {
4030 "refine_plan": [
4031 {
4032 "table": "`t1`"
4033 }
4034 ] /* refine_plan */
4035 }
4036 ] /* steps */
4037 } /* join_optimization */
4038 },
4039 {
4040 "join_explain": {
4041 "select#": 1,
4042 "steps": [
4043 ] /* steps */
4044 } /* join_explain */
4045 }
4046 ] /* steps */
4047 } 0 0
4048
4049 EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
4050 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4051 1 SIMPLE t1 NULL ref cola cola 12 const 1 100.00 Using index condition
4052 Warnings:
4053 Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`cola` = 'f\no')
4054
4055 SELECT * FROM information_schema.OPTIMIZER_TRACE;
4056 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
4057 EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no' {
4058 "steps": [
4059 {
4060 "join_preparation": {
4061 "select#": 1,
4062 "steps": [
4063 {
4064 "expanded_query": "/* select#1 */ select `t1`.`cola` AS `cola`,`t1`.`colb` AS `colb`,`t1`.`filler` AS `filler` from `t1` where (`t1`.`cola` = 'f\\no')"
4065 }
4066 ] /* steps */
4067 } /* join_preparation */
4068 },
4069 {
4070 "join_optimization": {
4071 "select#": 1,
4072 "steps": [
4073 {
4074 "condition_processing": {
4075 "condition": "WHERE",
4076 "original_condition": "(`t1`.`cola` = 'f\\no')",
4077 "steps": [
4078 {
4079 "transformation": "equality_propagation",
4080 "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
4081 },
4082 {
4083 "transformation": "constant_propagation",
4084 "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
4085 },
4086 {
4087 "transformation": "trivial_condition_removal",
4088 "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
4089 }
4090 ] /* steps */
4091 } /* condition_processing */
4092 },
4093 {
4094 "substitute_generated_columns": {
4095 } /* substitute_generated_columns */
4096 },
4097 {
4098 "table_dependencies": [
4099 {
4100 "table": "`t1`",
4101 "row_may_be_null": false,
4102 "map_bit": 0,
4103 "depends_on_map_bits": [
4104 ] /* depends_on_map_bits */
4105 }
4106 ] /* table_dependencies */
4107 },
4108 {
4109 "ref_optimizer_key_uses": [
4110 {
4111 "table": "`t1`",
4112 "field": "cola",
4113 "equals": "'f\\no'",
4114 "null_rejecting": true
4115 }
4116 ] /* ref_optimizer_key_uses */
4117 },
4118 {
4119 "rows_estimation": [
4120 {
4121 "table": "`t1`",
4122 "range_analysis": {
4123 "table_scan": {
4124 "rows": 8704,
4125 "cost": 1311.28
4126 } /* table_scan */,
4127 "potential_range_indexes": [
4128 {
4129 "index": "cola",
4130 "usable": true,
4131 "key_parts": [
4132 "cola"
4133 ] /* key_parts */
4134 },
4135 {
4136 "index": "colb",
4137 "usable": false,
4138 "cause": "not_applicable"
4139 }
4140 ] /* potential_range_indexes */,
4141 "setup_range_conditions": [
4142 ] /* setup_range_conditions */,
4143 "group_index_range": {
4144 "chosen": false,
4145 "cause": "not_group_by_or_distinct"
4146 } /* group_index_range */,
4147 "skip_scan_range": {
4148 "potential_skip_scan_indexes": [
4149 {
4150 "index": "cola",
4151 "usable": false,
4152 "cause": "query_references_nonkey_column"
4153 }
4154 ] /* potential_skip_scan_indexes */
4155 } /* skip_scan_range */,
4156 "analyzing_range_alternatives": {
4157 "range_scan_alternatives": [
4158 {
4159 "index": "cola",
4160 "ranges": [
4161 "cola = 'f\\no'"
4162 ] /* ranges */,
4163 "index_dives_for_eq_ranges": true,
4164 "rowid_ordered": true,
4165 "using_mrr": false,
4166 "index_only": false,
4167 "in_memory": -1,
4168 "rows": 1,
4169 "cost": 0.61,
4170 "chosen": true
4171 }
4172 ] /* range_scan_alternatives */,
4173 "analyzing_roworder_intersect": {
4174 "usable": false,
4175 "cause": "too_few_roworder_scans"
4176 } /* analyzing_roworder_intersect */
4177 } /* analyzing_range_alternatives */,
4178 "chosen_range_access_summary": {
4179 "range_access_plan": {
4180 "type": "range_scan",
4181 "index": "cola",
4182 "rows": 1,
4183 "ranges": [
4184 "cola = 'f\\no'"
4185 ] /* ranges */
4186 } /* range_access_plan */,
4187 "rows_for_plan": 1,
4188 "cost_for_plan": 0.61,
4189 "chosen": true
4190 } /* chosen_range_access_summary */
4191 } /* range_analysis */
4192 }
4193 ] /* rows_estimation */
4194 },
4195 {
4196 "considered_execution_plans": [
4197 {
4198 "plan_prefix": [
4199 ] /* plan_prefix */,
4200 "table": "`t1`",
4201 "best_access_path": {
4202 "considered_access_paths": [
4203 {
4204 "access_type": "ref",
4205 "index": "cola",
4206 "rows": 1,
4207 "cost": 0.35,
4208 "chosen": true
4209 },
4210 {
4211 "access_type": "range",
4212 "range_details": {
4213 "used_index": "cola"
4214 } /* range_details */,
4215 "chosen": false,
4216 "cause": "heuristic_index_cheaper"
4217 }
4218 ] /* considered_access_paths */
4219 } /* best_access_path */,
4220 "condition_filtering_pct": 100,
4221 "rows_for_plan": 1,
4222 "cost_for_plan": 0.35,
4223 "chosen": true
4224 }
4225 ] /* considered_execution_plans */
4226 },
4227 {
4228 "attaching_conditions_to_tables": {
4229 "original_condition": "(`t1`.`cola` = 'f\\no')",
4230 "attached_conditions_computation": [
4231 ] /* attached_conditions_computation */,
4232 "attached_conditions_summary": [
4233 {
4234 "table": "`t1`",
4235 "attached": "(`t1`.`cola` = 'f\\no')"
4236 }
4237 ] /* attached_conditions_summary */
4238 } /* attaching_conditions_to_tables */
4239 },
4240 {
4241 "finalizing_table_conditions": [
4242 {
4243 "table": "`t1`",
4244 "original_table_condition": "(`t1`.`cola` = 'f\\no')",
4245 "final_table_condition ": "(`t1`.`cola` = 'f\\no')"
4246 }
4247 ] /* finalizing_table_conditions */
4248 },
4249 {
4250 "refine_plan": [
4251 {
4252 "table": "`t1`",
4253 "pushed_index_condition": "(`t1`.`cola` = 'f\\no')",
4254 "table_condition_attached": null
4255 }
4256 ] /* refine_plan */
4257 }
4258 ] /* steps */
4259 } /* join_optimization */
4260 },
4261 {
4262 "join_explain": {
4263 "select#": 1,
4264 "steps": [
4265 ] /* steps */
4266 } /* join_explain */
4267 }
4268 ] /* steps */
4269 } 0 0
4270 DROP TABLE t1;
4271 CREATE TABLE t1(c INT);
4272 INSERT INTO t1 VALUES (),();
4273 CREATE TABLE t2 (b INT, KEY(b));
4274 INSERT INTO t2 VALUES (),(),();
4275 SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
4276 EXPLAIN SELECT 1 FROM
4277 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
4278 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4279 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
4280 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
4281 2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1)
4282 Warnings:
4283 Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2`
4284
4285 SELECT * FROM information_schema.OPTIMIZER_TRACE;
4286 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
4287 EXPLAIN SELECT 1 FROM
4288 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
4289 "steps": [
4290 {
4291 "join_preparation": {
4292 "select#": 1,
4293 "steps": [
4294 {
4295 "join_preparation": {
4296 "select#": 2,
4297 "steps": [
4298 {
4299 "expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1"
4300 }
4301 ] /* steps */
4302 } /* join_preparation */
4303 },
4304 {
4305 "derived": {
4306 "table": " `d2`",
4307 "select#": 2,
4308 "materialized": true
4309 } /* derived */
4310 },
4311 {
4312 "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1) `d2`"
4313 }
4314 ] /* steps */
4315 } /* join_preparation */
4316 },
4317 {
4318 "join_optimization": {
4319 "select#": 1,
4320 "steps": [
4321 {
4322 "join_optimization": {
4323 "select#": 2,
4324 "steps": [
4325 {
4326 "condition_processing": {
4327 "condition": "WHERE",
4328 "original_condition": "(`t2`.`b` < `t1`.`c`)",
4329 "steps": [
4330 {
4331 "transformation": "equality_propagation",
4332 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4333 },
4334 {
4335 "transformation": "constant_propagation",
4336 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4337 },
4338 {
4339 "transformation": "trivial_condition_removal",
4340 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4341 }
4342 ] /* steps */
4343 } /* condition_processing */
4344 },
4345 {
4346 "substitute_generated_columns": {
4347 } /* substitute_generated_columns */
4348 },
4349 {
4350 "table_dependencies": [
4351 {
4352 "table": "`t2`",
4353 "row_may_be_null": false,
4354 "map_bit": 0,
4355 "depends_on_map_bits": [
4356 ] /* depends_on_map_bits */
4357 },
4358 {
4359 "table": "`t1`",
4360 "row_may_be_null": false,
4361 "map_bit": 1,
4362 "depends_on_map_bits": [
4363 ] /* depends_on_map_bits */
4364 }
4365 ] /* table_dependencies */
4366 },
4367 {
4368 "ref_optimizer_key_uses": [
4369 ] /* ref_optimizer_key_uses */
4370 },
4371 {
4372 "rows_estimation": [
4373 {
4374 "table": "`t2`",
4375 "table_scan": {
4376 "rows": 3,
4377 "cost": 0.501282
4378 } /* table_scan */
4379 },
4380 {
4381 "table": "`t1`",
4382 "table_scan": {
4383 "rows": 2,
4384 "cost": 0.500854
4385 } /* table_scan */
4386 }
4387 ] /* rows_estimation */
4388 },
4389 {
4390 "considered_execution_plans": "..."
4391 },
4392 {
4393 "attaching_conditions_to_tables": {
4394 "original_condition": "(`t2`.`b` < `t1`.`c`)",
4395 "attached_conditions_computation": [
4396 {
4397 "table": "`t2`",
4398 "rechecking_index_usage": {
4399 "recheck_reason": "not_first_table",
4400 "range_analysis": {
4401 "table_scan": {
4402 "rows": 3,
4403 "cost": 3.05
4404 } /* table_scan */,
4405 "potential_range_indexes": [
4406 {
4407 "index": "b",
4408 "usable": true,
4409 "key_parts": [
4410 "b"
4411 ] /* key_parts */
4412 }
4413 ] /* potential_range_indexes */,
4414 "best_covering_index_scan": {
4415 "index": "b",
4416 "cost": 0.561628,
4417 "chosen": true
4418 } /* best_covering_index_scan */,
4419 "setup_range_conditions": [
4420 ] /* setup_range_conditions */,
4421 "group_index_range": {
4422 "chosen": false,
4423 "cause": "not_single_table"
4424 } /* group_index_range */,
4425 "skip_scan_range": {
4426 "chosen": false,
4427 "cause": "not_single_table"
4428 } /* skip_scan_range */,
4429 "analyzing_range_alternatives": {
4430 "range_scan_alternatives": [
4431 {
4432 "index": "b",
4433 "chosen": false,
4434 "cause": "depends_on_unread_values"
4435 }
4436 ] /* range_scan_alternatives */,
4437 "analyzing_roworder_intersect": {
4438 "usable": false,
4439 "cause": "too_few_roworder_scans"
4440 } /* analyzing_roworder_intersect */
4441 } /* analyzing_range_alternatives */
4442 } /* range_analysis */
4443 } /* rechecking_index_usage */
4444 }
4445 ] /* attached_conditions_computation */,
4446 "attached_conditions_summary": [
4447 {
4448 "table": "`t1`",
4449 "attached": null
4450 },
4451 {
4452 "table": "`t2`",
4453 "attached": "(`t2`.`b` < `t1`.`c`)"
4454 }
4455 ] /* attached_conditions_summary */
4456 } /* attaching_conditions_to_tables */
4457 },
4458 {
4459 "optimizing_distinct_group_by_order_by": {
4460 "simplifying_group_by": {
4461 "original_clause": "''",
4462 "items": [
4463 {
4464 "item": "1",
4465 "uses_only_constant_tables": true
4466 }
4467 ] /* items */,
4468 "resulting_clause_is_simple": true,
4469 "resulting_clause": ""
4470 } /* simplifying_group_by */
4471 } /* optimizing_distinct_group_by_order_by */
4472 },
4473 {
4474 "finalizing_table_conditions": [
4475 {
4476 "table": "`t2`",
4477 "original_table_condition": "(`t2`.`b` < `t1`.`c`)",
4478 "final_table_condition ": "(`t2`.`b` < `t1`.`c`)"
4479 }
4480 ] /* finalizing_table_conditions */
4481 },
4482 {
4483 "refine_plan": [
4484 {
4485 "table": "`t1`"
4486 },
4487 {
4488 "table": "`t2`"
4489 }
4490 ] /* refine_plan */
4491 }
4492 ] /* steps */
4493 } /* join_optimization */
4494 },
4495 {
4496 "creating_tmp_table": {
4497 "tmp_table_info": {
4498 "table": "d2",
4499 "columns": 1,
4500 "row_length": 5,
4501 "key_length": 0,
4502 "unique_constraint": false,
4503 "makes_grouped_rows": false,
4504 "cannot_insert_duplicates": false,
4505 "location": "TempTable"
4506 } /* tmp_table_info */
4507 } /* creating_tmp_table */
4508 },
4509 {
4510 "join_execution": {
4511 "select#": 2,
4512 "steps": [
4513 {
4514 "rows_estimation_per_outer_row": {
4515 "table": "`t2`",
4516 "range_analysis": {
4517 "table_scan": {
4518 "rows": 3,
4519 "cost": 2.90128
4520 } /* table_scan */,
4521 "potential_range_indexes": [
4522 {
4523 "index": "b",
4524 "usable": true,
4525 "key_parts": [
4526 "b"
4527 ] /* key_parts */
4528 }
4529 ] /* potential_range_indexes */,
4530 "best_covering_index_scan": {
4531 "index": "b",
4532 "cost": 0.561628,
4533 "chosen": true
4534 } /* best_covering_index_scan */,
4535 "setup_range_conditions": [
4536 {
4537 "impossible_condition": {
4538 "cause": "comparison_with_null_always_false"
4539 } /* impossible_condition */
4540 }
4541 ] /* setup_range_conditions */,
4542 "impossible_range": true
4543 } /* range_analysis */
4544 } /* rows_estimation_per_outer_row */
4545 },
4546 {
4547 "rows_estimation_per_outer_row": {
4548 "table": "`t2`",
4549 "range_analysis": {
4550 "table_scan": {
4551 "rows": 3,
4552 "cost": 2.90128
4553 } /* table_scan */,
4554 "potential_range_indexes": [
4555 {
4556 "index": "b",
4557 "usable": true,
4558 "key_parts": [
4559 "b"
4560 ] /* key_parts */
4561 }
4562 ] /* potential_range_indexes */,
4563 "best_covering_index_scan": {
4564 "index": "b",
4565 "cost": 0.561628,
4566 "chosen": true
4567 } /* best_covering_index_scan */,
4568 "setup_range_conditions": [
4569 {
4570 "impossible_condition": {
4571 "cause": "comparison_with_null_always_false"
4572 } /* impossible_condition */
4573 }
4574 ] /* setup_range_conditions */,
4575 "impossible_range": true
4576 } /* range_analysis */
4577 } /* rows_estimation_per_outer_row */
4578 }
4579 ] /* steps */
4580 } /* join_execution */
4581 },
4582 {
4583 "table_dependencies": [
4584 {
4585 "table": " `d2`",
4586 "row_may_be_null": false,
4587 "map_bit": 0,
4588 "depends_on_map_bits": [
4589 ] /* depends_on_map_bits */
4590 }
4591 ] /* table_dependencies */
4592 },
4593 {
4594 "rows_estimation": [
4595 {
4596 "table": " `d2`",
4597 "rows": 1,
4598 "cost": 1,
4599 "table_type": "system",
4600 "empty": true
4601 }
4602 ] /* rows_estimation */
4603 }
4604 ] /* steps */,
4605 "empty_result": {
4606 "cause": "no matching row in const table"
4607 } /* empty_result */
4608 } /* join_optimization */
4609 },
4610 {
4611 "join_explain": {
4612 "select#": 1,
4613 "steps": [
4614 {
4615 "join_explain": {
4616 "select#": 2,
4617 "steps": [
4618 ] /* steps */
4619 } /* join_explain */
4620 }
4621 ] /* steps */
4622 } /* join_explain */
4623 }
4624 ] /* steps */
4625 } 0 0
4626
4627 SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
4628 EXPLAIN SELECT 1 FROM
4629 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
4630 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4631 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
4632 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
4633 2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1)
4634 Warnings:
4635 Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2`
4636
4637 SELECT * FROM information_schema.OPTIMIZER_TRACE;
4638 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
4639 EXPLAIN SELECT 1 FROM
4640 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
4641 "steps": [
4642 {
4643 "join_preparation": {
4644 "select#": 1,
4645 "steps": [
4646 {
4647 "join_preparation": {
4648 "select#": 2,
4649 "steps": [
4650 {
4651 "expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1"
4652 }
4653 ] /* steps */
4654 } /* join_preparation */
4655 },
4656 {
4657 "derived": {
4658 "table": " `d2`",
4659 "select#": 2,
4660 "materialized": true
4661 } /* derived */
4662 },
4663 {
4664 "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1) `d2`"
4665 }
4666 ] /* steps */
4667 } /* join_preparation */
4668 },
4669 {
4670 "join_optimization": {
4671 "select#": 1,
4672 "steps": [
4673 {
4674 "join_optimization": {
4675 "select#": 2,
4676 "steps": [
4677 {
4678 "condition_processing": {
4679 "condition": "WHERE",
4680 "original_condition": "(`t2`.`b` < `t1`.`c`)",
4681 "steps": [
4682 {
4683 "transformation": "equality_propagation",
4684 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4685 },
4686 {
4687 "transformation": "constant_propagation",
4688 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4689 },
4690 {
4691 "transformation": "trivial_condition_removal",
4692 "resulting_condition": "(`t2`.`b` < `t1`.`c`)"
4693 }
4694 ] /* steps */
4695 } /* condition_processing */
4696 },
4697 {
4698 "substitute_generated_columns": {
4699 } /* substitute_generated_columns */
4700 },
4701 {
4702 "table_dependencies": [
4703 {
4704 "table": "`t2`",
4705 "row_may_be_null": false,
4706 "map_bit": 0,
4707 "depends_on_map_bits": [
4708 ] /* depends_on_map_bits */
4709 },
4710 {
4711 "table": "`t1`",
4712 "row_may_be_null": false,
4713 "map_bit": 1,
4714 "depends_on_map_bits": [
4715 ] /* depends_on_map_bits */
4716 }
4717 ] /* table_dependencies */
4718 },
4719 {
4720 "ref_optimizer_key_uses": [
4721 ] /* ref_optimizer_key_uses */
4722 },
4723 {
4724 "rows_estimation": [
4725 {
4726 "table": "`t2`",
4727 "table_scan": {
4728 "rows": 3,
4729 "cost": 0.501282
4730 } /* table_scan */
4731 },
4732 {
4733 "table": "`t1`",
4734 "table_scan": {
4735 "rows": 2,
4736 "cost": 0.500854
4737 } /* table_scan */
4738 }
4739 ] /* rows_estimation */
4740 },
4741 {
4742 "considered_execution_plans": "..."
4743 },
4744 {
4745 "attaching_conditions_to_tables": {
4746 "original_condition": "(`t2`.`b` < `t1`.`c`)",
4747 "attached_conditions_computation": [
4748 {
4749 "table": "`t2`",
4750 "rechecking_index_usage": {
4751 "recheck_reason": "not_first_table",
4752 "range_analysis": {
4753 "table_scan": {
4754 "rows": 3,
4755 "cost": 3.05
4756 } /* table_scan */,
4757 "potential_range_indexes": [
4758 {
4759 "index": "b",
4760 "usable": true,
4761 "key_parts": [
4762 "b"
4763 ] /* key_parts */
4764 }
4765 ] /* potential_range_indexes */,
4766 "best_covering_index_scan": {
4767 "index": "b",
4768 "cost": 0.561628,
4769 "chosen": true
4770 } /* best_covering_index_scan */,
4771 "setup_range_conditions": [
4772 ] /* setup_range_conditions */,
4773 "group_index_range": {
4774 "chosen": false,
4775 "cause": "not_single_table"
4776 } /* group_index_range */,
4777 "skip_scan_range": {
4778 "chosen": false,
4779 "cause": "not_single_table"
4780 } /* skip_scan_range */,
4781 "analyzing_range_alternatives": {
4782 "range_scan_alternatives": [
4783 {
4784 "index": "b",
4785 "chosen": false,
4786 "cause": "depends_on_unread_values"
4787 }
4788 ] /* range_scan_alternatives */,
4789 "analyzing_roworder_intersect": {
4790 "usable": false,
4791 "cause": "too_few_roworder_scans"
4792 } /* analyzing_roworder_intersect */
4793 } /* analyzing_range_alternatives */
4794 } /* range_analysis */
4795 } /* rechecking_index_usage */
4796 }
4797 ] /* attached_conditions_computation */,
4798 "attached_conditions_summary": [
4799 {
4800 "table": "`t1`",
4801 "attached": null
4802 },
4803 {
4804 "table": "`t2`",
4805 "attached": "(`t2`.`b` < `t1`.`c`)"
4806 }
4807 ] /* attached_conditions_summary */
4808 } /* attaching_conditions_to_tables */
4809 },
4810 {
4811 "optimizing_distinct_group_by_order_by": {
4812 "simplifying_group_by": {
4813 "original_clause": "''",
4814 "items": [
4815 {
4816 "item": "1",
4817 "uses_only_constant_tables": true
4818 }
4819 ] /* items */,
4820 "resulting_clause_is_simple": true,
4821 "resulting_clause": ""
4822 } /* simplifying_group_by */
4823 } /* optimizing_distinct_group_by_order_by */
4824 },
4825 {
4826 "finalizing_table_conditions": [
4827 {
4828 "table": "`t2`",
4829 "original_table_condition": "(`t2`.`b` < `t1`.`c`)",
4830 "final_table_condition ": "(`t2`.`b` < `t1`.`c`)"
4831 }
4832 ] /* finalizing_table_conditions */
4833 },
4834 {
4835 "refine_plan": [
4836 {
4837 "table": "`t1`"
4838 },
4839 {
4840 "table": "`t2`"
4841 }
4842 ] /* refine_plan */
4843 }
4844 ] /* steps */
4845 } /* join_optimization */
4846 },
4847 {
4848 "creating_tmp_table": {
4849 "tmp_table_info": {
4850 "table": "d2",
4851 "columns": 1,
4852 "row_length": 5,
4853 "key_length": 0,
4854 "unique_constraint": false,
4855 "makes_grouped_rows": false,
4856 "cannot_insert_duplicates": false,
4857 "location": "TempTable"
4858 } /* tmp_table_info */
4859 } /* creating_tmp_table */
4860 },
4861 {
4862 "join_execution": {
4863 "select#": 2,
4864 "steps": [
4865 {
4866 "rows_estimation_per_outer_row": {
4867 "table": "`t2`",
4868 "range_analysis": {
4869 "table_scan": {
4870 "rows": 3,
4871 "cost": 2.90128
4872 } /* table_scan */,
4873 "potential_range_indexes": [
4874 {
4875 "index": "b",
4876 "usable": true,
4877 "key_parts": [
4878 "b"
4879 ] /* key_parts */
4880 }
4881 ] /* potential_range_indexes */,
4882 "best_covering_index_scan": {
4883 "index": "b",
4884 "cost": 0.561628,
4885 "chosen": true
4886 } /* best_covering_index_scan */,
4887 "setup_range_conditions": [
4888 {
4889 "impossible_condition": {
4890 "cause": "comparison_with_null_always_false"
4891 } /* impossible_condition */
4892 }
4893 ] /* setup_range_conditions */,
4894 "impossible_range": true
4895 } /* range_analysis */
4896 } /* rows_estimation_per_outer_row */
4897 }
4898 ] /* steps */
4899 } /* join_execution */
4900 },
4901 {
4902 "table_dependencies": [
4903 {
4904 "table": " `d2`",
4905 "row_may_be_null": false,
4906 "map_bit": 0,
4907 "depends_on_map_bits": [
4908 ] /* depends_on_map_bits */
4909 }
4910 ] /* table_dependencies */
4911 },
4912 {
4913 "rows_estimation": [
4914 {
4915 "table": " `d2`",
4916 "rows": 1,
4917 "cost": 1,
4918 "table_type": "system",
4919 "empty": true
4920 }
4921 ] /* rows_estimation */
4922 }
4923 ] /* steps */,
4924 "empty_result": {
4925 "cause": "no matching row in const table"
4926 } /* empty_result */
4927 } /* join_optimization */
4928 },
4929 {
4930 "join_explain": {
4931 "select#": 1,
4932 "steps": [
4933 {
4934 "join_explain": {
4935 "select#": 2,
4936 "steps": [
4937 ] /* steps */
4938 } /* join_explain */
4939 }
4940 ] /* steps */
4941 } /* join_explain */
4942 }
4943 ] /* steps */
4944 } 0 0
4945
4946 DROP TABLE t1,t2;
4947 SET optimizer_trace_features=default;
4948 CREATE TABLE t1 (
4949 i1 int,
4950 i2 int,
4951 c char(1),
4952 KEY k1 (i1),
4953 KEY k2 (i1, i2)
4954 ) CHARSET utf8mb4;
4955 INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
4956 EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
4957 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4958 1 SIMPLE t1 NULL range k1,k2 k2 5 NULL 2 100.00 Using index condition
4959 Warnings:
4960 Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`i1` > 2) order by `test`.`t1`.`i1`,`test`.`t1`.`i2`
4961
4962 SELECT * FROM information_schema.OPTIMIZER_TRACE;
4963 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
4964 EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2 {
4965 "steps": [
4966 {
4967 "join_preparation": {
4968 "select#": 1,
4969 "steps": [
4970 {
4971 "expanded_query": "/* select#1 */ select `t1`.`i1` AS `i1`,`t1`.`i2` AS `i2`,`t1`.`c` AS `c` from `t1` where (`t1`.`i1` > '2') order by `t1`.`i1`,`t1`.`i2`"
4972 }
4973 ] /* steps */
4974 } /* join_preparation */
4975 },
4976 {
4977 "join_optimization": {
4978 "select#": 1,
4979 "steps": [
4980 {
4981 "condition_processing": {
4982 "condition": "WHERE",
4983 "original_condition": "(`t1`.`i1` > '2')",
4984 "steps": [
4985 {
4986 "transformation": "equality_propagation",
4987 "resulting_condition": "(`t1`.`i1` > '2')"
4988 },
4989 {
4990 "transformation": "constant_propagation",
4991 "resulting_condition": "(`t1`.`i1` > '2')"
4992 },
4993 {
4994 "transformation": "trivial_condition_removal",
4995 "resulting_condition": "(`t1`.`i1` > 2)"
4996 }
4997 ] /* steps */
4998 } /* condition_processing */
4999 },
5000 {
5001 "substitute_generated_columns": {
5002 } /* substitute_generated_columns */
5003 },
5004 {
5005 "table_dependencies": [
5006 {
5007 "table": "`t1`",
5008 "row_may_be_null": false,
5009 "map_bit": 0,
5010 "depends_on_map_bits": [
5011 ] /* depends_on_map_bits */
5012 }
5013 ] /* table_dependencies */
5014 },
5015 {
5016 "ref_optimizer_key_uses": [
5017 ] /* ref_optimizer_key_uses */
5018 },
5019 {
5020 "rows_estimation": [
5021 {
5022 "table": "`t1`",
5023 "range_analysis": {
5024 "table_scan": {
5025 "rows": 2,
5026 "cost": 2.80159
5027 } /* table_scan */,
5028 "potential_range_indexes": [
5029 {
5030 "index": "k1",
5031 "usable": true,
5032 "key_parts": [
5033 "i1"
5034 ] /* key_parts */
5035 },
5036 {
5037 "index": "k2",
5038 "usable": true,
5039 "key_parts": [
5040 "i1",
5041 "i2"
5042 ] /* key_parts */
5043 }
5044 ] /* potential_range_indexes */,
5045 "setup_range_conditions": [
5046 ] /* setup_range_conditions */,
5047 "group_index_range": {
5048 "chosen": false,
5049 "cause": "not_group_by_or_distinct"
5050 } /* group_index_range */,
5051 "skip_scan_range": {
5052 "potential_skip_scan_indexes": [
5053 {
5054 "index": "k1",
5055 "usable": false,
5056 "cause": "query_references_nonkey_column"
5057 },
5058 {
5059 "index": "k2",
5060 "usable": false,
5061 "cause": "query_references_nonkey_column"
5062 }
5063 ] /* potential_skip_scan_indexes */
5064 } /* skip_scan_range */,
5065 "analyzing_range_alternatives": {
5066 "range_scan_alternatives": [
5067 {
5068 "index": "k1",
5069 "ranges": [
5070 "2 < i1"
5071 ] /* ranges */,
5072 "index_dives_for_eq_ranges": true,
5073 "rowid_ordered": false,
5074 "using_mrr": false,
5075 "index_only": false,
5076 "in_memory": -1,
5077 "rows": 2,
5078 "cost": 0.96,
5079 "chosen": true
5080 },
5081 {
5082 "index": "k2",
5083 "ranges": [
5084 "2 < i1"
5085 ] /* ranges */,
5086 "index_dives_for_eq_ranges": true,
5087 "rowid_ordered": false,
5088 "using_mrr": false,
5089 "index_only": false,
5090 "in_memory": -1,
5091 "rows": 2,
5092 "cost": 0.96,
5093 "chosen": false,
5094 "cause": "cost"
5095 }
5096 ] /* range_scan_alternatives */,
5097 "analyzing_roworder_intersect": {
5098 "usable": false,
5099 "cause": "too_few_roworder_scans"
5100 } /* analyzing_roworder_intersect */
5101 } /* analyzing_range_alternatives */,
5102 "chosen_range_access_summary": {
5103 "range_access_plan": {
5104 "type": "range_scan",
5105 "index": "k1",
5106 "rows": 2,
5107 "ranges": [
5108 "2 < i1"
5109 ] /* ranges */
5110 } /* range_access_plan */,
5111 "rows_for_plan": 2,
5112 "cost_for_plan": 0.96,
5113 "chosen": true
5114 } /* chosen_range_access_summary */
5115 } /* range_analysis */
5116 }
5117 ] /* rows_estimation */
5118 },
5119 {
5120 "considered_execution_plans": [
5121 {
5122 "plan_prefix": [
5123 ] /* plan_prefix */,
5124 "table": "`t1`",
5125 "best_access_path": {
5126 "considered_access_paths": [
5127 {
5128 "rows_to_scan": 2,
5129 "filtering_effect": [
5130 ] /* filtering_effect */,
5131 "final_filtering_effect": 1,
5132 "access_type": "range",
5133 "range_details": {
5134 "used_index": "k1"
5135 } /* range_details */,
5136 "resulting_rows": 2,
5137 "cost": 1.16,
5138 "chosen": true,
5139 "use_tmp_table": true
5140 }
5141 ] /* considered_access_paths */
5142 } /* best_access_path */,
5143 "condition_filtering_pct": 100,
5144 "rows_for_plan": 2,
5145 "cost_for_plan": 1.16,
5146 "sort_cost": 2,
5147 "new_cost_for_plan": 3.16,
5148 "chosen": true
5149 }
5150 ] /* considered_execution_plans */
5151 },
5152 {
5153 "attaching_conditions_to_tables": {
5154 "original_condition": "(`t1`.`i1` > 2)",
5155 "attached_conditions_computation": [
5156 ] /* attached_conditions_computation */,
5157 "attached_conditions_summary": [
5158 {
5159 "table": "`t1`",
5160 "attached": "(`t1`.`i1` > 2)"
5161 }
5162 ] /* attached_conditions_summary */
5163 } /* attaching_conditions_to_tables */
5164 },
5165 {
5166 "optimizing_distinct_group_by_order_by": {
5167 "simplifying_order_by": {
5168 "original_clause": "`t1`.`i1`,`t1`.`i2`",
5169 "items": [
5170 {
5171 "item": "`t1`.`i1`"
5172 },
5173 {
5174 "item": "`t1`.`i2`"
5175 }
5176 ] /* items */,
5177 "resulting_clause_is_simple": true,
5178 "resulting_clause": "`t1`.`i1`,`t1`.`i2`"
5179 } /* simplifying_order_by */
5180 } /* optimizing_distinct_group_by_order_by */
5181 },
5182 {
5183 "reconsidering_access_paths_for_index_ordering": {
5184 "clause": "ORDER BY",
5185 "steps": [
5186 {
5187 "rows_estimation": {
5188 "table": "`t1`",
5189 "index": "k2",
5190 "range_analysis": {
5191 "table_scan": {
5192 "rows": 2,
5193 "cost": 2.80159
5194 } /* table_scan */,
5195 "potential_range_indexes": [
5196 {
5197 "index": "k1",
5198 "usable": false,
5199 "cause": "not_applicable"
5200 },
5201 {
5202 "index": "k2",
5203 "usable": true,
5204 "key_parts": [
5205 "i1",
5206 "i2"
5207 ] /* key_parts */
5208 }
5209 ] /* potential_range_indexes */,
5210 "setup_range_conditions": [
5211 ] /* setup_range_conditions */,
5212 "group_index_range": {
5213 "chosen": false,
5214 "cause": "not_group_by_or_distinct"
5215 } /* group_index_range */,
5216 "skip_scan_range": {
5217 "potential_skip_scan_indexes": [
5218 {
5219 "index": "k2",
5220 "usable": false,
5221 "cause": "query_references_nonkey_column"
5222 }
5223 ] /* potential_skip_scan_indexes */
5224 } /* skip_scan_range */,
5225 "analyzing_range_alternatives": {
5226 "range_scan_alternatives": [
5227 {
5228 "index": "k2",
5229 "ranges": [
5230 "2 < i1"
5231 ] /* ranges */,
5232 "index_dives_for_eq_ranges": true,
5233 "rowid_ordered": false,
5234 "using_mrr": false,
5235 "index_only": false,
5236 "in_memory": -1,
5237 "rows": 2,
5238 "cost": 0.96,
5239 "chosen": true
5240 }
5241 ] /* range_scan_alternatives */,
5242 "analyzing_roworder_intersect": {
5243 "usable": false,
5244 "cause": "too_few_roworder_scans"
5245 } /* analyzing_roworder_intersect */
5246 } /* analyzing_range_alternatives */,
5247 "chosen_range_access_summary": {
5248 "range_access_plan": {
5249 "type": "range_scan",
5250 "index": "k2",
5251 "rows": 2,
5252 "ranges": [
5253 "2 < i1"
5254 ] /* ranges */
5255 } /* range_access_plan */,
5256 "rows_for_plan": 2,
5257 "cost_for_plan": 0.96,
5258 "chosen": true
5259 } /* chosen_range_access_summary */
5260 } /* range_analysis */
5261 } /* rows_estimation */
5262 }
5263 ] /* steps */,
5264 "index_order_summary": {
5265 "table": "`t1`",
5266 "index_provides_order": true,
5267 "order_direction": "asc",
5268 "index": "k2",
5269 "plan_changed": true,
5270 "access_type": "range"
5271 } /* index_order_summary */
5272 } /* reconsidering_access_paths_for_index_ordering */
5273 },
5274 {
5275 "finalizing_table_conditions": [
5276 {
5277 "table": "`t1`",
5278 "original_table_condition": "(`t1`.`i1` > 2)",
5279 "final_table_condition ": "(`t1`.`i1` > 2)"
5280 }
5281 ] /* finalizing_table_conditions */
5282 },
5283 {
5284 "refine_plan": [
5285 {
5286 "table": "`t1`",
5287 "pushed_index_condition": "(`t1`.`i1` > 2)",
5288 "table_condition_attached": null
5289 }
5290 ] /* refine_plan */
5291 },
5292 {
5293 "considering_tmp_tables": [
5294 ] /* considering_tmp_tables */
5295 }
5296 ] /* steps */
5297 } /* join_optimization */
5298 },
5299 {
5300 "join_explain": {
5301 "select#": 1,
5302 "steps": [
5303 ] /* steps */
5304 } /* join_explain */
5305 }
5306 ] /* steps */
5307 } 0 0
5308
5309 EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
5310 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
5311 1 SIMPLE t1 NULL index k1,k2 k1 5 NULL 2 100.00 Using where; Backward index scan; Using index
5312 Warnings:
5313 Note 1003 /* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i1` >= 1) order by `test`.`t1`.`i1` desc
5314
5315 SELECT * FROM information_schema.OPTIMIZER_TRACE;
5316 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
5317 EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC {
5318 "steps": [
5319 {
5320 "join_preparation": {
5321 "select#": 1,
5322 "steps": [
5323 {
5324 "expanded_query": "/* select#1 */ select distinct `t1`.`i1` AS `i1` from `t1` where (`t1`.`i1` >= '1') order by `t1`.`i1` desc"
5325 }
5326 ] /* steps */
5327 } /* join_preparation */
5328 },
5329 {
5330 "join_optimization": {
5331 "select#": 1,
5332 "steps": [
5333 {
5334 "condition_processing": {
5335 "condition": "WHERE",
5336 "original_condition": "(`t1`.`i1` >= '1')",
5337 "steps": [
5338 {
5339 "transformation": "equality_propagation",
5340 "resulting_condition": "(`t1`.`i1` >= '1')"
5341 },
5342 {
5343 "transformation": "constant_propagation",
5344 "resulting_condition": "(`t1`.`i1` >= '1')"
5345 },
5346 {
5347 "transformation": "trivial_condition_removal",
5348 "resulting_condition": "(`t1`.`i1` >= 1)"
5349 }
5350 ] /* steps */
5351 } /* condition_processing */
5352 },
5353 {
5354 "substitute_generated_columns": {
5355 } /* substitute_generated_columns */
5356 },
5357 {
5358 "table_dependencies": [
5359 {
5360 "table": "`t1`",
5361 "row_may_be_null": false,
5362 "map_bit": 0,
5363 "depends_on_map_bits": [
5364 ] /* depends_on_map_bits */
5365 }
5366 ] /* table_dependencies */
5367 },
5368 {
5369 "ref_optimizer_key_uses": [
5370 ] /* ref_optimizer_key_uses */
5371 },
5372 {
5373 "rows_estimation": [
5374 {
5375 "table": "`t1`",
5376 "range_analysis": {
5377 "table_scan": {
5378 "rows": 2,
5379 "cost": 2.80159
5380 } /* table_scan */,
5381 "potential_range_indexes": [
5382 {
5383 "index": "k1",
5384 "usable": true,
5385 "key_parts": [
5386 "i1"
5387 ] /* key_parts */
5388 },
5389 {
5390 "index": "k2",
5391 "usable": true,
5392 "key_parts": [
5393 "i1",
5394 "i2"
5395 ] /* key_parts */
5396 }
5397 ] /* potential_range_indexes */,
5398 "best_covering_index_scan": {
5399 "index": "k1",
5400 "cost": 0.455814,
5401 "chosen": true
5402 } /* best_covering_index_scan */,
5403 "setup_range_conditions": [
5404 ] /* setup_range_conditions */,
5405 "group_index_range": {
5406 "distinct_query": true,
5407 "potential_group_range_indexes": [
5408 {
5409 "index": "k1",
5410 "covering": true,
5411 "index_dives_for_eq_ranges": true,
5412 "ranges": [
5413 "1 <= i1"
5414 ] /* ranges */,
5415 "rows": 3,
5416 "cost": 0.7
5417 },
5418 {
5419 "index": "k2",
5420 "covering": true,
5421 "index_dives_for_eq_ranges": true,
5422 "ranges": [
5423 "1 <= i1"
5424 ] /* ranges */,
5425 "rows": 3,
5426 "cost": 0.7
5427 }
5428 ] /* potential_group_range_indexes */
5429 } /* group_index_range */,
5430 "best_group_range_summary": {
5431 "type": "index_group",
5432 "index": "k1",
5433 "group_attribute": null,
5434 "min_aggregate": false,
5435 "max_aggregate": false,
5436 "distinct_aggregate": false,
5437 "rows": 3,
5438 "cost": 0.7,
5439 "key_parts_used_for_access": [
5440 "i1"
5441 ] /* key_parts_used_for_access */,
5442 "ranges": [
5443 "1 <= i1"
5444 ] /* ranges */,
5445 "chosen": false,
5446 "cause": "cost"
5447 } /* best_group_range_summary */,
5448 "skip_scan_range": {
5449 "chosen": false,
5450 "cause": "has_select_distinct"
5451 } /* skip_scan_range */,
5452 "analyzing_range_alternatives": {
5453 "range_scan_alternatives": [
5454 {
5455 "index": "k1",
5456 "ranges": [
5457 "1 <= i1"
5458 ] /* ranges */,
5459 "index_dives_for_eq_ranges": true,
5460 "rowid_ordered": false,
5461 "using_mrr": false,
5462 "index_only": true,
5463 "in_memory": -1,
5464 "rows": 2,
5465 "cost": 0.465814,
5466 "chosen": false,
5467 "cause": "cost"
5468 },
5469 {
5470 "index": "k2",
5471 "ranges": [
5472 "1 <= i1"
5473 ] /* ranges */,
5474 "index_dives_for_eq_ranges": true,
5475 "rowid_ordered": false,
5476 "using_mrr": false,
5477 "index_only": true,
5478 "in_memory": -1,
5479 "rows": 2,
5480 "cost": 0.468065,
5481 "chosen": false,
5482 "cause": "cost"
5483 }
5484 ] /* range_scan_alternatives */,
5485 "analyzing_roworder_intersect": {
5486 "usable": false,
5487 "cause": "too_few_roworder_scans"
5488 } /* analyzing_roworder_intersect */
5489 } /* analyzing_range_alternatives */
5490 } /* range_analysis */
5491 }
5492 ] /* rows_estimation */
5493 },
5494 {
5495 "considered_execution_plans": [
5496 {
5497 "plan_prefix": [
5498 ] /* plan_prefix */,
5499 "table": "`t1`",
5500 "best_access_path": {
5501 "considered_access_paths": [
5502 {
5503 "rows_to_scan": 2,
5504 "filtering_effect": [
5505 ] /* filtering_effect */,
5506 "final_filtering_effect": 1,
5507 "access_type": "scan",
5508 "resulting_rows": 2,
5509 "cost": 0.701587,
5510 "chosen": true,
5511 "use_tmp_table": true
5512 }
5513 ] /* considered_access_paths */
5514 } /* best_access_path */,
5515 "condition_filtering_pct": 100,
5516 "rows_for_plan": 2,
5517 "cost_for_plan": 0.701587,
5518 "sort_cost": 2,
5519 "new_cost_for_plan": 2.70159,
5520 "chosen": true
5521 }
5522 ] /* considered_execution_plans */
5523 },
5524 {
5525 "attaching_conditions_to_tables": {
5526 "original_condition": "(`t1`.`i1` >= 1)",
5527 "attached_conditions_computation": [
5528 ] /* attached_conditions_computation */,
5529 "attached_conditions_summary": [
5530 {
5531 "table": "`t1`",
5532 "attached": "(`t1`.`i1` >= 1)"
5533 }
5534 ] /* attached_conditions_summary */
5535 } /* attaching_conditions_to_tables */
5536 },
5537 {
5538 "optimizing_distinct_group_by_order_by": {
5539 "simplifying_order_by": {
5540 "original_clause": "`t1`.`i1` desc",
5541 "items": [
5542 {
5543 "item": "`t1`.`i1`"
5544 }
5545 ] /* items */,
5546 "resulting_clause_is_simple": true,
5547 "resulting_clause": "`t1`.`i1` desc"
5548 } /* simplifying_order_by */,
5549 "unknown_key_1": {
5550 "reconsidering_access_paths_for_index_ordering": {
5551 "clause": "ORDER BY",
5552 "steps": [
5553 ] /* steps */,
5554 "index_order_summary": {
5555 "table": "`t1`",
5556 "index_provides_order": true,
5557 "order_direction": "desc",
5558 "index": "k1",
5559 "plan_changed": false
5560 } /* index_order_summary */
5561 } /* reconsidering_access_paths_for_index_ordering */
5562 },
5563 "unknown_key_2": {
5564 "reconsidering_access_paths_for_index_ordering": {
5565 "clause": "GROUP BY",
5566 "steps": [
5567 ] /* steps */,
5568 "index_order_summary": {
5569 "table": "`t1`",
5570 "index_provides_order": true,
5571 "order_direction": "desc",
5572 "index": "k1",
5573 "plan_changed": false
5574 } /* index_order_summary */
5575 } /* reconsidering_access_paths_for_index_ordering */
5576 },
5577 "changed_distinct_to_group_by": true,
5578 "simplifying_group_by": {
5579 "original_clause": "`t1`.`i1` desc",
5580 "items": [
5581 {
5582 "item": "`t1`.`i1`"
5583 }
5584 ] /* items */,
5585 "resulting_clause_is_simple": true,
5586 "resulting_clause": "`t1`.`i1` desc"
5587 } /* simplifying_group_by */,
5588 "removed_order_by": true
5589 } /* optimizing_distinct_group_by_order_by */
5590 },
5591 {
5592 "reconsidering_access_paths_for_index_ordering": {
5593 "clause": "GROUP BY",
5594 "steps": [
5595 ] /* steps */,
5596 "index_order_summary": {
5597 "table": "`t1`",
5598 "index_provides_order": true,
5599 "order_direction": "desc",
5600 "index": "k1",
5601 "plan_changed": false
5602 } /* index_order_summary */
5603 } /* reconsidering_access_paths_for_index_ordering */
5604 },
5605 {
5606 "finalizing_table_conditions": [
5607 {
5608 "table": "`t1`",
5609 "original_table_condition": "(`t1`.`i1` >= 1)",
5610 "final_table_condition ": "(`t1`.`i1` >= 1)"
5611 }
5612 ] /* finalizing_table_conditions */
5613 },
5614 {
5615 "refine_plan": [
5616 {
5617 "table": "`t1`"
5618 }
5619 ] /* refine_plan */
5620 },
5621 {
5622 "considering_tmp_tables": [
5623 ] /* considering_tmp_tables */
5624 }
5625 ] /* steps */
5626 } /* join_optimization */
5627 },
5628 {
5629 "join_explain": {
5630 "select#": 1,
5631 "steps": [
5632 ] /* steps */
5633 } /* join_explain */
5634 }
5635 ] /* steps */
5636 } 0 0
5637
5638 DROP TABLE t1;
5639 CREATE TABLE t1 (
5640 pk INT PRIMARY KEY,
5641 i1 INT,
5642 i2 INT,
5643 v varchar(1),
5644 INDEX i1_idx (i1),
5645 INDEX v_idx (v,i1)
5646 ) ENGINE=InnoDB, CHARSET utf8mb4;
5647 INSERT INTO t1 VALUES (1, 1, 9,'a'), (2, 2, 8,'b'), (3, 3, 7,'c'),
5648 (4, 4, 6,'d'), (5, 5, 5,'e');
5649
5650 # Covering ROR intersect not chosen: Index with more keyparts found.
5651 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
5652 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
5653 1 SIMPLE t1 NULL range PRIMARY,i1_idx,v_idx v_idx 16 NULL 1 100.00 Using where; Using index
5654 Warnings:
5655 Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))
5656
5657 SELECT * FROM information_schema.OPTIMIZER_TRACE;
5658 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
5659 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3 {
5660 "steps": [
5661 {
5662 "join_preparation": {
5663 "select#": 1,
5664 "steps": [
5665 {
5666 "expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))"
5667 }
5668 ] /* steps */
5669 } /* join_preparation */
5670 },
5671 {
5672 "join_optimization": {
5673 "select#": 1,
5674 "steps": [
5675 {
5676 "condition_processing": {
5677 "condition": "WHERE",
5678 "original_condition": "((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))",
5679 "steps": [
5680 {
5681 "transformation": "equality_propagation",
5682 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
5683 },
5684 {
5685 "transformation": "constant_propagation",
5686 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
5687 },
5688 {
5689 "transformation": "trivial_condition_removal",
5690 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
5691 }
5692 ] /* steps */
5693 } /* condition_processing */
5694 },
5695 {
5696 "substitute_generated_columns": {
5697 } /* substitute_generated_columns */
5698 },
5699 {
5700 "table_dependencies": [
5701 {
5702 "table": "`t1`",
5703 "row_may_be_null": false,
5704 "map_bit": 0,
5705 "depends_on_map_bits": [
5706 ] /* depends_on_map_bits */
5707 }
5708 ] /* table_dependencies */
5709 },
5710 {
5711 "ref_optimizer_key_uses": [
5712 {
5713 "table": "`t1`",
5714 "field": "i1",
5715 "equals": "1",
5716 "null_rejecting": true
5717 },
5718 {
5719 "table": "`t1`",
5720 "field": "v",
5721 "equals": "'a'",
5722 "null_rejecting": true
5723 },
5724 {
5725 "table": "`t1`",
5726 "field": "i1",
5727 "equals": "1",
5728 "null_rejecting": true
5729 }
5730 ] /* ref_optimizer_key_uses */
5731 },
5732 {
5733 "rows_estimation": [
5734 {
5735 "table": "`t1`",
5736 "range_analysis": {
5737 "table_scan": {
5738 "rows": 5,
5739 "cost": 2.85
5740 } /* table_scan */,
5741 "potential_range_indexes": [
5742 {
5743 "index": "PRIMARY",
5744 "usable": true,
5745 "key_parts": [
5746 "pk"
5747 ] /* key_parts */
5748 },
5749 {
5750 "index": "i1_idx",
5751 "usable": true,
5752 "key_parts": [
5753 "i1",
5754 "pk"
5755 ] /* key_parts */
5756 },
5757 {
5758 "index": "v_idx",
5759 "usable": true,
5760 "key_parts": [
5761 "v",
5762 "i1",
5763 "pk"
5764 ] /* key_parts */
5765 }
5766 ] /* potential_range_indexes */,
5767 "best_covering_index_scan": {
5768 "index": "v_idx",
5769 "cost": 0.751949,
5770 "chosen": true
5771 } /* best_covering_index_scan */,
5772 "setup_range_conditions": [
5773 ] /* setup_range_conditions */,
5774 "group_index_range": {
5775 "chosen": false,
5776 "cause": "not_group_by_or_distinct"
5777 } /* group_index_range */,
5778 "skip_scan_range": {
5779 "potential_skip_scan_indexes": [
5780 {
5781 "index": "PRIMARY",
5782 "usable": false,
5783 "cause": "query_references_nonkey_column"
5784 },
5785 {
5786 "index": "i1_idx",
5787 "usable": false,
5788 "cause": "query_references_nonkey_column"
5789 },
5790 {
5791 "index": "v_idx",
5792 "usable": false,
5793 "cause": "prefix_not_const_equality"
5794 }
5795 ] /* potential_skip_scan_indexes */
5796 } /* skip_scan_range */,
5797 "analyzing_range_alternatives": {
5798 "range_scan_alternatives": [
5799 {
5800 "index": "PRIMARY",
5801 "ranges": [
5802 "pk < 3"
5803 ] /* ranges */,
5804 "index_dives_for_eq_ranges": true,
5805 "rowid_ordered": true,
5806 "using_mrr": false,
5807 "index_only": false,
5808 "in_memory": 1,
5809 "rows": 2,
5810 "cost": 0.71,
5811 "chosen": true
5812 },
5813 {
5814 "index": "i1_idx",
5815 "ranges": [
5816 "i1 = 1 AND pk < 3"
5817 ] /* ranges */,
5818 "index_dives_for_eq_ranges": true,
5819 "rowid_ordered": true,
5820 "using_mrr": false,
5821 "index_only": false,
5822 "in_memory": 1,
5823 "rows": 1,
5824 "cost": 0.61,
5825 "chosen": true
5826 },
5827 {
5828 "index": "v_idx",
5829 "ranges": [
5830 "v = 'a' AND i1 = 1 AND pk < 3"
5831 ] /* ranges */,
5832 "index_dives_for_eq_ranges": true,
5833 "rowid_ordered": true,
5834 "using_mrr": false,
5835 "index_only": true,
5836 "in_memory": 1,
5837 "rows": 1,
5838 "cost": 0.36,
5839 "chosen": true
5840 }
5841 ] /* range_scan_alternatives */,
5842 "analyzing_roworder_intersect": {
5843 "intersecting_indexes": [
5844 {
5845 "index": "v_idx",
5846 "index_scan_cost": 0.25,
5847 "cumulated_index_scan_cost": 0.25,
5848 "disk_sweep_cost": 0,
5849 "cumulated_total_cost": 0.25,
5850 "usable": true,
5851 "matching_rows_now": 1,
5852 "isect_covering_with_this_index": true,
5853 "chosen": true
5854 }
5855 ] /* intersecting_indexes */,
5856 "clustered_pk": {
5857 "clustered_pk_added_to_intersect": false,
5858 "cause": "roworder_is_covering"
5859 } /* clustered_pk */,
5860 "chosen": false,
5861 "cause": "too_few_indexes_to_merge"
5862 } /* analyzing_roworder_intersect */
5863 } /* analyzing_range_alternatives */,
5864 "chosen_range_access_summary": {
5865 "range_access_plan": {
5866 "type": "range_scan",
5867 "index": "v_idx",
5868 "rows": 1,
5869 "ranges": [
5870 "v = 'a' AND i1 = 1 AND pk < 3"
5871 ] /* ranges */
5872 } /* range_access_plan */,
5873 "rows_for_plan": 1,
5874 "cost_for_plan": 0.36,
5875 "chosen": true
5876 } /* chosen_range_access_summary */
5877 } /* range_analysis */
5878 }
5879 ] /* rows_estimation */
5880 },
5881 {
5882 "considered_execution_plans": [
5883 {
5884 "plan_prefix": [
5885 ] /* plan_prefix */,
5886 "table": "`t1`",
5887 "best_access_path": {
5888 "considered_access_paths": [
5889 {
5890 "access_type": "ref",
5891 "index": "i1_idx",
5892 "chosen": false,
5893 "cause": "range_uses_more_keyparts"
5894 },
5895 {
5896 "access_type": "ref",
5897 "index": "v_idx",
5898 "chosen": false,
5899 "cause": "range_uses_more_keyparts"
5900 },
5901 {
5902 "rows_to_scan": 1,
5903 "filtering_effect": [
5904 ] /* filtering_effect */,
5905 "final_filtering_effect": 1,
5906 "access_type": "range",
5907 "range_details": {
5908 "used_index": "v_idx"
5909 } /* range_details */,
5910 "resulting_rows": 1,
5911 "cost": 0.46,
5912 "chosen": true
5913 }
5914 ] /* considered_access_paths */
5915 } /* best_access_path */,
5916 "condition_filtering_pct": 100,
5917 "rows_for_plan": 1,
5918 "cost_for_plan": 0.46,
5919 "chosen": true
5920 }
5921 ] /* considered_execution_plans */
5922 },
5923 {
5924 "attaching_conditions_to_tables": {
5925 "original_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
5926 "attached_conditions_computation": [
5927 ] /* attached_conditions_computation */,
5928 "attached_conditions_summary": [
5929 {
5930 "table": "`t1`",
5931 "attached": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
5932 }
5933 ] /* attached_conditions_summary */
5934 } /* attaching_conditions_to_tables */
5935 },
5936 {
5937 "finalizing_table_conditions": [
5938 {
5939 "table": "`t1`",
5940 "original_table_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
5941 "final_table_condition ": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
5942 }
5943 ] /* finalizing_table_conditions */
5944 },
5945 {
5946 "refine_plan": [
5947 {
5948 "table": "`t1`"
5949 }
5950 ] /* refine_plan */
5951 }
5952 ] /* steps */
5953 } /* join_optimization */
5954 },
5955 {
5956 "join_explain": {
5957 "select#": 1,
5958 "steps": [
5959 ] /* steps */
5960 } /* join_explain */
5961 }
5962 ] /* steps */
5963 } 0 0
5964
5965 # Chooses ROR intersect
5966 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3;
5967 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
5968 1 SIMPLE t1 NULL index_merge PRIMARY,i1_idx,v_idx i1_idx,PRIMARY 9,4 NULL 1 100.00 Using intersect(i1_idx,PRIMARY); Using where
5969 Warnings:
5970 Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))
5971
5972 SELECT * FROM information_schema.OPTIMIZER_TRACE;
5973 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
5974 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3 {
5975 "steps": [
5976 {
5977 "join_preparation": {
5978 "select#": 1,
5979 "steps": [
5980 {
5981 "expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
5982 }
5983 ] /* steps */
5984 } /* join_preparation */
5985 },
5986 {
5987 "join_optimization": {
5988 "select#": 1,
5989 "steps": [
5990 {
5991 "condition_processing": {
5992 "condition": "WHERE",
5993 "original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
5994 "steps": [
5995 {
5996 "transformation": "equality_propagation",
5997 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
5998 },
5999 {
6000 "transformation": "constant_propagation",
6001 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
6002 },
6003 {
6004 "transformation": "trivial_condition_removal",
6005 "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
6006 }
6007 ] /* steps */
6008 } /* condition_processing */
6009 },
6010 {
6011 "substitute_generated_columns": {
6012 } /* substitute_generated_columns */
6013 },
6014 {
6015 "table_dependencies": [
6016 {
6017 "table": "`t1`",
6018 "row_may_be_null": false,
6019 "map_bit": 0,
6020 "depends_on_map_bits": [
6021 ] /* depends_on_map_bits */
6022 }
6023 ] /* table_dependencies */
6024 },
6025 {
6026 "ref_optimizer_key_uses": [
6027 {
6028 "table": "`t1`",
6029 "field": "i1",
6030 "equals": "1",
6031 "null_rejecting": true
6032 }
6033 ] /* ref_optimizer_key_uses */
6034 },
6035 {
6036 "rows_estimation": [
6037 {
6038 "table": "`t1`",
6039 "range_analysis": {
6040 "table_scan": {
6041 "rows": 5,
6042 "cost": 2.85
6043 } /* table_scan */,
6044 "potential_range_indexes": [
6045 {
6046 "index": "PRIMARY",
6047 "usable": true,
6048 "key_parts": [
6049 "pk"
6050 ] /* key_parts */
6051 },
6052 {
6053 "index": "i1_idx",
6054 "usable": true,
6055 "key_parts": [
6056 "i1",
6057 "pk"
6058 ] /* key_parts */
6059 },
6060 {
6061 "index": "v_idx",
6062 "usable": true,
6063 "key_parts": [
6064 "v",
6065 "i1",
6066 "pk"
6067 ] /* key_parts */
6068 }
6069 ] /* potential_range_indexes */,
6070 "best_covering_index_scan": {
6071 "index": "v_idx",
6072 "cost": 0.751949,
6073 "chosen": true
6074 } /* best_covering_index_scan */,
6075 "setup_range_conditions": [
6076 ] /* setup_range_conditions */,
6077 "group_index_range": {
6078 "chosen": false,
6079 "cause": "not_group_by_or_distinct"
6080 } /* group_index_range */,
6081 "skip_scan_range": {
6082 "potential_skip_scan_indexes": [
6083 {
6084 "index": "PRIMARY",
6085 "usable": false,
6086 "cause": "query_references_nonkey_column"
6087 },
6088 {
6089 "index": "i1_idx",
6090 "usable": false,
6091 "cause": "query_references_nonkey_column"
6092 },
6093 {
6094 "index": "v_idx",
6095 "tree_travel_cost": 0.15,
6096 "num_groups": 6,
6097 "rows": 5,
6098 "cost": 2.80195
6099 }
6100 ] /* potential_skip_scan_indexes */
6101 } /* skip_scan_range */,
6102 "best_skip_scan_summary": {
6103 "type": "skip_scan",
6104 "index": "v_idx",
6105 "key_parts_used_for_access": [
6106 "v",
6107 "i1"
6108 ] /* key_parts_used_for_access */,
6109 "range": [
6110 "i1 = 1"
6111 ] /* range */,
6112 "chosen": false,
6113 "cause": "cost"
6114 } /* best_skip_scan_summary */,
6115 "analyzing_range_alternatives": {
6116 "range_scan_alternatives": [
6117 {
6118 "index": "PRIMARY",
6119 "ranges": [
6120 "pk < 3"
6121 ] /* ranges */,
6122 "index_dives_for_eq_ranges": true,
6123 "rowid_ordered": true,
6124 "using_mrr": false,
6125 "index_only": false,
6126 "in_memory": 1,
6127 "rows": 2,
6128 "cost": 0.71,
6129 "chosen": true
6130 },
6131 {
6132 "index": "i1_idx",
6133 "ranges": [
6134 "i1 = 1 AND pk < 3"
6135 ] /* ranges */,
6136 "index_dives_for_eq_ranges": true,
6137 "rowid_ordered": true,
6138 "using_mrr": false,
6139 "index_only": false,
6140 "in_memory": 1,
6141 "rows": 1,
6142 "cost": 0.61,
6143 "chosen": true
6144 },
6145 {
6146 "index": "v_idx",
6147 "chosen": false,
6148 "cause": "no_valid_range_for_this_index"
6149 }
6150 ] /* range_scan_alternatives */,
6151 "analyzing_roworder_intersect": {
6152 "intersecting_indexes": [
6153 {
6154 "index": "i1_idx",
6155 "index_scan_cost": 0.25,
6156 "cumulated_index_scan_cost": 0.25,
6157 "disk_sweep_cost": 0.25,
6158 "cumulated_total_cost": 0.5,
6159 "usable": true,
6160 "matching_rows_now": 1,
6161 "isect_covering_with_this_index": false,
6162 "chosen": true
6163 }
6164 ] /* intersecting_indexes */,
6165 "clustered_pk": {
6166 "index_scan_cost": 0.05,
6167 "cumulated_index_scan_cost": 0.3,
6168 "disk_sweep_cost": 0,
6169 "clustered_pk_scan_added_to_intersect": true,
6170 "cumulated_cost": 0.3
6171 } /* clustered_pk */,
6172 "rows": 1,
6173 "cost": 0.3,
6174 "covering": false,
6175 "chosen": true
6176 } /* analyzing_roworder_intersect */
6177 } /* analyzing_range_alternatives */,
6178 "chosen_range_access_summary": {
6179 "range_access_plan": {
6180 "type": "index_roworder_intersect",
6181 "rows": 1,
6182 "cost": 0.3,
6183 "covering": false,
6184 "clustered_pk_scan": true,
6185 "intersect_of": [
6186 {
6187 "type": "range_scan",
6188 "index": "i1_idx",
6189 "rows": 1,
6190 "ranges": [
6191 "i1 = 1 AND pk < 3"
6192 ] /* ranges */
6193 }
6194 ] /* intersect_of */
6195 } /* range_access_plan */,
6196 "rows_for_plan": 1,
6197 "cost_for_plan": 0.3,
6198 "chosen": true
6199 } /* chosen_range_access_summary */
6200 } /* range_analysis */
6201 }
6202 ] /* rows_estimation */
6203 },
6204 {
6205 "considered_execution_plans": [
6206 {
6207 "plan_prefix": [
6208 ] /* plan_prefix */,
6209 "table": "`t1`",
6210 "best_access_path": {
6211 "considered_access_paths": [
6212 {
6213 "access_type": "ref",
6214 "index": "i1_idx",
6215 "chosen": false,
6216 "cause": "range_uses_more_keyparts"
6217 },
6218 {
6219 "rows_to_scan": 1,
6220 "filtering_effect": [
6221 ] /* filtering_effect */,
6222 "final_filtering_effect": 1,
6223 "access_type": "range",
6224 "range_details": {
6225 "used_index": "intersect(i1_idx,PRIMARY)"
6226 } /* range_details */,
6227 "resulting_rows": 1,
6228 "cost": 0.4,
6229 "chosen": true
6230 }
6231 ] /* considered_access_paths */
6232 } /* best_access_path */,
6233 "condition_filtering_pct": 100,
6234 "rows_for_plan": 1,
6235 "cost_for_plan": 0.4,
6236 "chosen": true
6237 }
6238 ] /* considered_execution_plans */
6239 },
6240 {
6241 "attaching_conditions_to_tables": {
6242 "original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
6243 "attached_conditions_computation": [
6244 ] /* attached_conditions_computation */,
6245 "attached_conditions_summary": [
6246 {
6247 "table": "`t1`",
6248 "attached": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
6249 }
6250 ] /* attached_conditions_summary */
6251 } /* attaching_conditions_to_tables */
6252 },
6253 {
6254 "finalizing_table_conditions": [
6255 {
6256 "table": "`t1`",
6257 "original_table_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
6258 "final_table_condition ": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
6259 }
6260 ] /* finalizing_table_conditions */
6261 },
6262 {
6263 "refine_plan": [
6264 {
6265 "table": "`t1`"
6266 }
6267 ] /* refine_plan */
6268 }
6269 ] /* steps */
6270 } /* join_optimization */
6271 },
6272 {
6273 "join_explain": {
6274 "select#": 1,
6275 "steps": [
6276 ] /* steps */
6277 } /* join_explain */
6278 }
6279 ] /* steps */
6280 } 0 0
6281
6282 DROP TABLE t1;
6283 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB STATS_PERSISTENT=0;
6284 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
6285
6286 # Test trace for unreliable_ref_cost_and_range_uses_more_keyparts to
6287 # ignore ref access on index with overly optimistic cost-estimate
6288 EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
6289 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
6290 1 SIMPLE t1 NULL range PRIMARY,b PRIMARY 8 NULL 1 100.00 Using where; Using index for group-by
6291 Warnings:
6292 Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2)) group by `test`.`t1`.`a`
6293
6294 SELECT * FROM information_schema.OPTIMIZER_TRACE;
6295 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
6296 EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a {
6297 "steps": [
6298 {
6299 "join_preparation": {
6300 "select#": 1,
6301 "steps": [
6302 {
6303 "expanded_query": "/* select#1 */ select max(`t1`.`b`) AS `MAX(b)`,`t1`.`a` AS `a` from `t1` where ((`t1`.`b` < 2) and (`t1`.`a` = 1)) group by `t1`.`a`"
6304 }
6305 ] /* steps */
6306 } /* join_preparation */
6307 },
6308 {
6309 "join_optimization": {
6310 "select#": 1,
6311 "steps": [
6312 {
6313 "condition_processing": {
6314 "condition": "WHERE",
6315 "original_condition": "((`t1`.`b` < 2) and (`t1`.`a` = 1))",
6316 "steps": [
6317 {
6318 "transformation": "equality_propagation",
6319 "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
6320 },
6321 {
6322 "transformation": "constant_propagation",
6323 "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
6324 },
6325 {
6326 "transformation": "trivial_condition_removal",
6327 "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
6328 }
6329 ] /* steps */
6330 } /* condition_processing */
6331 },
6332 {
6333 "substitute_generated_columns": {
6334 } /* substitute_generated_columns */
6335 },
6336 {
6337 "table_dependencies": [
6338 {
6339 "table": "`t1`",
6340 "row_may_be_null": false,
6341 "map_bit": 0,
6342 "depends_on_map_bits": [
6343 ] /* depends_on_map_bits */
6344 }
6345 ] /* table_dependencies */
6346 },
6347 {
6348 "ref_optimizer_key_uses": [
6349 {
6350 "table": "`t1`",
6351 "field": "a",
6352 "equals": "1",
6353 "null_rejecting": true
6354 }
6355 ] /* ref_optimizer_key_uses */
6356 },
6357 {
6358 "rows_estimation": [
6359 {
6360 "table": "`t1`",
6361 "range_analysis": {
6362 "table_scan": {
6363 "rows": 4,
6364 "cost": 2.75
6365 } /* table_scan */,
6366 "potential_range_indexes": [
6367 {
6368 "index": "PRIMARY",
6369 "usable": true,
6370 "key_parts": [
6371 "a",
6372 "b"
6373 ] /* key_parts */
6374 },
6375 {
6376 "index": "b",
6377 "usable": true,
6378 "key_parts": [
6379 "b",
6380 "a"
6381 ] /* key_parts */
6382 }
6383 ] /* potential_range_indexes */,
6384 "best_covering_index_scan": {
6385 "index": "b",
6386 "cost": 0.651098,
6387 "chosen": true
6388 } /* best_covering_index_scan */,
6389 "setup_range_conditions": [
6390 ] /* setup_range_conditions */,
6391 "group_index_range": {
6392 "potential_group_range_indexes": [
6393 {
6394 "index": "PRIMARY",
6395 "covering": true,
6396 "index_dives_for_eq_ranges": true,
6397 "ranges": [
6398 "a = 1 AND b < 2"
6399 ] /* ranges */,
6400 "rows": 1,
6401 "cost": 0.4
6402 },
6403 {
6404 "index": "b",
6405 "covering": true,
6406 "usable": false,
6407 "cause": "group_attribute_not_prefix_in_index"
6408 }
6409 ] /* potential_group_range_indexes */
6410 } /* group_index_range */,
6411 "best_group_range_summary": {
6412 "type": "index_group",
6413 "index": "PRIMARY",
6414 "group_attribute": "b",
6415 "min_aggregate": false,
6416 "max_aggregate": true,
6417 "distinct_aggregate": false,
6418 "rows": 1,
6419 "cost": 0.4,
6420 "key_parts_used_for_access": [
6421 "a",
6422 "b"
6423 ] /* key_parts_used_for_access */,
6424 "ranges": [
6425 "a = 1 AND b < 2"
6426 ] /* ranges */,
6427 "chosen": true
6428 } /* best_group_range_summary */,
6429 "skip_scan_range": {
6430 "chosen": false,
6431 "cause": "has_group_by"
6432 } /* skip_scan_range */,
6433 "analyzing_range_alternatives": {
6434 "range_scan_alternatives": [
6435 {
6436 "index": "PRIMARY",
6437 "ranges": [
6438 "a = 1 AND b < 2"
6439 ] /* ranges */,
6440 "index_dives_for_eq_ranges": true,
6441 "rowid_ordered": true,
6442 "using_mrr": false,
6443 "index_only": true,
6444 "in_memory": 1,
6445 "rows": 2,
6446 "cost": 0.71,
6447 "chosen": false,
6448 "cause": "cost"
6449 },
6450 {
6451 "index": "b",
6452 "ranges": [
6453 "b < 2"
6454 ] /* ranges */,
6455 "index_dives_for_eq_ranges": true,
6456 "rowid_ordered": false,
6457 "using_mrr": false,
6458 "index_only": true,
6459 "in_memory": 1,
6460 "rows": 2,
6461 "cost": 0.460366,
6462 "chosen": false,
6463 "cause": "cost"
6464 }
6465 ] /* range_scan_alternatives */,
6466 "analyzing_roworder_intersect": {
6467 "usable": false,
6468 "cause": "too_few_roworder_scans"
6469 } /* analyzing_roworder_intersect */
6470 } /* analyzing_range_alternatives */,
6471 "chosen_range_access_summary": {
6472 "range_access_plan": {
6473 "type": "index_group",
6474 "index": "PRIMARY",
6475 "group_attribute": "b",
6476 "min_aggregate": false,
6477 "max_aggregate": true,
6478 "distinct_aggregate": false,
6479 "rows": 1,
6480 "cost": 0.4,
6481 "key_parts_used_for_access": [
6482 "a",
6483 "b"
6484 ] /* key_parts_used_for_access */,
6485 "ranges": [
6486 "a = 1 AND b < 2"
6487 ] /* ranges */
6488 } /* range_access_plan */,
6489 "rows_for_plan": 1,
6490 "cost_for_plan": 0.4,
6491 "chosen": true
6492 } /* chosen_range_access_summary */
6493 } /* range_analysis */
6494 }
6495 ] /* rows_estimation */
6496 },
6497 {
6498 "considered_execution_plans": [
6499 {
6500 "plan_prefix": [
6501 ] /* plan_prefix */,
6502 "table": "`t1`",
6503 "best_access_path": {
6504 "considered_access_paths": [
6505 {
6506 "access_type": "ref",
6507 "index": "PRIMARY",
6508 "chosen": false,
6509 "cause": "range_uses_more_keyparts"
6510 },
6511 {
6512 "rows_to_scan": 1,
6513 "filtering_effect": [
6514 ] /* filtering_effect */,
6515 "final_filtering_effect": 1,
6516 "access_type": "range",
6517 "range_details": {
6518 "used_index": "index_for_group_by(PRIMARY)"
6519 } /* range_details */,
6520 "resulting_rows": 1,
6521 "cost": 0.5,
6522 "chosen": true,
6523 "use_tmp_table": true
6524 }
6525 ] /* considered_access_paths */
6526 } /* best_access_path */,
6527 "condition_filtering_pct": 100,
6528 "rows_for_plan": 1,
6529 "cost_for_plan": 0.5,
6530 "sort_cost": 1,
6531 "new_cost_for_plan": 1.5,
6532 "chosen": true
6533 }
6534 ] /* considered_execution_plans */
6535 },
6536 {
6537 "attaching_conditions_to_tables": {
6538 "original_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))",
6539 "attached_conditions_computation": [
6540 ] /* attached_conditions_computation */,
6541 "attached_conditions_summary": [
6542 {
6543 "table": "`t1`",
6544 "attached": "((`t1`.`a` = 1) and (`t1`.`b` < 2))"
6545 }
6546 ] /* attached_conditions_summary */
6547 } /* attaching_conditions_to_tables */
6548 },
6549 {
6550 "optimizing_distinct_group_by_order_by": {
6551 "simplifying_group_by": {
6552 "original_clause": "`t1`.`a`",
6553 "items": [
6554 {
6555 "item": "`t1`.`a`",
6556 "equals_constant_in_where": true
6557 }
6558 ] /* items */,
6559 "resulting_clause_is_simple": true,
6560 "resulting_clause": ""
6561 } /* simplifying_group_by */
6562 } /* optimizing_distinct_group_by_order_by */
6563 },
6564 {
6565 "finalizing_table_conditions": [
6566 {
6567 "table": "`t1`",
6568 "original_table_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))",
6569 "final_table_condition ": "((`t1`.`a` = 1) and (`t1`.`b` < 2))"
6570 }
6571 ] /* finalizing_table_conditions */
6572 },
6573 {
6574 "refine_plan": [
6575 {
6576 "table": "`t1`"
6577 }
6578 ] /* refine_plan */
6579 }
6580 ] /* steps */
6581 } /* join_optimization */
6582 },
6583 {
6584 "join_explain": {
6585 "select#": 1,
6586 "steps": [
6587 ] /* steps */
6588 } /* join_explain */
6589 }
6590 ] /* steps */
6591 } 0 0
6592
6593 drop table t1;
6594 #
6595 # Tracing of when test_if_skip_sort_order() switches to another
6596 # index and we abandon ICP,
6597 # see "disabling_pushed_condition_on_old_index" in trace.
6598 #
6599 CREATE TABLE t1 (
6600 c1 VARCHAR(2) NOT NULL,
6601 i1 INTEGER NOT NULL,
6602 c2 VARCHAR(2) NOT NULL,
6603 KEY k1 (c1),
6604 KEY k2 (c1, i1)
6605 ) CHARSET utf8mb4;
6606 INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1');
6607 EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1;
6608 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
6609 1 SIMPLE t1 NULL ref k1,k2 k2 10 const 1 100.00 NULL
6610 Warnings:
6611 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` = '1') order by `test`.`t1`.`i1`
6612 SELECT * FROM information_schema.OPTIMIZER_TRACE;
6613 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
6614 EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1 {
6615 "steps": [
6616 {
6617 "join_preparation": {
6618 "select#": 1,
6619 "steps": [
6620 {
6621 "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`i1` AS `i1`,`t1`.`c2` AS `c2` from `t1` where (`t1`.`c1` = '1') order by `t1`.`i1`"
6622 }
6623 ] /* steps */
6624 } /* join_preparation */
6625 },
6626 {
6627 "join_optimization": {
6628 "select#": 1,
6629 "steps": [
6630 {
6631 "condition_processing": {
6632 "condition": "WHERE",
6633 "original_condition": "(`t1`.`c1` = '1')",
6634 "steps": [
6635 {
6636 "transformation": "equality_propagation",
6637 "resulting_condition": "multiple equal('1', `t1`.`c1`)"
6638 },
6639 {
6640 "transformation": "constant_propagation",
6641 "resulting_condition": "multiple equal('1', `t1`.`c1`)"
6642 },
6643 {
6644 "transformation": "trivial_condition_removal",
6645 "resulting_condition": "multiple equal('1', `t1`.`c1`)"
6646 }
6647 ] /* steps */
6648 } /* condition_processing */
6649 },
6650 {
6651 "substitute_generated_columns": {
6652 } /* substitute_generated_columns */
6653 },
6654 {
6655 "table_dependencies": [
6656 {
6657 "table": "`t1`",
6658 "row_may_be_null": false,
6659 "map_bit": 0,
6660 "depends_on_map_bits": [
6661 ] /* depends_on_map_bits */
6662 }
6663 ] /* table_dependencies */
6664 },
6665 {
6666 "ref_optimizer_key_uses": [
6667 {
6668 "table": "`t1`",
6669 "field": "c1",
6670 "equals": "'1'",
6671 "null_rejecting": true
6672 },
6673 {
6674 "table": "`t1`",
6675 "field": "c1",
6676 "equals": "'1'",
6677 "null_rejecting": true
6678 }
6679 ] /* ref_optimizer_key_uses */
6680 },
6681 {
6682 "rows_estimation": [
6683 {
6684 "table": "`t1`",
6685 "range_analysis": {
6686 "table_scan": {
6687 "rows": 2,
6688 "cost": 2.80244
6689 } /* table_scan */,
6690 "potential_range_indexes": [
6691 {
6692 "index": "k1",
6693 "usable": true,
6694 "key_parts": [
6695 "c1"
6696 ] /* key_parts */
6697 },
6698 {
6699 "index": "k2",
6700 "usable": true,
6701 "key_parts": [
6702 "c1",
6703 "i1"
6704 ] /* key_parts */
6705 }
6706 ] /* potential_range_indexes */,
6707 "setup_range_conditions": [
6708 ] /* setup_range_conditions */,
6709 "group_index_range": {
6710 "chosen": false,
6711 "cause": "not_group_by_or_distinct"
6712 } /* group_index_range */,
6713 "skip_scan_range": {
6714 "potential_skip_scan_indexes": [
6715 {
6716 "index": "k1",
6717 "usable": false,
6718 "cause": "query_references_nonkey_column"
6719 },
6720 {
6721 "index": "k2",
6722 "usable": false,
6723 "cause": "query_references_nonkey_column"
6724 }
6725 ] /* potential_skip_scan_indexes */
6726 } /* skip_scan_range */,
6727 "analyzing_range_alternatives": {
6728 "range_scan_alternatives": [
6729 {
6730 "index": "k1",
6731 "ranges": [
6732 "c1 = '1'"
6733 ] /* ranges */,
6734 "index_dives_for_eq_ranges": true,
6735 "rowid_ordered": true,
6736 "using_mrr": false,
6737 "index_only": false,
6738 "in_memory": -1,
6739 "rows": 1,
6740 "cost": 0.61,
6741 "chosen": true
6742 },
6743 {
6744 "index": "k2",
6745 "ranges": [
6746 "c1 = '1'"
6747 ] /* ranges */,
6748 "index_dives_for_eq_ranges": true,
6749 "rowid_ordered": false,
6750 "using_mrr": false,
6751 "index_only": false,
6752 "in_memory": -1,
6753 "rows": 1,
6754 "cost": 0.61,
6755 "chosen": false,
6756 "cause": "cost"
6757 }
6758 ] /* range_scan_alternatives */,
6759 "analyzing_roworder_intersect": {
6760 "usable": false,
6761 "cause": "too_few_roworder_scans"
6762 } /* analyzing_roworder_intersect */
6763 } /* analyzing_range_alternatives */,
6764 "chosen_range_access_summary": {
6765 "range_access_plan": {
6766 "type": "range_scan",
6767 "index": "k1",
6768 "rows": 1,
6769 "ranges": [
6770 "c1 = '1'"
6771 ] /* ranges */
6772 } /* range_access_plan */,
6773 "rows_for_plan": 1,
6774 "cost_for_plan": 0.61,
6775 "chosen": true
6776 } /* chosen_range_access_summary */
6777 } /* range_analysis */
6778 }
6779 ] /* rows_estimation */
6780 },
6781 {
6782 "considered_execution_plans": [
6783 {
6784 "plan_prefix": [
6785 ] /* plan_prefix */,
6786 "table": "`t1`",
6787 "best_access_path": {
6788 "considered_access_paths": [
6789 {
6790 "access_type": "ref",
6791 "index": "k1",
6792 "rows": 1,
6793 "cost": 0.35,
6794 "chosen": true
6795 },
6796 {
6797 "access_type": "ref",
6798 "index": "k2",
6799 "rows": 1,
6800 "cost": 0.35,
6801 "chosen": false
6802 },
6803 {
6804 "access_type": "range",
6805 "range_details": {
6806 "used_index": "k1"
6807 } /* range_details */,
6808 "chosen": false,
6809 "cause": "heuristic_index_cheaper"
6810 }
6811 ] /* considered_access_paths */
6812 } /* best_access_path */,
6813 "condition_filtering_pct": 100,
6814 "rows_for_plan": 1,
6815 "cost_for_plan": 0.35,
6816 "chosen": true
6817 }
6818 ] /* considered_execution_plans */
6819 },
6820 {
6821 "attaching_conditions_to_tables": {
6822 "original_condition": "(`t1`.`c1` = '1')",
6823 "attached_conditions_computation": [
6824 ] /* attached_conditions_computation */,
6825 "attached_conditions_summary": [
6826 {
6827 "table": "`t1`",
6828 "attached": "(`t1`.`c1` = '1')"
6829 }
6830 ] /* attached_conditions_summary */
6831 } /* attaching_conditions_to_tables */
6832 },
6833 {
6834 "optimizing_distinct_group_by_order_by": {
6835 "simplifying_order_by": {
6836 "original_clause": "`t1`.`i1`",
6837 "items": [
6838 {
6839 "item": "`t1`.`i1`"
6840 }
6841 ] /* items */,
6842 "resulting_clause_is_simple": true,
6843 "resulting_clause": "`t1`.`i1`"
6844 } /* simplifying_order_by */
6845 } /* optimizing_distinct_group_by_order_by */
6846 },
6847 {
6848 "reconsidering_access_paths_for_index_ordering": {
6849 "clause": "ORDER BY",
6850 "steps": [
6851 {
6852 "check_if_range_uses_more_keyparts_than_ref": {
6853 "rerunning_range_optimizer_for_single_index": {
6854 "range_analysis": {
6855 "table_scan": {
6856 "rows": 2,
6857 "cost": 1.79769e+308
6858 } /* table_scan */,
6859 "potential_range_indexes": [
6860 {
6861 "index": "k1",
6862 "usable": false,
6863 "cause": "not_applicable"
6864 },
6865 {
6866 "index": "k2",
6867 "usable": true,
6868 "key_parts": [
6869 "c1",
6870 "i1"
6871 ] /* key_parts */
6872 }
6873 ] /* potential_range_indexes */,
6874 "setup_range_conditions": [
6875 ] /* setup_range_conditions */,
6876 "group_index_range": {
6877 "chosen": false,
6878 "cause": "not_group_by_or_distinct"
6879 } /* group_index_range */,
6880 "skip_scan_range": {
6881 "potential_skip_scan_indexes": [
6882 {
6883 "index": "k2",
6884 "usable": false,
6885 "cause": "query_references_nonkey_column"
6886 }
6887 ] /* potential_skip_scan_indexes */
6888 } /* skip_scan_range */,
6889 "analyzing_range_alternatives": {
6890 "range_scan_alternatives": [
6891 {
6892 "index": "k2",
6893 "ranges": [
6894 "c1 = '1'"
6895 ] /* ranges */,
6896 "index_dives_for_eq_ranges": true,
6897 "rowid_ordered": false,
6898 "using_mrr": false,
6899 "index_only": false,
6900 "in_memory": -1,
6901 "rows": 1,
6902 "cost": 0.61,
6903 "chosen": true
6904 }
6905 ] /* range_scan_alternatives */,
6906 "analyzing_roworder_intersect": {
6907 "usable": false,
6908 "cause": "too_few_roworder_scans"
6909 } /* analyzing_roworder_intersect */
6910 } /* analyzing_range_alternatives */,
6911 "chosen_range_access_summary": {
6912 "range_access_plan": {
6913 "type": "range_scan",
6914 "index": "k2",
6915 "rows": 1,
6916 "ranges": [
6917 "c1 = '1'"
6918 ] /* ranges */
6919 } /* range_access_plan */,
6920 "rows_for_plan": 1,
6921 "cost_for_plan": 0.61,
6922 "chosen": true
6923 } /* chosen_range_access_summary */
6924 } /* range_analysis */,
6925 "access_type_unchanged": {
6926 "ref_key_length": 10,
6927 "range_key_length": 10
6928 } /* access_type_unchanged */
6929 } /* rerunning_range_optimizer_for_single_index */
6930 } /* check_if_range_uses_more_keyparts_than_ref */
6931 }
6932 ] /* steps */,
6933 "index_order_summary": {
6934 "table": "`t1`",
6935 "index_provides_order": true,
6936 "order_direction": "asc",
6937 "index": "k2",
6938 "plan_changed": true,
6939 "access_type": "ref"
6940 } /* index_order_summary */
6941 } /* reconsidering_access_paths_for_index_ordering */
6942 },
6943 {
6944 "finalizing_table_conditions": [
6945 {
6946 "table": "`t1`",
6947 "original_table_condition": "(`t1`.`c1` = '1')",
6948 "final_table_condition ": null
6949 }
6950 ] /* finalizing_table_conditions */
6951 },
6952 {
6953 "refine_plan": [
6954 {
6955 "table": "`t1`"
6956 }
6957 ] /* refine_plan */
6958 },
6959 {
6960 "considering_tmp_tables": [
6961 ] /* considering_tmp_tables */
6962 }
6963 ] /* steps */
6964 } /* join_optimization */
6965 },
6966 {
6967 "join_explain": {
6968 "select#": 1,
6969 "steps": [
6970 ] /* steps */
6971 } /* join_explain */
6972 }
6973 ] /* steps */
6974 } 0 0
6975 #
6976 # Bug#30266767 OPTIMIZER TRACE CONTAINS INVALID JSON FOR FORCE INDEX QUERIES
6977 #
6978 SELECT JSON_TYPE(REGEXP_SUBSTR(TRACE, "1.*e[+]?308")) AS json_type
6979 FROM information_schema.OPTIMIZER_TRACE;
6980 json_type
6981 DOUBLE
6982 DROP TABLE t1;
6983
6984 # BUG#18023222 OPTIMIZER TRACE ERROR ON RANGE ANALYSIS OF
6985 # INDEX ON A BINARY COLUMN
6986
6987 CREATE TABLE t(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
6988 INSERT INTO t VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
6989 INSERT INTO t VALUES (2, NULL);
6990 EXPLAIN SELECT * FROM t WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
6991 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
6992 1 SIMPLE t NULL ref i_b i_b 17 const 1 100.00 Using index condition
6993 Warnings:
6994 Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)
6995 SELECT trace FROM information_schema.optimizer_trace;
6996 trace
6997 {
6998 "steps": [
6999 {
7000 "join_preparation": {
7001 "select#": 1,
7002 "steps": [
7003 {
7004 "expanded_query": "/* select#1 */ select `t`.`i` AS `i`,`t`.`b` AS `b` from `t` where (`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)"
7005 }
7006 ] /* steps */
7007 } /* join_preparation */
7008 },
7009 {
7010 "join_optimization": {
7011 "select#": 1,
7012 "steps": [
7013 {
7014 "condition_processing": {
7015 "condition": "WHERE",
7016 "original_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)",
7017 "steps": [
7018 {
7019 "transformation": "equality_propagation",
7020 "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)"
7021 },
7022 {
7023 "transformation": "constant_propagation",
7024 "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)"
7025 },
7026 {
7027 "transformation": "trivial_condition_removal",
7028 "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)"
7029 }
7030 ] /* steps */
7031 } /* condition_processing */
7032 },
7033 {
7034 "substitute_generated_columns": {
7035 } /* substitute_generated_columns */
7036 },
7037 {
7038 "table_dependencies": [
7039 {
7040 "table": "`t`",
7041 "row_may_be_null": false,
7042 "map_bit": 0,
7043 "depends_on_map_bits": [
7044 ] /* depends_on_map_bits */
7045 }
7046 ] /* table_dependencies */
7047 },
7048 {
7049 "ref_optimizer_key_uses": [
7050 {
7051 "table": "`t`",
7052 "field": "b",
7053 "equals": "0xd95b94336a9946a39cf5b58cfe772d8c",
7054 "null_rejecting": true
7055 }
7056 ] /* ref_optimizer_key_uses */
7057 },
7058 {
7059 "rows_estimation": [
7060 {
7061 "table": "`t`",
7062 "range_analysis": {
7063 "table_scan": {
7064 "rows": 2,
7065 "cost": 2.80256
7066 } /* table_scan */,
7067 "potential_range_indexes": [
7068 {
7069 "index": "PRIMARY",
7070 "usable": false,
7071 "cause": "not_applicable"
7072 },
7073 {
7074 "index": "i_b",
7075 "usable": true,
7076 "key_parts": [
7077 "b"
7078 ] /* key_parts */
7079 }
7080 ] /* potential_range_indexes */,
7081 "setup_range_conditions": [
7082 ] /* setup_range_conditions */,
7083 "group_index_range": {
7084 "chosen": false,
7085 "cause": "not_group_by_or_distinct"
7086 } /* group_index_range */,
7087 "skip_scan_range": {
7088 "potential_skip_scan_indexes": [
7089 {
7090 "index": "i_b",
7091 "usable": false,
7092 "cause": "query_references_nonkey_column"
7093 }
7094 ] /* potential_skip_scan_indexes */
7095 } /* skip_scan_range */,
7096 "analyzing_range_alternatives": {
7097 "range_scan_alternatives": [
7098 {
7099 "index": "i_b",
7100 "ranges": [
7101 "b = 0xd95b94336a9946a39cf5b58cfe772d8c"
7102 ] /* ranges */,
7103 "index_dives_for_eq_ranges": true,
7104 "rowid_ordered": true,
7105 "using_mrr": false,
7106 "index_only": false,
7107 "in_memory": -1,
7108 "rows": 1,
7109 "cost": 0.61,
7110 "chosen": true
7111 }
7112 ] /* range_scan_alternatives */,
7113 "analyzing_roworder_intersect": {
7114 "usable": false,
7115 "cause": "too_few_roworder_scans"
7116 } /* analyzing_roworder_intersect */
7117 } /* analyzing_range_alternatives */,
7118 "chosen_range_access_summary": {
7119 "range_access_plan": {
7120 "type": "range_scan",
7121 "index": "i_b",
7122 "rows": 1,
7123 "ranges": [
7124 "b = 0xd95b94336a9946a39cf5b58cfe772d8c"
7125 ] /* ranges */
7126 } /* range_access_plan */,
7127 "rows_for_plan": 1,
7128 "cost_for_plan": 0.61,
7129 "chosen": true
7130 } /* chosen_range_access_summary */
7131 } /* range_analysis */
7132 }
7133 ] /* rows_estimation */
7134 },
7135 {
7136 "considered_execution_plans": [
7137 {
7138 "plan_prefix": [
7139 ] /* plan_prefix */,
7140 "table": "`t`",
7141 "best_access_path": {
7142 "considered_access_paths": [
7143 {
7144 "access_type": "ref",
7145 "index": "i_b",
7146 "rows": 1,
7147 "cost": 0.35,
7148 "chosen": true
7149 },
7150 {
7151 "access_type": "range",
7152 "range_details": {
7153 "used_index": "i_b"
7154 } /* range_details */,
7155 "chosen": false,
7156 "cause": "heuristic_index_cheaper"
7157 }
7158 ] /* considered_access_paths */
7159 } /* best_access_path */,
7160 "condition_filtering_pct": 100,
7161 "rows_for_plan": 1,
7162 "cost_for_plan": 0.35,
7163 "chosen": true
7164 }
7165 ] /* considered_execution_plans */
7166 },
7167 {
7168 "attaching_conditions_to_tables": {
7169 "original_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)",
7170 "attached_conditions_computation": [
7171 ] /* attached_conditions_computation */,
7172 "attached_conditions_summary": [
7173 {
7174 "table": "`t`",
7175 "attached": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)"
7176 }
7177 ] /* attached_conditions_summary */
7178 } /* attaching_conditions_to_tables */
7179 },
7180 {
7181 "finalizing_table_conditions": [
7182 {
7183 "table": "`t`",
7184 "original_table_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)",
7185 "final_table_condition ": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)"
7186 }
7187 ] /* finalizing_table_conditions */
7188 },
7189 {
7190 "refine_plan": [
7191 {
7192 "table": "`t`",
7193 "pushed_index_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)",
7194 "table_condition_attached": null
7195 }
7196 ] /* refine_plan */
7197 }
7198 ] /* steps */
7199 } /* join_optimization */
7200 },
7201 {
7202 "join_explain": {
7203 "select#": 1,
7204 "steps": [
7205 ] /* steps */
7206 } /* join_explain */
7207 }
7208 ] /* steps */
7209 }
7210 EXPLAIN SELECT * FROM t WHERE b IS NULL;
7211 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
7212 1 SIMPLE t NULL ref i_b i_b 17 const 1 100.00 Using index condition
7213 Warnings:
7214 Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`b` is null)
7215 SELECT trace FROM information_schema.optimizer_trace;
7216 trace
7217 {
7218 "steps": [
7219 {
7220 "join_preparation": {
7221 "select#": 1,
7222 "steps": [
7223 {
7224 "expanded_query": "/* select#1 */ select `t`.`i` AS `i`,`t`.`b` AS `b` from `t` where (`t`.`b` is null)"
7225 }
7226 ] /* steps */
7227 } /* join_preparation */
7228 },
7229 {
7230 "join_optimization": {
7231 "select#": 1,
7232 "steps": [
7233 {
7234 "condition_processing": {
7235 "condition": "WHERE",
7236 "original_condition": "(`t`.`b` is null)",
7237 "steps": [
7238 {
7239 "transformation": "equality_propagation",
7240 "resulting_condition": "(`t`.`b` is null)"
7241 },
7242 {
7243 "transformation": "constant_propagation",
7244 "resulting_condition": "(`t`.`b` is null)"
7245 },
7246 {
7247 "transformation": "trivial_condition_removal",
7248 "resulting_condition": "(`t`.`b` is null)"
7249 }
7250 ] /* steps */
7251 } /* condition_processing */
7252 },
7253 {
7254 "substitute_generated_columns": {
7255 } /* substitute_generated_columns */
7256 },
7257 {
7258 "table_dependencies": [
7259 {
7260 "table": "`t`",
7261 "row_may_be_null": false,
7262 "map_bit": 0,
7263 "depends_on_map_bits": [
7264 ] /* depends_on_map_bits */
7265 }
7266 ] /* table_dependencies */
7267 },
7268 {
7269 "ref_optimizer_key_uses": [
7270 {
7271 "table": "`t`",
7272 "field": "b",
7273 "equals": "NULL",
7274 "null_rejecting": false
7275 }
7276 ] /* ref_optimizer_key_uses */
7277 },
7278 {
7279 "rows_estimation": [
7280 {
7281 "table": "`t`",
7282 "range_analysis": {
7283 "table_scan": {
7284 "rows": 2,
7285 "cost": 2.80256
7286 } /* table_scan */,
7287 "potential_range_indexes": [
7288 {
7289 "index": "PRIMARY",
7290 "usable": false,
7291 "cause": "not_applicable"
7292 },
7293 {
7294 "index": "i_b",
7295 "usable": true,
7296 "key_parts": [
7297 "b"
7298 ] /* key_parts */
7299 }
7300 ] /* potential_range_indexes */,
7301 "setup_range_conditions": [
7302 ] /* setup_range_conditions */,
7303 "group_index_range": {
7304 "chosen": false,
7305 "cause": "not_group_by_or_distinct"
7306 } /* group_index_range */,
7307 "skip_scan_range": {
7308 "potential_skip_scan_indexes": [
7309 {
7310 "index": "i_b",
7311 "usable": false,
7312 "cause": "query_references_nonkey_column"
7313 }
7314 ] /* potential_skip_scan_indexes */
7315 } /* skip_scan_range */,
7316 "analyzing_range_alternatives": {
7317 "range_scan_alternatives": [
7318 {
7319 "index": "i_b",
7320 "ranges": [
7321 "b = NULL"
7322 ] /* ranges */,
7323 "index_dives_for_eq_ranges": true,
7324 "rowid_ordered": true,
7325 "using_mrr": false,
7326 "index_only": false,
7327 "in_memory": -1,
7328 "rows": 1,
7329 "cost": 0.61,
7330 "chosen": true
7331 }
7332 ] /* range_scan_alternatives */,
7333 "analyzing_roworder_intersect": {
7334 "usable": false,
7335 "cause": "too_few_roworder_scans"
7336 } /* analyzing_roworder_intersect */
7337 } /* analyzing_range_alternatives */,
7338 "chosen_range_access_summary": {
7339 "range_access_plan": {
7340 "type": "range_scan",
7341 "index": "i_b",
7342 "rows": 1,
7343 "ranges": [
7344 "b = NULL"
7345 ] /* ranges */
7346 } /* range_access_plan */,
7347 "rows_for_plan": 1,
7348 "cost_for_plan": 0.61,
7349 "chosen": true
7350 } /* chosen_range_access_summary */
7351 } /* range_analysis */
7352 }
7353 ] /* rows_estimation */
7354 },
7355 {
7356 "considered_execution_plans": [
7357 {
7358 "plan_prefix": [
7359 ] /* plan_prefix */,
7360 "table": "`t`",
7361 "best_access_path": {
7362 "considered_access_paths": [
7363 {
7364 "access_type": "ref",
7365 "index": "i_b",
7366 "rows": 1,
7367 "cost": 0.35,
7368 "chosen": true
7369 },
7370 {
7371 "access_type": "range",
7372 "range_details": {
7373 "used_index": "i_b"
7374 } /* range_details */,
7375 "chosen": false,
7376 "cause": "heuristic_index_cheaper"
7377 }
7378 ] /* considered_access_paths */
7379 } /* best_access_path */,
7380 "condition_filtering_pct": 100,
7381 "rows_for_plan": 1,
7382 "cost_for_plan": 0.35,
7383 "chosen": true
7384 }
7385 ] /* considered_execution_plans */
7386 },
7387 {
7388 "attaching_conditions_to_tables": {
7389 "original_condition": "(`t`.`b` is null)",
7390 "attached_conditions_computation": [
7391 ] /* attached_conditions_computation */,
7392 "attached_conditions_summary": [
7393 {
7394 "table": "`t`",
7395 "attached": "(`t`.`b` is null)"
7396 }
7397 ] /* attached_conditions_summary */
7398 } /* attaching_conditions_to_tables */
7399 },
7400 {
7401 "finalizing_table_conditions": [
7402 {
7403 "table": "`t`",
7404 "original_table_condition": "(`t`.`b` is null)",
7405 "final_table_condition ": "(`t`.`b` is null)"
7406 }
7407 ] /* finalizing_table_conditions */
7408 },
7409 {
7410 "refine_plan": [
7411 {
7412 "table": "`t`",
7413 "pushed_index_condition": "(`t`.`b` is null)",
7414 "table_condition_attached": null
7415 }
7416 ] /* refine_plan */
7417 }
7418 ] /* steps */
7419 } /* join_optimization */
7420 },
7421 {
7422 "join_explain": {
7423 "select#": 1,
7424 "steps": [
7425 ] /* steps */
7426 } /* join_explain */
7427 }
7428 ] /* steps */
7429 }
7430 drop table t;
7431 #
7432 # Test trace of geometry fields
7433 #
7434 CREATE TABLE t1(
7435 pk INT AUTO_INCREMENT PRIMARY KEY,
7436 col_geom GEOMETRY NOT NULL SRID 0
7437 ) ENGINE=MyISAM;
7438 INSERT INTO t1 VALUES (1, ST_GeomFromText('POINT(10 10)'));
7439 INSERT INTO t1 VALUES (2, ST_GeomFromText('POINT(11 11)'));
7440 CREATE SPATIAL INDEX idx3 on t1(col_geom);
7441 # Show that geometry data is traced as "unprintable_geometry_value"
7442 EXPLAIN
7443 SELECT pk, ST_AsText(col_geom)
7444 FROM t1
7445 WHERE MBREquals(t1.col_geom, ST_GeomFromText('POINT(12 13)'));
7446 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
7447 1 SIMPLE t1 NULL range idx3 idx3 34 NULL 1 100.00 Using where
7448 Warnings:
7449 Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,st_astext(`test`.`t1`.`col_geom`) AS `ST_AsText(col_geom)` from `test`.`t1` where mbrequals(`test`.`t1`.`col_geom`,<cache>(st_geomfromtext('POINT(12 13)')))
7450 SELECT show_json_object('"range_scan_alternatives": [', TRACE)
7451 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
7452 show_json_object('"range_scan_alternatives": [', TRACE)
7453 "range_scan_alternatives": [
7454 {
7455 "index": "idx3",
7456 "ranges": [
7457 "col_geom unprintable_geometry_value"
7458 ] /* ranges */,
7459 "index_dives_for_eq_ranges": true,
7460 "rowid_ordered": false,
7461 "using_mrr": false,
7462 "index_only": false,
7463 "in_memory": -1,
7464 "rows": 1,
7465 "cost": 0.61,
7466 "chosen": true
7467 }
7468 ]
7469 DROP TABLE t1;
7470 DROP FUNCTION show_json_object;