Debian Codegraph @

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

raw
1 CREATE TABLE t1 (f1 INT, f2 INT, f3 CHAR(1), f4 CHAR(1), f5 CHAR(1), f6 CHAR(1),
2 PRIMARY KEY (f5, f1), KEY (f2), KEY (f3), KEY (f4)) ENGINE=INNODB;
3 INSERT INTO t1 VALUES (1, 1, 'a', 'h', 'i', ''), (2, 3, 'a', 'h', 'i', ''), (3, 2, 'b', '', 'j', ''), (4, 2, 'b', '', 'j', '');
4 INSERT INTO t1 VALUES (5, 1, 'a', 'h', 'i', ''), (6, 3, 'a', 'h', 'i', ''), (7, 2, 'b', '', 'j', ''), (8, 2, 'b', '', 'j', '');
5 INSERT INTO t1 SELECT f1 + 8, f2, f3, f4, f5, f6 FROM t1;
6 INSERT INTO t1 SELECT f1 + 16, f2, f3, f4, f5, f6 FROM t1;
7 INSERT INTO t1 VALUES (33, 3, 'c', 'g', '', ''), (34, 4, 'c', 'g', '', ''), (35, 5, 'd', 'f', '', ''), (36, 6, 'd', 'f', '', '');
8 INSERT INTO t1 SELECT f1 + 36, f2, f3, f4, f5, f6 FROM t1;
9 INSERT INTO t1 SELECT f1 + 72, f2, f3, f4, f5, f6 FROM t1;
10 INSERT INTO t1 SELECT f1 + 144, f2, f3, f4, f5, f6 FROM t1;
11 ANALYZE TABLE t1;
12 Table Op Msg_type Msg_text
13 test.t1 analyze status OK
14 SET optimizer_trace_max_mem_size=1048576;
15 SET end_markers_in_json=on;
16 SET optimizer_trace="enabled=on,one_line=off";
17 SET optimizer_switch='index_merge_union=off,index_merge=off';
18 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3) */ f1 FROM t1 WHERE f2 = 5 OR f3 = 'c' OR f4 = 'f';
19 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
20 1 SIMPLE t1 NULL ALL f2,f3,f4 NULL NULL NULL 288 53.12 Using where
21 Warnings:
22 Note 1003 /* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`) */ `test`.`t1`.`f1` AS `f1` from `test`.`t1` where ((`test`.`t1`.`f2` = 5) or (`test`.`t1`.`f3` = 'c') or (`test`.`t1`.`f4` = 'f'))
23 SELECT * FROM information_schema.OPTIMIZER_TRACE;
24 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
25 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3) */ f1 FROM t1 WHERE f2 = 5 OR f3 = 'c' OR f4 = 'f' {
26 "steps": [
27 {
28 "join_preparation": {
29 "select#": 1,
30 "steps": [
31 {
32 "expanded_query": "/* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`) */ `t1`.`f1` AS `f1` from `t1` where ((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))"
33 }
34 ] /* steps */
35 } /* join_preparation */
36 },
37 {
38 "join_optimization": {
39 "select#": 1,
40 "steps": [
41 {
42 "condition_processing": {
43 "condition": "WHERE",
44 "original_condition": "((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))",
45 "steps": [
46 {
47 "transformation": "equality_propagation",
48 "resulting_condition": "(multiple equal(5, `t1`.`f2`) or multiple equal('c', `t1`.`f3`) or multiple equal('f', `t1`.`f4`))"
49 },
50 {
51 "transformation": "constant_propagation",
52 "resulting_condition": "(multiple equal(5, `t1`.`f2`) or multiple equal('c', `t1`.`f3`) or multiple equal('f', `t1`.`f4`))"
53 },
54 {
55 "transformation": "trivial_condition_removal",
56 "resulting_condition": "(multiple equal(5, `t1`.`f2`) or multiple equal('c', `t1`.`f3`) or multiple equal('f', `t1`.`f4`))"
57 }
58 ] /* steps */
59 } /* condition_processing */
60 },
61 {
62 "substitute_generated_columns": {
63 } /* substitute_generated_columns */
64 },
65 {
66 "table_dependencies": [
67 {
68 "table": "`t1`",
69 "row_may_be_null": false,
70 "map_bit": 0,
71 "depends_on_map_bits": [
72 ] /* depends_on_map_bits */
73 }
74 ] /* table_dependencies */
75 },
76 {
77 "ref_optimizer_key_uses": [
78 ] /* ref_optimizer_key_uses */
79 },
80 {
81 "rows_estimation": [
82 {
83 "table": "`t1`",
84 "range_analysis": {
85 "table_scan": {
86 "rows": 288,
87 "cost": 31.15
88 } /* table_scan */,
89 "potential_range_indexes": [
90 {
91 "index": "PRIMARY",
92 "usable": false,
93 "cause": "not_applicable"
94 },
95 {
96 "index": "f2",
97 "usable": true,
98 "key_parts": [
99 "f2",
100 "f5",
101 "f1"
102 ] /* key_parts */
103 },
104 {
105 "index": "f3",
106 "usable": true,
107 "key_parts": [
108 "f3",
109 "f5",
110 "f1"
111 ] /* key_parts */
112 },
113 {
114 "index": "f4",
115 "usable": true,
116 "key_parts": [
117 "f4",
118 "f5",
119 "f1"
120 ] /* key_parts */
121 }
122 ] /* potential_range_indexes */,
123 "setup_range_conditions": [
124 ] /* setup_range_conditions */,
125 "group_index_range": {
126 "chosen": false,
127 "cause": "not_group_by_or_distinct"
128 } /* group_index_range */,
129 "skip_scan_range": {
130 "potential_skip_scan_indexes": [
131 {
132 "index": "f2",
133 "usable": false,
134 "cause": "query_references_nonkey_column"
135 },
136 {
137 "index": "f3",
138 "usable": false,
139 "cause": "query_references_nonkey_column"
140 },
141 {
142 "index": "f4",
143 "usable": false,
144 "cause": "query_references_nonkey_column"
145 }
146 ] /* potential_skip_scan_indexes */
147 } /* skip_scan_range */,
148 "analyzing_range_alternatives": {
149 "range_scan_alternatives": [
150 ] /* range_scan_alternatives */,
151 "analyzing_roworder_intersect": {
152 "usable": false,
153 "cause": "too_few_roworder_scans"
154 } /* analyzing_roworder_intersect */
155 } /* analyzing_range_alternatives */,
156 "analyzing_index_merge_union": [
157 {
158 "indexes_to_merge": [
159 {
160 "range_scan_alternatives": [
161 {
162 "index": "f2",
163 "ranges": [
164 "f2 = 5"
165 ] /* ranges */,
166 "index_dives_for_eq_ranges": true,
167 "rowid_ordered": true,
168 "using_mrr": false,
169 "index_only": true,
170 "in_memory": 1,
171 "rows": 8,
172 "cost": 1.06277,
173 "chosen": true
174 }
175 ] /* range_scan_alternatives */,
176 "index_to_merge": "f2",
177 "cumulated_cost": 1.06277
178 },
179 {
180 "range_scan_alternatives": [
181 {
182 "index": "f3",
183 "ranges": [
184 "f3 = 'c'"
185 ] /* ranges */,
186 "index_dives_for_eq_ranges": true,
187 "rowid_ordered": true,
188 "using_mrr": false,
189 "index_only": true,
190 "in_memory": 1,
191 "rows": 16,
192 "cost": 1.86594,
193 "chosen": true
194 }
195 ] /* range_scan_alternatives */,
196 "index_to_merge": "f3",
197 "cumulated_cost": 2.92872
198 },
199 {
200 "range_scan_alternatives": [
201 {
202 "index": "f4",
203 "chosen": false,
204 "cause": "index_merge_hint"
205 }
206 ] /* range_scan_alternatives */,
207 "chosen": false,
208 "cause": "cost"
209 }
210 ] /* indexes_to_merge */,
211 "cost_of_reading_ranges": 2.92872,
212 "chosen": false,
213 "cause": "cost"
214 }
215 ] /* analyzing_index_merge_union */
216 } /* range_analysis */
217 }
218 ] /* rows_estimation */
219 },
220 {
221 "considered_execution_plans": [
222 {
223 "plan_prefix": [
224 ] /* plan_prefix */,
225 "table": "`t1`",
226 "best_access_path": {
227 "considered_access_paths": [
228 {
229 "rows_to_scan": 288,
230 "filtering_effect": [
231 ] /* filtering_effect */,
232 "final_filtering_effect": 0.53125,
233 "access_type": "scan",
234 "resulting_rows": 153,
235 "cost": 29.05,
236 "chosen": true
237 }
238 ] /* considered_access_paths */
239 } /* best_access_path */,
240 "condition_filtering_pct": 100,
241 "rows_for_plan": 153,
242 "cost_for_plan": 29.05,
243 "chosen": true
244 }
245 ] /* considered_execution_plans */
246 },
247 {
248 "attaching_conditions_to_tables": {
249 "original_condition": "((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))",
250 "attached_conditions_computation": [
251 ] /* attached_conditions_computation */,
252 "attached_conditions_summary": [
253 {
254 "table": "`t1`",
255 "attached": "((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))"
256 }
257 ] /* attached_conditions_summary */
258 } /* attaching_conditions_to_tables */
259 },
260 {
261 "finalizing_table_conditions": [
262 {
263 "table": "`t1`",
264 "original_table_condition": "((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))",
265 "final_table_condition ": "((`t1`.`f2` = 5) or (`t1`.`f3` = 'c') or (`t1`.`f4` = 'f'))"
266 }
267 ] /* finalizing_table_conditions */
268 },
269 {
270 "refine_plan": [
271 {
272 "table": "`t1`"
273 }
274 ] /* refine_plan */
275 }
276 ] /* steps */
277 } /* join_optimization */
278 },
279 {
280 "join_explain": {
281 "select#": 1,
282 "steps": [
283 ] /* steps */
284 } /* join_explain */
285 }
286 ] /* steps */
287 } 0 0
288 SET optimizer_switch='index_merge=on,index_merge_intersection=on';
289 EXPLAIN SELECT /*+ NO_INDEX_MERGE(t1 f3) */ COUNT(*) FROM t1 WHERE f4 = 'h' AND f2 = 2 AND f3 = 'b';
290 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
291 1 SIMPLE t1 NULL ref f2,f3,f4 f2 5 const 128 19.75 Using where
292 Warnings:
293 Note 1003 /* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`select#1` `f3`) */ count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`f3` = 'b') and (`test`.`t1`.`f2` = 2) and (`test`.`t1`.`f4` = 'h'))
294 SELECT * FROM information_schema.OPTIMIZER_TRACE;
295 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
296 EXPLAIN SELECT /*+ NO_INDEX_MERGE(t1 f3) */ COUNT(*) FROM t1 WHERE f4 = 'h' AND f2 = 2 AND f3 = 'b' {
297 "steps": [
298 {
299 "join_preparation": {
300 "select#": 1,
301 "steps": [
302 {
303 "expanded_query": "/* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`select#1` `f3`) */ count(0) AS `COUNT(*)` from `t1` where ((`t1`.`f4` = 'h') and (`t1`.`f2` = 2) and (`t1`.`f3` = 'b'))"
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`.`f4` = 'h') and (`t1`.`f2` = 2) and (`t1`.`f3` = 'b'))",
316 "steps": [
317 {
318 "transformation": "equality_propagation",
319 "resulting_condition": "(multiple equal('h', `t1`.`f4`) and multiple equal(2, `t1`.`f2`) and multiple equal('b', `t1`.`f3`))"
320 },
321 {
322 "transformation": "constant_propagation",
323 "resulting_condition": "(multiple equal('h', `t1`.`f4`) and multiple equal(2, `t1`.`f2`) and multiple equal('b', `t1`.`f3`))"
324 },
325 {
326 "transformation": "trivial_condition_removal",
327 "resulting_condition": "(multiple equal('h', `t1`.`f4`) and multiple equal(2, `t1`.`f2`) and multiple equal('b', `t1`.`f3`))"
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 {
350 "table": "`t1`",
351 "field": "f2",
352 "equals": "2",
353 "null_rejecting": true
354 },
355 {
356 "table": "`t1`",
357 "field": "f3",
358 "equals": "'b'",
359 "null_rejecting": true
360 },
361 {
362 "table": "`t1`",
363 "field": "f4",
364 "equals": "'h'",
365 "null_rejecting": true
366 }
367 ] /* ref_optimizer_key_uses */
368 },
369 {
370 "rows_estimation": [
371 {
372 "table": "`t1`",
373 "range_analysis": {
374 "table_scan": {
375 "rows": 288,
376 "cost": 31.15
377 } /* table_scan */,
378 "potential_range_indexes": [
379 {
380 "index": "PRIMARY",
381 "usable": false,
382 "cause": "not_applicable"
383 },
384 {
385 "index": "f2",
386 "usable": true,
387 "key_parts": [
388 "f2",
389 "f5",
390 "f1"
391 ] /* key_parts */
392 },
393 {
394 "index": "f3",
395 "usable": true,
396 "key_parts": [
397 "f3",
398 "f5",
399 "f1"
400 ] /* key_parts */
401 },
402 {
403 "index": "f4",
404 "usable": true,
405 "key_parts": [
406 "f4",
407 "f5",
408 "f1"
409 ] /* key_parts */
410 }
411 ] /* potential_range_indexes */,
412 "setup_range_conditions": [
413 ] /* setup_range_conditions */,
414 "group_index_range": {
415 "chosen": false,
416 "cause": "not_group_by_or_distinct"
417 } /* group_index_range */,
418 "skip_scan_range": {
419 "potential_skip_scan_indexes": [
420 {
421 "index": "f2",
422 "usable": false,
423 "cause": "query_references_nonkey_column"
424 },
425 {
426 "index": "f3",
427 "usable": false,
428 "cause": "query_references_nonkey_column"
429 },
430 {
431 "index": "f4",
432 "usable": false,
433 "cause": "query_references_nonkey_column"
434 }
435 ] /* potential_skip_scan_indexes */
436 } /* skip_scan_range */,
437 "analyzing_range_alternatives": {
438 "range_scan_alternatives": [
439 {
440 "index": "f2",
441 "ranges": [
442 "f2 = 2"
443 ] /* ranges */,
444 "index_dives_for_eq_ranges": true,
445 "rowid_ordered": true,
446 "using_mrr": false,
447 "index_only": false,
448 "in_memory": 1,
449 "rows": 128,
450 "cost": 45.06,
451 "chosen": false,
452 "cause": "cost"
453 },
454 {
455 "index": "f3",
456 "chosen": false,
457 "cause": "index_merge_hint"
458 },
459 {
460 "index": "f4",
461 "ranges": [
462 "f4 = 'h'"
463 ] /* ranges */,
464 "index_dives_for_eq_ranges": true,
465 "rowid_ordered": true,
466 "using_mrr": false,
467 "index_only": false,
468 "in_memory": 1,
469 "rows": 128,
470 "cost": 45.06,
471 "chosen": false,
472 "cause": "cost"
473 }
474 ] /* range_scan_alternatives */,
475 "analyzing_roworder_intersect": {
476 "intersecting_indexes": [
477 {
478 "index": "f2",
479 "index_scan_cost": 0.300317,
480 "cumulated_index_scan_cost": 0.300317,
481 "disk_sweep_cost": 1,
482 "cumulated_total_cost": 1.30032,
483 "usable": true,
484 "matching_rows_now": 128,
485 "isect_covering_with_this_index": false,
486 "chosen": true
487 },
488 {
489 "index": "f4",
490 "index_scan_cost": 0.300317,
491 "cumulated_index_scan_cost": 0.600634,
492 "disk_sweep_cost": 1,
493 "cumulated_total_cost": 1.60063,
494 "usable": true,
495 "matching_rows_now": 56.8889,
496 "isect_covering_with_this_index": false,
497 "chosen": false,
498 "cause": "does_not_reduce_cost"
499 }
500 ] /* intersecting_indexes */,
501 "clustered_pk": {
502 "clustered_pk_added_to_intersect": false,
503 "cause": "no_clustered_pk_index"
504 } /* clustered_pk */,
505 "chosen": false,
506 "cause": "too_few_indexes_to_merge"
507 } /* analyzing_roworder_intersect */
508 } /* analyzing_range_alternatives */
509 } /* range_analysis */
510 }
511 ] /* rows_estimation */
512 },
513 {
514 "considered_execution_plans": [
515 {
516 "plan_prefix": [
517 ] /* plan_prefix */,
518 "table": "`t1`",
519 "best_access_path": {
520 "considered_access_paths": [
521 {
522 "access_type": "ref",
523 "index": "f2",
524 "rows": 128,
525 "cost": 13.55,
526 "chosen": true
527 },
528 {
529 "access_type": "ref",
530 "index": "f3",
531 "rows": 128,
532 "cost": 13.55,
533 "chosen": false
534 },
535 {
536 "access_type": "ref",
537 "index": "f4",
538 "rows": 128,
539 "cost": 13.55,
540 "chosen": false
541 },
542 {
543 "rows_to_scan": 288,
544 "filtering_effect": [
545 ] /* filtering_effect */,
546 "final_filtering_effect": 0.0877915,
547 "access_type": "scan",
548 "resulting_rows": 25.284,
549 "cost": 29.05,
550 "chosen": false
551 }
552 ] /* considered_access_paths */
553 } /* best_access_path */,
554 "condition_filtering_pct": 19.7531,
555 "rows_for_plan": 25.284,
556 "cost_for_plan": 13.55,
557 "chosen": true
558 }
559 ] /* considered_execution_plans */
560 },
561 {
562 "attaching_conditions_to_tables": {
563 "original_condition": "((`t1`.`f3` = 'b') and (`t1`.`f2` = 2) and (`t1`.`f4` = 'h'))",
564 "attached_conditions_computation": [
565 ] /* attached_conditions_computation */,
566 "attached_conditions_summary": [
567 {
568 "table": "`t1`",
569 "attached": "((`t1`.`f3` = 'b') and (`t1`.`f2` = 2) and (`t1`.`f4` = 'h'))"
570 }
571 ] /* attached_conditions_summary */
572 } /* attaching_conditions_to_tables */
573 },
574 {
575 "optimizing_distinct_group_by_order_by": {
576 } /* optimizing_distinct_group_by_order_by */
577 },
578 {
579 "finalizing_table_conditions": [
580 {
581 "table": "`t1`",
582 "original_table_condition": "((`t1`.`f3` = 'b') and (`t1`.`f2` = 2) and (`t1`.`f4` = 'h'))",
583 "final_table_condition ": "((`t1`.`f3` = 'b') and (`t1`.`f4` = 'h'))"
584 }
585 ] /* finalizing_table_conditions */
586 },
587 {
588 "refine_plan": [
589 {
590 "table": "`t1`"
591 }
592 ] /* refine_plan */
593 },
594 {
595 "considering_tmp_tables": [
596 ] /* considering_tmp_tables */
597 }
598 ] /* steps */
599 } /* join_optimization */
600 },
601 {
602 "join_explain": {
603 "select#": 1,
604 "steps": [
605 ] /* steps */
606 } /* join_explain */
607 }
608 ] /* steps */
609 } 0 0
610 SET optimizer_switch='index_merge_union=off,index_merge=off';
611 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 300 AND 310 OR f4 = 'b');
612 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
613 1 SIMPLE t1 NULL index_merge f2,f3,f4 f2,f4 5,5 NULL 2 100.00 Using sort_union(f2,f4); Using where
614 Warnings:
615 Note 1003 /* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`, `f4`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t1`.`f4` AS `f4`,`test`.`t1`.`f5` AS `f5`,`test`.`t1`.`f6` AS `f6` from `test`.`t1` where (((`test`.`t1`.`f2` = 5) or (`test`.`t1`.`f3` = 'b')) and ((`test`.`t1`.`f2` between 300 and 310) or (`test`.`t1`.`f4` = 'b')))
616 SELECT * FROM information_schema.OPTIMIZER_TRACE;
617 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
618 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 300 AND 310 OR f4 = 'b') {
619 "steps": [
620 {
621 "join_preparation": {
622 "select#": 1,
623 "steps": [
624 {
625 "expanded_query": "/* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`, `f4`) */ `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`t1`.`f3` AS `f3`,`t1`.`f4` AS `f4`,`t1`.`f5` AS `f5`,`t1`.`f6` AS `f6` from `t1` where (((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))"
626 }
627 ] /* steps */
628 } /* join_preparation */
629 },
630 {
631 "join_optimization": {
632 "select#": 1,
633 "steps": [
634 {
635 "condition_processing": {
636 "condition": "WHERE",
637 "original_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))",
638 "steps": [
639 {
640 "transformation": "equality_propagation",
641 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 300 and 310) or multiple equal('b', `t1`.`f4`)))"
642 },
643 {
644 "transformation": "constant_propagation",
645 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 300 and 310) or multiple equal('b', `t1`.`f4`)))"
646 },
647 {
648 "transformation": "trivial_condition_removal",
649 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 300 and 310) or multiple equal('b', `t1`.`f4`)))"
650 }
651 ] /* steps */
652 } /* condition_processing */
653 },
654 {
655 "substitute_generated_columns": {
656 } /* substitute_generated_columns */
657 },
658 {
659 "table_dependencies": [
660 {
661 "table": "`t1`",
662 "row_may_be_null": false,
663 "map_bit": 0,
664 "depends_on_map_bits": [
665 ] /* depends_on_map_bits */
666 }
667 ] /* table_dependencies */
668 },
669 {
670 "ref_optimizer_key_uses": [
671 ] /* ref_optimizer_key_uses */
672 },
673 {
674 "rows_estimation": [
675 {
676 "table": "`t1`",
677 "range_analysis": {
678 "table_scan": {
679 "rows": 288,
680 "cost": 31.15
681 } /* table_scan */,
682 "potential_range_indexes": [
683 {
684 "index": "PRIMARY",
685 "usable": false,
686 "cause": "not_applicable"
687 },
688 {
689 "index": "f2",
690 "usable": true,
691 "key_parts": [
692 "f2",
693 "f5",
694 "f1"
695 ] /* key_parts */
696 },
697 {
698 "index": "f3",
699 "usable": true,
700 "key_parts": [
701 "f3",
702 "f5",
703 "f1"
704 ] /* key_parts */
705 },
706 {
707 "index": "f4",
708 "usable": true,
709 "key_parts": [
710 "f4",
711 "f5",
712 "f1"
713 ] /* key_parts */
714 }
715 ] /* potential_range_indexes */,
716 "setup_range_conditions": [
717 ] /* setup_range_conditions */,
718 "group_index_range": {
719 "chosen": false,
720 "cause": "not_group_by_or_distinct"
721 } /* group_index_range */,
722 "skip_scan_range": {
723 "potential_skip_scan_indexes": [
724 {
725 "index": "f2",
726 "usable": false,
727 "cause": "query_references_nonkey_column"
728 },
729 {
730 "index": "f3",
731 "usable": false,
732 "cause": "query_references_nonkey_column"
733 },
734 {
735 "index": "f4",
736 "usable": false,
737 "cause": "query_references_nonkey_column"
738 }
739 ] /* potential_skip_scan_indexes */
740 } /* skip_scan_range */,
741 "analyzing_range_alternatives": {
742 "range_scan_alternatives": [
743 ] /* range_scan_alternatives */,
744 "analyzing_roworder_intersect": {
745 "usable": false,
746 "cause": "too_few_roworder_scans"
747 } /* analyzing_roworder_intersect */
748 } /* analyzing_range_alternatives */,
749 "analyzing_index_merge_union": [
750 {
751 "indexes_to_merge": [
752 {
753 "range_scan_alternatives": [
754 {
755 "index": "f2",
756 "ranges": [
757 "f2 = 5"
758 ] /* ranges */,
759 "index_dives_for_eq_ranges": true,
760 "rowid_ordered": true,
761 "using_mrr": false,
762 "index_only": true,
763 "in_memory": 1,
764 "rows": 8,
765 "cost": 1.06277,
766 "chosen": true
767 }
768 ] /* range_scan_alternatives */,
769 "index_to_merge": "f2",
770 "cumulated_cost": 1.06277
771 },
772 {
773 "range_scan_alternatives": [
774 {
775 "index": "f3",
776 "ranges": [
777 "f3 = 'b'"
778 ] /* ranges */,
779 "index_dives_for_eq_ranges": true,
780 "rowid_ordered": true,
781 "using_mrr": false,
782 "index_only": true,
783 "in_memory": 1,
784 "rows": 128,
785 "cost": 13.1103,
786 "chosen": true
787 }
788 ] /* range_scan_alternatives */,
789 "index_to_merge": "f3",
790 "cumulated_cost": 14.1731
791 }
792 ] /* indexes_to_merge */,
793 "cost_of_reading_ranges": 14.1731,
794 "use_roworder_union": true,
795 "cause": "always_cheaper_than_not_roworder_retrieval",
796 "analyzing_roworder_scans": [
797 {
798 "type": "range_scan",
799 "index": "f2",
800 "rows": 8,
801 "ranges": [
802 "f2 = 5"
803 ] /* ranges */,
804 "analyzing_roworder_intersect": {
805 "usable": false,
806 "cause": "too_few_roworder_scans"
807 } /* analyzing_roworder_intersect */
808 },
809 {
810 "type": "range_scan",
811 "index": "f3",
812 "rows": 128,
813 "ranges": [
814 "f3 = 'b'"
815 ] /* ranges */,
816 "analyzing_roworder_intersect": {
817 "usable": false,
818 "cause": "too_few_roworder_scans"
819 } /* analyzing_roworder_intersect */
820 }
821 ] /* analyzing_roworder_scans */,
822 "index_roworder_union_cost": 21.8231,
823 "members": 2,
824 "chosen": true
825 },
826 {
827 "indexes_to_merge": [
828 {
829 "range_scan_alternatives": [
830 {
831 "index": "f2",
832 "ranges": [
833 "300 <= f2 <= 310"
834 ] /* ranges */,
835 "index_dives_for_eq_ranges": true,
836 "rowid_ordered": false,
837 "using_mrr": false,
838 "index_only": true,
839 "in_memory": 1,
840 "rows": 1,
841 "cost": 0.36,
842 "chosen": true
843 }
844 ] /* range_scan_alternatives */,
845 "index_to_merge": "f2",
846 "cumulated_cost": 0.36
847 },
848 {
849 "range_scan_alternatives": [
850 {
851 "index": "f4",
852 "ranges": [
853 "f4 = 'b'"
854 ] /* ranges */,
855 "index_dives_for_eq_ranges": true,
856 "rowid_ordered": true,
857 "using_mrr": false,
858 "index_only": true,
859 "in_memory": 1,
860 "rows": 1,
861 "cost": 0.36,
862 "chosen": true
863 }
864 ] /* range_scan_alternatives */,
865 "index_to_merge": "f4",
866 "cumulated_cost": 0.72
867 }
868 ] /* indexes_to_merge */,
869 "cost_of_reading_ranges": 0.72,
870 "cost_sort_rowid_and_read_disk": 0.4375,
871 "cost_duplicate_removal": 0.254503,
872 "total_cost": 1.412
873 }
874 ] /* analyzing_index_merge_union */,
875 "chosen_range_access_summary": {
876 "range_access_plan": {
877 "type": "index_merge",
878 "index_merge_of": [
879 {
880 "type": "range_scan",
881 "index": "f2",
882 "rows": 1,
883 "ranges": [
884 "300 <= f2 <= 310"
885 ] /* ranges */
886 },
887 {
888 "type": "range_scan",
889 "index": "f4",
890 "rows": 1,
891 "ranges": [
892 "f4 = 'b'"
893 ] /* ranges */
894 }
895 ] /* index_merge_of */
896 } /* range_access_plan */,
897 "rows_for_plan": 2,
898 "cost_for_plan": 1.412,
899 "chosen": true
900 } /* chosen_range_access_summary */
901 } /* range_analysis */
902 }
903 ] /* rows_estimation */
904 },
905 {
906 "considered_execution_plans": [
907 {
908 "plan_prefix": [
909 ] /* plan_prefix */,
910 "table": "`t1`",
911 "best_access_path": {
912 "considered_access_paths": [
913 {
914 "rows_to_scan": 2,
915 "filtering_effect": [
916 ] /* filtering_effect */,
917 "final_filtering_effect": 1,
918 "access_type": "range",
919 "range_details": {
920 "used_index": "sort_union(f2,f4)"
921 } /* range_details */,
922 "resulting_rows": 2,
923 "cost": 1.612,
924 "chosen": true
925 }
926 ] /* considered_access_paths */
927 } /* best_access_path */,
928 "condition_filtering_pct": 100,
929 "rows_for_plan": 2,
930 "cost_for_plan": 1.612,
931 "chosen": true
932 }
933 ] /* considered_execution_plans */
934 },
935 {
936 "attaching_conditions_to_tables": {
937 "original_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))",
938 "attached_conditions_computation": [
939 ] /* attached_conditions_computation */,
940 "attached_conditions_summary": [
941 {
942 "table": "`t1`",
943 "attached": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))"
944 }
945 ] /* attached_conditions_summary */
946 } /* attaching_conditions_to_tables */
947 },
948 {
949 "finalizing_table_conditions": [
950 {
951 "table": "`t1`",
952 "original_table_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))",
953 "final_table_condition ": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 300 and 310) or (`t1`.`f4` = 'b')))"
954 }
955 ] /* finalizing_table_conditions */
956 },
957 {
958 "refine_plan": [
959 {
960 "table": "`t1`"
961 }
962 ] /* refine_plan */
963 }
964 ] /* steps */
965 } /* join_optimization */
966 },
967 {
968 "join_explain": {
969 "select#": 1,
970 "steps": [
971 ] /* steps */
972 } /* join_explain */
973 }
974 ] /* steps */
975 } 0 0
976 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 1 AND 10 OR f4 = 'b');
977 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
978 1 SIMPLE t1 NULL index_merge f2,f3,f4 f2,f3 5,5 NULL 133 100.00 Using union(f2,f3); Using where
979 Warnings:
980 Note 1003 /* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`, `f4`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t1`.`f4` AS `f4`,`test`.`t1`.`f5` AS `f5`,`test`.`t1`.`f6` AS `f6` from `test`.`t1` where (((`test`.`t1`.`f2` = 5) or (`test`.`t1`.`f3` = 'b')) and ((`test`.`t1`.`f2` between 1 and 10) or (`test`.`t1`.`f4` = 'b')))
981 SELECT * FROM information_schema.OPTIMIZER_TRACE;
982 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
983 EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f3, f4) */ * FROM t1 WHERE (f2 = 5 OR f3 = 'b') AND (f2 BETWEEN 1 AND 10 OR f4 = 'b') {
984 "steps": [
985 {
986 "join_preparation": {
987 "select#": 1,
988 "steps": [
989 {
990 "expanded_query": "/* select#1 */ select /*+ INDEX_MERGE(`t1`@`select#1` `f2`, `f3`, `f4`) */ `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`t1`.`f3` AS `f3`,`t1`.`f4` AS `f4`,`t1`.`f5` AS `f5`,`t1`.`f6` AS `f6` from `t1` where (((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))"
991 }
992 ] /* steps */
993 } /* join_preparation */
994 },
995 {
996 "join_optimization": {
997 "select#": 1,
998 "steps": [
999 {
1000 "condition_processing": {
1001 "condition": "WHERE",
1002 "original_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))",
1003 "steps": [
1004 {
1005 "transformation": "equality_propagation",
1006 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 1 and 10) or multiple equal('b', `t1`.`f4`)))"
1007 },
1008 {
1009 "transformation": "constant_propagation",
1010 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 1 and 10) or multiple equal('b', `t1`.`f4`)))"
1011 },
1012 {
1013 "transformation": "trivial_condition_removal",
1014 "resulting_condition": "((multiple equal(5, `t1`.`f2`) or multiple equal('b', `t1`.`f3`)) and ((`t1`.`f2` between 1 and 10) or multiple equal('b', `t1`.`f4`)))"
1015 }
1016 ] /* steps */
1017 } /* condition_processing */
1018 },
1019 {
1020 "substitute_generated_columns": {
1021 } /* substitute_generated_columns */
1022 },
1023 {
1024 "table_dependencies": [
1025 {
1026 "table": "`t1`",
1027 "row_may_be_null": false,
1028 "map_bit": 0,
1029 "depends_on_map_bits": [
1030 ] /* depends_on_map_bits */
1031 }
1032 ] /* table_dependencies */
1033 },
1034 {
1035 "ref_optimizer_key_uses": [
1036 ] /* ref_optimizer_key_uses */
1037 },
1038 {
1039 "rows_estimation": [
1040 {
1041 "table": "`t1`",
1042 "range_analysis": {
1043 "table_scan": {
1044 "rows": 288,
1045 "cost": 31.15
1046 } /* table_scan */,
1047 "potential_range_indexes": [
1048 {
1049 "index": "PRIMARY",
1050 "usable": false,
1051 "cause": "not_applicable"
1052 },
1053 {
1054 "index": "f2",
1055 "usable": true,
1056 "key_parts": [
1057 "f2",
1058 "f5",
1059 "f1"
1060 ] /* key_parts */
1061 },
1062 {
1063 "index": "f3",
1064 "usable": true,
1065 "key_parts": [
1066 "f3",
1067 "f5",
1068 "f1"
1069 ] /* key_parts */
1070 },
1071 {
1072 "index": "f4",
1073 "usable": true,
1074 "key_parts": [
1075 "f4",
1076 "f5",
1077 "f1"
1078 ] /* key_parts */
1079 }
1080 ] /* potential_range_indexes */,
1081 "setup_range_conditions": [
1082 ] /* setup_range_conditions */,
1083 "group_index_range": {
1084 "chosen": false,
1085 "cause": "not_group_by_or_distinct"
1086 } /* group_index_range */,
1087 "skip_scan_range": {
1088 "potential_skip_scan_indexes": [
1089 {
1090 "index": "f2",
1091 "usable": false,
1092 "cause": "query_references_nonkey_column"
1093 },
1094 {
1095 "index": "f3",
1096 "usable": false,
1097 "cause": "query_references_nonkey_column"
1098 },
1099 {
1100 "index": "f4",
1101 "usable": false,
1102 "cause": "query_references_nonkey_column"
1103 }
1104 ] /* potential_skip_scan_indexes */
1105 } /* skip_scan_range */,
1106 "analyzing_range_alternatives": {
1107 "range_scan_alternatives": [
1108 ] /* range_scan_alternatives */,
1109 "analyzing_roworder_intersect": {
1110 "usable": false,
1111 "cause": "too_few_roworder_scans"
1112 } /* analyzing_roworder_intersect */
1113 } /* analyzing_range_alternatives */,
1114 "analyzing_index_merge_union": [
1115 {
1116 "indexes_to_merge": [
1117 {
1118 "range_scan_alternatives": [
1119 {
1120 "index": "f2",
1121 "ranges": [
1122 "f2 = 5"
1123 ] /* ranges */,
1124 "index_dives_for_eq_ranges": true,
1125 "rowid_ordered": true,
1126 "using_mrr": false,
1127 "index_only": true,
1128 "in_memory": 1,
1129 "rows": 8,
1130 "cost": 1.06277,
1131 "chosen": true
1132 }
1133 ] /* range_scan_alternatives */,
1134 "index_to_merge": "f2",
1135 "cumulated_cost": 1.06277
1136 },
1137 {
1138 "range_scan_alternatives": [
1139 {
1140 "index": "f3",
1141 "ranges": [
1142 "f3 = 'b'"
1143 ] /* ranges */,
1144 "index_dives_for_eq_ranges": true,
1145 "rowid_ordered": true,
1146 "using_mrr": false,
1147 "index_only": true,
1148 "in_memory": 1,
1149 "rows": 128,
1150 "cost": 13.1103,
1151 "chosen": true
1152 }
1153 ] /* range_scan_alternatives */,
1154 "index_to_merge": "f3",
1155 "cumulated_cost": 14.1731
1156 }
1157 ] /* indexes_to_merge */,
1158 "cost_of_reading_ranges": 14.1731,
1159 "use_roworder_union": true,
1160 "cause": "always_cheaper_than_not_roworder_retrieval",
1161 "analyzing_roworder_scans": [
1162 {
1163 "type": "range_scan",
1164 "index": "f2",
1165 "rows": 8,
1166 "ranges": [
1167 "f2 = 5"
1168 ] /* ranges */,
1169 "analyzing_roworder_intersect": {
1170 "usable": false,
1171 "cause": "too_few_roworder_scans"
1172 } /* analyzing_roworder_intersect */
1173 },
1174 {
1175 "type": "range_scan",
1176 "index": "f3",
1177 "rows": 128,
1178 "ranges": [
1179 "f3 = 'b'"
1180 ] /* ranges */,
1181 "analyzing_roworder_intersect": {
1182 "usable": false,
1183 "cause": "too_few_roworder_scans"
1184 } /* analyzing_roworder_intersect */
1185 }
1186 ] /* analyzing_roworder_scans */,
1187 "index_roworder_union_cost": 21.8231,
1188 "members": 2,
1189 "chosen": true
1190 },
1191 {
1192 "indexes_to_merge": [
1193 {
1194 "range_scan_alternatives": [
1195 {
1196 "index": "f2",
1197 "ranges": [
1198 "1 <= f2 <= 10"
1199 ] /* ranges */,
1200 "index_dives_for_eq_ranges": true,
1201 "rowid_ordered": false,
1202 "using_mrr": false,
1203 "index_only": true,
1204 "in_memory": 1,
1205 "rows": 288,
1206 "cost": 29.1737,
1207 "chosen": true
1208 }
1209 ] /* range_scan_alternatives */,
1210 "index_to_merge": "f2",
1211 "cumulated_cost": 29.1737
1212 },
1213 {
1214 "range_scan_alternatives": [
1215 {
1216 "index": "f4",
1217 "ranges": [
1218 "f4 = 'b'"
1219 ] /* ranges */,
1220 "index_dives_for_eq_ranges": true,
1221 "rowid_ordered": true,
1222 "using_mrr": false,
1223 "index_only": true,
1224 "in_memory": 1,
1225 "rows": 1,
1226 "cost": 0.36,
1227 "chosen": true
1228 }
1229 ] /* range_scan_alternatives */,
1230 "index_to_merge": "f4",
1231 "cumulated_cost": 29.5337
1232 }
1233 ] /* indexes_to_merge */,
1234 "cost_of_reading_ranges": 29.5337,
1235 "cost_sort_rowid_and_read_disk": 1,
1236 "cost_duplicate_removal": 195.921,
1237 "total_cost": 226.454
1238 }
1239 ] /* analyzing_index_merge_union */,
1240 "chosen_range_access_summary": {
1241 "range_access_plan": {
1242 "type": "index_roworder_union",
1243 "union_of": [
1244 {
1245 "type": "range_scan",
1246 "index": "f2",
1247 "rows": 8,
1248 "ranges": [
1249 "f2 = 5"
1250 ] /* ranges */
1251 },
1252 {
1253 "type": "range_scan",
1254 "index": "f3",
1255 "rows": 128,
1256 "ranges": [
1257 "f3 = 'b'"
1258 ] /* ranges */
1259 }
1260 ] /* union_of */
1261 } /* range_access_plan */,
1262 "rows_for_plan": 133,
1263 "cost_for_plan": 21.8231,
1264 "chosen": true
1265 } /* chosen_range_access_summary */
1266 } /* range_analysis */
1267 }
1268 ] /* rows_estimation */
1269 },
1270 {
1271 "considered_execution_plans": [
1272 {
1273 "plan_prefix": [
1274 ] /* plan_prefix */,
1275 "table": "`t1`",
1276 "best_access_path": {
1277 "considered_access_paths": [
1278 {
1279 "rows_to_scan": 133,
1280 "filtering_effect": [
1281 ] /* filtering_effect */,
1282 "final_filtering_effect": 1,
1283 "access_type": "range",
1284 "range_details": {
1285 "used_index": "union(f2,f3)"
1286 } /* range_details */,
1287 "resulting_rows": 133,
1288 "cost": 35.1231,
1289 "chosen": true
1290 }
1291 ] /* considered_access_paths */
1292 } /* best_access_path */,
1293 "condition_filtering_pct": 100,
1294 "rows_for_plan": 133,
1295 "cost_for_plan": 35.1231,
1296 "chosen": true
1297 }
1298 ] /* considered_execution_plans */
1299 },
1300 {
1301 "attaching_conditions_to_tables": {
1302 "original_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))",
1303 "attached_conditions_computation": [
1304 ] /* attached_conditions_computation */,
1305 "attached_conditions_summary": [
1306 {
1307 "table": "`t1`",
1308 "attached": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))"
1309 }
1310 ] /* attached_conditions_summary */
1311 } /* attaching_conditions_to_tables */
1312 },
1313 {
1314 "finalizing_table_conditions": [
1315 {
1316 "table": "`t1`",
1317 "original_table_condition": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))",
1318 "final_table_condition ": "(((`t1`.`f2` = 5) or (`t1`.`f3` = 'b')) and ((`t1`.`f2` between 1 and 10) or (`t1`.`f4` = 'b')))"
1319 }
1320 ] /* finalizing_table_conditions */
1321 },
1322 {
1323 "refine_plan": [
1324 {
1325 "table": "`t1`"
1326 }
1327 ] /* refine_plan */
1328 }
1329 ] /* steps */
1330 } /* join_optimization */
1331 },
1332 {
1333 "join_explain": {
1334 "select#": 1,
1335 "steps": [
1336 ] /* steps */
1337 } /* join_explain */
1338 }
1339 ] /* steps */
1340 } 0 0
1341 SET optimizer_switch=default;
1342 DROP TABLE t1;