Debian Codegraph @

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

raw
1 SET optimizer_trace_max_mem_size=1048576;
2 SET optimizer_trace="enabled=on,one_line=off";
3 SET end_markers_in_json="on";
4 SET eq_range_index_dive_limit=default;
5 SELECT @@eq_range_index_dive_limit;
6 @@eq_range_index_dive_limit
7 200
8 CREATE TABLE t1 (
9 a INT,
10 b INT,
11 KEY (a,b)
12 );
13 INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
14 INSERT INTO t1 VALUES (4,1), (4,2), (4,3);
15 INSERT INTO t1 VALUES (5,1), (5,2), (5,3);
16 SHOW INDEX FROM t1;
17 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
18 t1 1 a 1 a A # NULL NULL YES BTREE YES NULL
19 t1 1 a 2 b A # NULL NULL YES BTREE YES NULL
20 ANALYZE TABLE t1;
21 Table Op Msg_type Msg_text
22 test.t1 analyze status OK
23 SHOW INDEX FROM t1;
24 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
25 t1 1 a 1 a A # NULL NULL YES BTREE YES NULL
26 t1 1 a 2 b A # NULL NULL YES BTREE YES NULL
27 #####
28 # Apply knowledge about the statistics (each index value for
29 # the first key part has an estimate of 2 rows) to ensure that
30 # index statistics kicks in correctly.
31 #####
32 # Index dives are done, giving correct estimate of 3 records
33 EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
34 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
35 1 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index
36 Warnings:
37 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,2,3))
38 SET eq_range_index_dive_limit=3;
39 SELECT @@eq_range_index_dive_limit;
40 @@eq_range_index_dive_limit
41 3
42 SET SESSION DEBUG="+d,crash_records_in_range";
43 # Index statistics kicks in, giving incorrect estimate of 3x2=6 records
44 EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index
47 Warnings:
48 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,2,3))
49 SELECT * FROM t1 WHERE a IN (1,2,3);
50 a b
51 1 1
52 2 2
53 3 3
54 SET SESSION DEBUG="-d,crash_records_in_range";
55 #####
56 # Below: A number of tests to verify that the number of equality ranges
57 # are counted correctly
58 #####
59
60 # 2 equality ranges: should not use index statistics
61 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1);
62 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
63 1 SIMPLE t1 NULL range a a 5 NULL 5 100.00 Using where; Using index
64 Warnings:
65 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) or (`test`.`t1`.`a` > 10) or (`test`.`t1`.`a` = 1))
66 SELECT * FROM information_schema.OPTIMIZER_TRACE;
67 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
68 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1) {
69 "steps": [
70 {
71 "join_preparation": {
72 "select#": 1,
73 "steps": [
74 {
75 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))"
76 }
77 ] /* steps */
78 } /* join_preparation */
79 },
80 {
81 "join_optimization": {
82 "select#": 1,
83 "steps": [
84 {
85 "condition_processing": {
86 "condition": "WHERE",
87 "original_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))",
88 "steps": [
89 {
90 "transformation": "equality_propagation",
91 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or multiple equal(1, `t1`.`a`))"
92 },
93 {
94 "transformation": "constant_propagation",
95 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or multiple equal(1, `t1`.`a`))"
96 },
97 {
98 "transformation": "trivial_condition_removal",
99 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or multiple equal(1, `t1`.`a`))"
100 }
101 ] /* steps */
102 } /* condition_processing */
103 },
104 {
105 "substitute_generated_columns": {
106 } /* substitute_generated_columns */
107 },
108 {
109 "table_dependencies": [
110 {
111 "table": "`t1`",
112 "row_may_be_null": false,
113 "map_bit": 0,
114 "depends_on_map_bits": [
115 ] /* depends_on_map_bits */
116 }
117 ] /* table_dependencies */
118 },
119 {
120 "ref_optimizer_key_uses": [
121 ] /* ref_optimizer_key_uses */
122 },
123 {
124 "rows_estimation": [
125 {
126 "table": "`t1`",
127 "range_analysis": {
128 "table_scan": {
129 "rows": 9,
130 "cost": 3.25
131 } /* table_scan */,
132 "potential_range_indexes": [
133 {
134 "index": "a",
135 "usable": true,
136 "key_parts": [
137 "a",
138 "b"
139 ] /* key_parts */
140 }
141 ] /* potential_range_indexes */,
142 "best_covering_index_scan": {
143 "index": "a",
144 "cost": 1.1539,
145 "chosen": true
146 } /* best_covering_index_scan */,
147 "setup_range_conditions": [
148 ] /* setup_range_conditions */,
149 "group_index_range": {
150 "chosen": false,
151 "cause": "not_group_by_or_distinct"
152 } /* group_index_range */,
153 "skip_scan_range": {
154 "potential_skip_scan_indexes": [
155 {
156 "index": "a",
157 "usable": false,
158 "cause": "prefix_not_const_equality"
159 }
160 ] /* potential_skip_scan_indexes */
161 } /* skip_scan_range */,
162 "analyzing_range_alternatives": {
163 "range_scan_alternatives": [
164 {
165 "index": "a",
166 "ranges": [
167 "a = 1",
168 "a = 5",
169 "10 < a"
170 ] /* ranges */,
171 "index_dives_for_eq_ranges": true,
172 "rowid_ordered": false,
173 "using_mrr": false,
174 "index_only": true,
175 "in_memory": 1,
176 "rows": 5,
177 "cost": 0.761949,
178 "chosen": true
179 }
180 ] /* range_scan_alternatives */,
181 "analyzing_roworder_intersect": {
182 "usable": false,
183 "cause": "too_few_roworder_scans"
184 } /* analyzing_roworder_intersect */
185 } /* analyzing_range_alternatives */,
186 "chosen_range_access_summary": {
187 "range_access_plan": {
188 "type": "range_scan",
189 "index": "a",
190 "rows": 5,
191 "ranges": [
192 "a = 1",
193 "a = 5",
194 "10 < a"
195 ] /* ranges */
196 } /* range_access_plan */,
197 "rows_for_plan": 5,
198 "cost_for_plan": 0.761949,
199 "chosen": true
200 } /* chosen_range_access_summary */
201 } /* range_analysis */
202 }
203 ] /* rows_estimation */
204 },
205 {
206 "considered_execution_plans": [
207 {
208 "plan_prefix": [
209 ] /* plan_prefix */,
210 "table": "`t1`",
211 "best_access_path": {
212 "considered_access_paths": [
213 {
214 "rows_to_scan": 5,
215 "filtering_effect": [
216 ] /* filtering_effect */,
217 "final_filtering_effect": 1,
218 "access_type": "range",
219 "range_details": {
220 "used_index": "a"
221 } /* range_details */,
222 "resulting_rows": 5,
223 "cost": 1.26195,
224 "chosen": true
225 }
226 ] /* considered_access_paths */
227 } /* best_access_path */,
228 "condition_filtering_pct": 100,
229 "rows_for_plan": 5,
230 "cost_for_plan": 1.26195,
231 "chosen": true
232 }
233 ] /* considered_execution_plans */
234 },
235 {
236 "attaching_conditions_to_tables": {
237 "original_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))",
238 "attached_conditions_computation": [
239 ] /* attached_conditions_computation */,
240 "attached_conditions_summary": [
241 {
242 "table": "`t1`",
243 "attached": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))"
244 }
245 ] /* attached_conditions_summary */
246 } /* attaching_conditions_to_tables */
247 },
248 {
249 "finalizing_table_conditions": [
250 {
251 "table": "`t1`",
252 "original_table_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))",
253 "final_table_condition ": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` = 1))"
254 }
255 ] /* finalizing_table_conditions */
256 },
257 {
258 "refine_plan": [
259 {
260 "table": "`t1`"
261 }
262 ] /* refine_plan */
263 }
264 ] /* steps */
265 } /* join_optimization */
266 },
267 {
268 "join_explain": {
269 "select#": 1,
270 "steps": [
271 ] /* steps */
272 } /* join_explain */
273 }
274 ] /* steps */
275 } 0 0
276
277 # 3 equality ranges: should use index statistics
278 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1,2);
279 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
280 1 SIMPLE t1 NULL range a a 5 NULL 4 100.00 Using where; Using index
281 Warnings:
282 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) or (`test`.`t1`.`a` > 10) or (`test`.`t1`.`a` in (1,2)))
283 SELECT * FROM information_schema.OPTIMIZER_TRACE;
284 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
285 EXPLAIN SELECT * FROM t1 WHERE a=5 OR a>10 OR a IN (1,2) {
286 "steps": [
287 {
288 "join_preparation": {
289 "select#": 1,
290 "steps": [
291 {
292 "IN_uses_bisection": true
293 },
294 {
295 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
296 }
297 ] /* steps */
298 } /* join_preparation */
299 },
300 {
301 "join_optimization": {
302 "select#": 1,
303 "steps": [
304 {
305 "condition_processing": {
306 "condition": "WHERE",
307 "original_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))",
308 "steps": [
309 {
310 "transformation": "equality_propagation",
311 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
312 },
313 {
314 "transformation": "constant_propagation",
315 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
316 },
317 {
318 "transformation": "trivial_condition_removal",
319 "resulting_condition": "(multiple equal(5, `t1`.`a`) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
320 }
321 ] /* steps */
322 } /* condition_processing */
323 },
324 {
325 "substitute_generated_columns": {
326 } /* substitute_generated_columns */
327 },
328 {
329 "table_dependencies": [
330 {
331 "table": "`t1`",
332 "row_may_be_null": false,
333 "map_bit": 0,
334 "depends_on_map_bits": [
335 ] /* depends_on_map_bits */
336 }
337 ] /* table_dependencies */
338 },
339 {
340 "ref_optimizer_key_uses": [
341 ] /* ref_optimizer_key_uses */
342 },
343 {
344 "rows_estimation": [
345 {
346 "table": "`t1`",
347 "range_analysis": {
348 "table_scan": {
349 "rows": 9,
350 "cost": 3.25
351 } /* table_scan */,
352 "potential_range_indexes": [
353 {
354 "index": "a",
355 "usable": true,
356 "key_parts": [
357 "a",
358 "b"
359 ] /* key_parts */
360 }
361 ] /* potential_range_indexes */,
362 "best_covering_index_scan": {
363 "index": "a",
364 "cost": 1.1539,
365 "chosen": true
366 } /* best_covering_index_scan */,
367 "setup_range_conditions": [
368 ] /* setup_range_conditions */,
369 "group_index_range": {
370 "chosen": false,
371 "cause": "not_group_by_or_distinct"
372 } /* group_index_range */,
373 "skip_scan_range": {
374 "potential_skip_scan_indexes": [
375 {
376 "index": "a",
377 "usable": false,
378 "cause": "prefix_not_const_equality"
379 }
380 ] /* potential_skip_scan_indexes */
381 } /* skip_scan_range */,
382 "analyzing_range_alternatives": {
383 "range_scan_alternatives": [
384 {
385 "index": "a",
386 "ranges": [
387 "a = 1",
388 "a = 2",
389 "a = 5",
390 "10 < a"
391 ] /* ranges */,
392 "index_dives_for_eq_ranges": false,
393 "rowid_ordered": false,
394 "using_mrr": false,
395 "index_only": true,
396 "in_memory": 1,
397 "rows": 4,
398 "cost": 0.661462,
399 "chosen": true
400 }
401 ] /* range_scan_alternatives */,
402 "analyzing_roworder_intersect": {
403 "usable": false,
404 "cause": "too_few_roworder_scans"
405 } /* analyzing_roworder_intersect */
406 } /* analyzing_range_alternatives */,
407 "chosen_range_access_summary": {
408 "range_access_plan": {
409 "type": "range_scan",
410 "index": "a",
411 "rows": 4,
412 "ranges": [
413 "a = 1",
414 "a = 2",
415 "a = 5",
416 "10 < a"
417 ] /* ranges */
418 } /* range_access_plan */,
419 "rows_for_plan": 4,
420 "cost_for_plan": 0.661462,
421 "chosen": true
422 } /* chosen_range_access_summary */
423 } /* range_analysis */
424 }
425 ] /* rows_estimation */
426 },
427 {
428 "considered_execution_plans": [
429 {
430 "plan_prefix": [
431 ] /* plan_prefix */,
432 "table": "`t1`",
433 "best_access_path": {
434 "considered_access_paths": [
435 {
436 "rows_to_scan": 4,
437 "filtering_effect": [
438 ] /* filtering_effect */,
439 "final_filtering_effect": 1,
440 "access_type": "range",
441 "range_details": {
442 "used_index": "a"
443 } /* range_details */,
444 "resulting_rows": 4,
445 "cost": 1.06146,
446 "chosen": true
447 }
448 ] /* considered_access_paths */
449 } /* best_access_path */,
450 "condition_filtering_pct": 100,
451 "rows_for_plan": 4,
452 "cost_for_plan": 1.06146,
453 "chosen": true
454 }
455 ] /* considered_execution_plans */
456 },
457 {
458 "attaching_conditions_to_tables": {
459 "original_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))",
460 "attached_conditions_computation": [
461 ] /* attached_conditions_computation */,
462 "attached_conditions_summary": [
463 {
464 "table": "`t1`",
465 "attached": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
466 }
467 ] /* attached_conditions_summary */
468 } /* attaching_conditions_to_tables */
469 },
470 {
471 "finalizing_table_conditions": [
472 {
473 "table": "`t1`",
474 "original_table_condition": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))",
475 "final_table_condition ": "((`t1`.`a` = 5) or (`t1`.`a` > 10) or (`t1`.`a` in (1,2)))"
476 }
477 ] /* finalizing_table_conditions */
478 },
479 {
480 "refine_plan": [
481 {
482 "table": "`t1`"
483 }
484 ] /* refine_plan */
485 }
486 ] /* steps */
487 } /* join_optimization */
488 },
489 {
490 "join_explain": {
491 "select#": 1,
492 "steps": [
493 ] /* steps */
494 } /* join_explain */
495 }
496 ] /* steps */
497 } 0 0
498
499 # 3 equality ranges: should use index statistics
500 SET SESSION DEBUG="+d,crash_records_in_range";
501 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4);
502 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
503 1 SIMPLE t1 NULL range a a 10 NULL 3 100.00 Using where; Using index
504 Warnings:
505 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`b` = 3) or (`test`.`t1`.`b` = 4)))
506 SELECT * FROM information_schema.OPTIMIZER_TRACE;
507 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
508 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4) {
509 "steps": [
510 {
511 "join_preparation": {
512 "select#": 1,
513 "steps": [
514 {
515 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
516 }
517 ] /* steps */
518 } /* join_preparation */
519 },
520 {
521 "join_optimization": {
522 "select#": 1,
523 "steps": [
524 {
525 "condition_processing": {
526 "condition": "WHERE",
527 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
528 "steps": [
529 {
530 "transformation": "equality_propagation",
531 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)) and multiple equal(5, `t1`.`a`))"
532 },
533 {
534 "transformation": "constant_propagation",
535 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)) and multiple equal(5, `t1`.`a`))"
536 },
537 {
538 "transformation": "trivial_condition_removal",
539 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)) and multiple equal(5, `t1`.`a`))"
540 }
541 ] /* steps */
542 } /* condition_processing */
543 },
544 {
545 "substitute_generated_columns": {
546 } /* substitute_generated_columns */
547 },
548 {
549 "table_dependencies": [
550 {
551 "table": "`t1`",
552 "row_may_be_null": false,
553 "map_bit": 0,
554 "depends_on_map_bits": [
555 ] /* depends_on_map_bits */
556 }
557 ] /* table_dependencies */
558 },
559 {
560 "ref_optimizer_key_uses": [
561 {
562 "table": "`t1`",
563 "field": "a",
564 "equals": "5",
565 "null_rejecting": true
566 }
567 ] /* ref_optimizer_key_uses */
568 },
569 {
570 "rows_estimation": [
571 {
572 "table": "`t1`",
573 "range_analysis": {
574 "table_scan": {
575 "rows": 9,
576 "cost": 3.25
577 } /* table_scan */,
578 "potential_range_indexes": [
579 {
580 "index": "a",
581 "usable": true,
582 "key_parts": [
583 "a",
584 "b"
585 ] /* key_parts */
586 }
587 ] /* potential_range_indexes */,
588 "best_covering_index_scan": {
589 "index": "a",
590 "cost": 1.1539,
591 "chosen": true
592 } /* best_covering_index_scan */,
593 "setup_range_conditions": [
594 ] /* setup_range_conditions */,
595 "group_index_range": {
596 "chosen": false,
597 "cause": "not_group_by_or_distinct"
598 } /* group_index_range */,
599 "skip_scan_range": {
600 "potential_skip_scan_indexes": [
601 {
602 "index": "a",
603 "usable": false,
604 "cause": "no_range_predicate"
605 }
606 ] /* potential_skip_scan_indexes */
607 } /* skip_scan_range */,
608 "analyzing_range_alternatives": {
609 "range_scan_alternatives": [
610 {
611 "index": "a",
612 "ranges": [
613 "a = 5 AND b = 2",
614 "a = 5 AND b = 3",
615 "a = 5 AND b = 4"
616 ] /* ranges */,
617 "index_dives_for_eq_ranges": false,
618 "rowid_ordered": false,
619 "using_mrr": false,
620 "index_only": true,
621 "in_memory": 1,
622 "rows": 3,
623 "cost": 0.560975,
624 "chosen": true
625 }
626 ] /* range_scan_alternatives */,
627 "analyzing_roworder_intersect": {
628 "usable": false,
629 "cause": "too_few_roworder_scans"
630 } /* analyzing_roworder_intersect */
631 } /* analyzing_range_alternatives */,
632 "chosen_range_access_summary": {
633 "range_access_plan": {
634 "type": "range_scan",
635 "index": "a",
636 "rows": 3,
637 "ranges": [
638 "a = 5 AND b = 2",
639 "a = 5 AND b = 3",
640 "a = 5 AND b = 4"
641 ] /* ranges */
642 } /* range_access_plan */,
643 "rows_for_plan": 3,
644 "cost_for_plan": 0.560975,
645 "chosen": true
646 } /* chosen_range_access_summary */
647 } /* range_analysis */
648 }
649 ] /* rows_estimation */
650 },
651 {
652 "considered_execution_plans": [
653 {
654 "plan_prefix": [
655 ] /* plan_prefix */,
656 "table": "`t1`",
657 "best_access_path": {
658 "considered_access_paths": [
659 {
660 "access_type": "ref",
661 "index": "a",
662 "chosen": false,
663 "cause": "range_uses_more_keyparts"
664 },
665 {
666 "rows_to_scan": 3,
667 "filtering_effect": [
668 ] /* filtering_effect */,
669 "final_filtering_effect": 1,
670 "access_type": "range",
671 "range_details": {
672 "used_index": "a"
673 } /* range_details */,
674 "resulting_rows": 3,
675 "cost": 0.860975,
676 "chosen": true
677 }
678 ] /* considered_access_paths */
679 } /* best_access_path */,
680 "condition_filtering_pct": 100,
681 "rows_for_plan": 3,
682 "cost_for_plan": 0.860975,
683 "chosen": true
684 }
685 ] /* considered_execution_plans */
686 },
687 {
688 "attaching_conditions_to_tables": {
689 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
690 "attached_conditions_computation": [
691 ] /* attached_conditions_computation */,
692 "attached_conditions_summary": [
693 {
694 "table": "`t1`",
695 "attached": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
696 }
697 ] /* attached_conditions_summary */
698 } /* attaching_conditions_to_tables */
699 },
700 {
701 "finalizing_table_conditions": [
702 {
703 "table": "`t1`",
704 "original_table_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
705 "final_table_condition ": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
706 }
707 ] /* finalizing_table_conditions */
708 },
709 {
710 "refine_plan": [
711 {
712 "table": "`t1`"
713 }
714 ] /* refine_plan */
715 }
716 ] /* steps */
717 } /* join_optimization */
718 },
719 {
720 "join_explain": {
721 "select#": 1,
722 "steps": [
723 ] /* steps */
724 } /* join_explain */
725 }
726 ] /* steps */
727 } 0 0
728 SET SESSION DEBUG="-d,crash_records_in_range";
729
730 # 2 equality ranges: should not use index statistics
731 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b>4);
732 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
733 1 SIMPLE t1 NULL range a a 10 NULL 3 100.00 Using where; Using index
734 Warnings:
735 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`b` = 3) or (`test`.`t1`.`b` > 4)))
736 SELECT * FROM information_schema.OPTIMIZER_TRACE;
737 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
738 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b>4) {
739 "steps": [
740 {
741 "join_preparation": {
742 "select#": 1,
743 "steps": [
744 {
745 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))"
746 }
747 ] /* steps */
748 } /* join_preparation */
749 },
750 {
751 "join_optimization": {
752 "select#": 1,
753 "steps": [
754 {
755 "condition_processing": {
756 "condition": "WHERE",
757 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))",
758 "steps": [
759 {
760 "transformation": "equality_propagation",
761 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` > 4)) and multiple equal(5, `t1`.`a`))"
762 },
763 {
764 "transformation": "constant_propagation",
765 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` > 4)) and multiple equal(5, `t1`.`a`))"
766 },
767 {
768 "transformation": "trivial_condition_removal",
769 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` > 4)) and multiple equal(5, `t1`.`a`))"
770 }
771 ] /* steps */
772 } /* condition_processing */
773 },
774 {
775 "substitute_generated_columns": {
776 } /* substitute_generated_columns */
777 },
778 {
779 "table_dependencies": [
780 {
781 "table": "`t1`",
782 "row_may_be_null": false,
783 "map_bit": 0,
784 "depends_on_map_bits": [
785 ] /* depends_on_map_bits */
786 }
787 ] /* table_dependencies */
788 },
789 {
790 "ref_optimizer_key_uses": [
791 {
792 "table": "`t1`",
793 "field": "a",
794 "equals": "5",
795 "null_rejecting": true
796 }
797 ] /* ref_optimizer_key_uses */
798 },
799 {
800 "rows_estimation": [
801 {
802 "table": "`t1`",
803 "range_analysis": {
804 "table_scan": {
805 "rows": 9,
806 "cost": 3.25
807 } /* table_scan */,
808 "potential_range_indexes": [
809 {
810 "index": "a",
811 "usable": true,
812 "key_parts": [
813 "a",
814 "b"
815 ] /* key_parts */
816 }
817 ] /* potential_range_indexes */,
818 "best_covering_index_scan": {
819 "index": "a",
820 "cost": 1.1539,
821 "chosen": true
822 } /* best_covering_index_scan */,
823 "setup_range_conditions": [
824 ] /* setup_range_conditions */,
825 "group_index_range": {
826 "chosen": false,
827 "cause": "not_group_by_or_distinct"
828 } /* group_index_range */,
829 "skip_scan_range": {
830 "potential_skip_scan_indexes": [
831 {
832 "index": "a",
833 "usable": false,
834 "cause": "prefix_not_const_equality"
835 }
836 ] /* potential_skip_scan_indexes */
837 } /* skip_scan_range */,
838 "analyzing_range_alternatives": {
839 "range_scan_alternatives": [
840 {
841 "index": "a",
842 "ranges": [
843 "a = 5 AND b = 2",
844 "a = 5 AND b = 3",
845 "a = 5 AND 4 < b"
846 ] /* ranges */,
847 "index_dives_for_eq_ranges": true,
848 "rowid_ordered": false,
849 "using_mrr": false,
850 "index_only": true,
851 "in_memory": 1,
852 "rows": 3,
853 "cost": 0.560975,
854 "chosen": true
855 }
856 ] /* range_scan_alternatives */,
857 "analyzing_roworder_intersect": {
858 "usable": false,
859 "cause": "too_few_roworder_scans"
860 } /* analyzing_roworder_intersect */
861 } /* analyzing_range_alternatives */,
862 "chosen_range_access_summary": {
863 "range_access_plan": {
864 "type": "range_scan",
865 "index": "a",
866 "rows": 3,
867 "ranges": [
868 "a = 5 AND b = 2",
869 "a = 5 AND b = 3",
870 "a = 5 AND 4 < b"
871 ] /* ranges */
872 } /* range_access_plan */,
873 "rows_for_plan": 3,
874 "cost_for_plan": 0.560975,
875 "chosen": true
876 } /* chosen_range_access_summary */
877 } /* range_analysis */
878 }
879 ] /* rows_estimation */
880 },
881 {
882 "considered_execution_plans": [
883 {
884 "plan_prefix": [
885 ] /* plan_prefix */,
886 "table": "`t1`",
887 "best_access_path": {
888 "considered_access_paths": [
889 {
890 "access_type": "ref",
891 "index": "a",
892 "chosen": false,
893 "cause": "range_uses_more_keyparts"
894 },
895 {
896 "rows_to_scan": 3,
897 "filtering_effect": [
898 ] /* filtering_effect */,
899 "final_filtering_effect": 1,
900 "access_type": "range",
901 "range_details": {
902 "used_index": "a"
903 } /* range_details */,
904 "resulting_rows": 3,
905 "cost": 0.860975,
906 "chosen": true
907 }
908 ] /* considered_access_paths */
909 } /* best_access_path */,
910 "condition_filtering_pct": 100,
911 "rows_for_plan": 3,
912 "cost_for_plan": 0.860975,
913 "chosen": true
914 }
915 ] /* considered_execution_plans */
916 },
917 {
918 "attaching_conditions_to_tables": {
919 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))",
920 "attached_conditions_computation": [
921 ] /* attached_conditions_computation */,
922 "attached_conditions_summary": [
923 {
924 "table": "`t1`",
925 "attached": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))"
926 }
927 ] /* attached_conditions_summary */
928 } /* attaching_conditions_to_tables */
929 },
930 {
931 "finalizing_table_conditions": [
932 {
933 "table": "`t1`",
934 "original_table_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))",
935 "final_table_condition ": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` > 4)))"
936 }
937 ] /* finalizing_table_conditions */
938 },
939 {
940 "refine_plan": [
941 {
942 "table": "`t1`"
943 }
944 ] /* refine_plan */
945 }
946 ] /* steps */
947 } /* join_optimization */
948 },
949 {
950 "join_explain": {
951 "select#": 1,
952 "steps": [
953 ] /* steps */
954 } /* join_explain */
955 }
956 ] /* steps */
957 } 0 0
958
959 # 2 equality ranges: should not use index statistics
960 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b IS NULL);
961 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
962 1 SIMPLE t1 NULL range a a 10 NULL 3 100.00 Using where; Using index
963 Warnings:
964 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`b` = 3) or (`test`.`t1`.`b` is null)))
965 SELECT * FROM information_schema.OPTIMIZER_TRACE;
966 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
967 EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b IS NULL) {
968 "steps": [
969 {
970 "join_preparation": {
971 "select#": 1,
972 "steps": [
973 {
974 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))"
975 }
976 ] /* steps */
977 } /* join_preparation */
978 },
979 {
980 "join_optimization": {
981 "select#": 1,
982 "steps": [
983 {
984 "condition_processing": {
985 "condition": "WHERE",
986 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))",
987 "steps": [
988 {
989 "transformation": "equality_propagation",
990 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))"
991 },
992 {
993 "transformation": "constant_propagation",
994 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))"
995 },
996 {
997 "transformation": "trivial_condition_removal",
998 "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))"
999 }
1000 ] /* steps */
1001 } /* condition_processing */
1002 },
1003 {
1004 "substitute_generated_columns": {
1005 } /* substitute_generated_columns */
1006 },
1007 {
1008 "table_dependencies": [
1009 {
1010 "table": "`t1`",
1011 "row_may_be_null": false,
1012 "map_bit": 0,
1013 "depends_on_map_bits": [
1014 ] /* depends_on_map_bits */
1015 }
1016 ] /* table_dependencies */
1017 },
1018 {
1019 "ref_optimizer_key_uses": [
1020 {
1021 "table": "`t1`",
1022 "field": "a",
1023 "equals": "5",
1024 "null_rejecting": true
1025 }
1026 ] /* ref_optimizer_key_uses */
1027 },
1028 {
1029 "rows_estimation": [
1030 {
1031 "table": "`t1`",
1032 "range_analysis": {
1033 "table_scan": {
1034 "rows": 9,
1035 "cost": 3.25
1036 } /* table_scan */,
1037 "potential_range_indexes": [
1038 {
1039 "index": "a",
1040 "usable": true,
1041 "key_parts": [
1042 "a",
1043 "b"
1044 ] /* key_parts */
1045 }
1046 ] /* potential_range_indexes */,
1047 "best_covering_index_scan": {
1048 "index": "a",
1049 "cost": 1.1539,
1050 "chosen": true
1051 } /* best_covering_index_scan */,
1052 "setup_range_conditions": [
1053 ] /* setup_range_conditions */,
1054 "group_index_range": {
1055 "chosen": false,
1056 "cause": "not_group_by_or_distinct"
1057 } /* group_index_range */,
1058 "skip_scan_range": {
1059 "potential_skip_scan_indexes": [
1060 {
1061 "index": "a",
1062 "usable": false,
1063 "cause": "no_range_predicate"
1064 }
1065 ] /* potential_skip_scan_indexes */
1066 } /* skip_scan_range */,
1067 "analyzing_range_alternatives": {
1068 "range_scan_alternatives": [
1069 {
1070 "index": "a",
1071 "ranges": [
1072 "a = 5 AND b = NULL",
1073 "a = 5 AND b = 2",
1074 "a = 5 AND b = 3"
1075 ] /* ranges */,
1076 "index_dives_for_eq_ranges": true,
1077 "rowid_ordered": false,
1078 "using_mrr": false,
1079 "index_only": true,
1080 "in_memory": 1,
1081 "rows": 3,
1082 "cost": 0.560975,
1083 "chosen": true
1084 }
1085 ] /* range_scan_alternatives */,
1086 "analyzing_roworder_intersect": {
1087 "usable": false,
1088 "cause": "too_few_roworder_scans"
1089 } /* analyzing_roworder_intersect */
1090 } /* analyzing_range_alternatives */,
1091 "chosen_range_access_summary": {
1092 "range_access_plan": {
1093 "type": "range_scan",
1094 "index": "a",
1095 "rows": 3,
1096 "ranges": [
1097 "a = 5 AND b = NULL",
1098 "a = 5 AND b = 2",
1099 "a = 5 AND b = 3"
1100 ] /* ranges */
1101 } /* range_access_plan */,
1102 "rows_for_plan": 3,
1103 "cost_for_plan": 0.560975,
1104 "chosen": true
1105 } /* chosen_range_access_summary */
1106 } /* range_analysis */
1107 }
1108 ] /* rows_estimation */
1109 },
1110 {
1111 "considered_execution_plans": [
1112 {
1113 "plan_prefix": [
1114 ] /* plan_prefix */,
1115 "table": "`t1`",
1116 "best_access_path": {
1117 "considered_access_paths": [
1118 {
1119 "access_type": "ref",
1120 "index": "a",
1121 "chosen": false,
1122 "cause": "range_uses_more_keyparts"
1123 },
1124 {
1125 "rows_to_scan": 3,
1126 "filtering_effect": [
1127 ] /* filtering_effect */,
1128 "final_filtering_effect": 1,
1129 "access_type": "range",
1130 "range_details": {
1131 "used_index": "a"
1132 } /* range_details */,
1133 "resulting_rows": 3,
1134 "cost": 0.860975,
1135 "chosen": true
1136 }
1137 ] /* considered_access_paths */
1138 } /* best_access_path */,
1139 "condition_filtering_pct": 100,
1140 "rows_for_plan": 3,
1141 "cost_for_plan": 0.860975,
1142 "chosen": true
1143 }
1144 ] /* considered_execution_plans */
1145 },
1146 {
1147 "attaching_conditions_to_tables": {
1148 "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))",
1149 "attached_conditions_computation": [
1150 ] /* attached_conditions_computation */,
1151 "attached_conditions_summary": [
1152 {
1153 "table": "`t1`",
1154 "attached": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))"
1155 }
1156 ] /* attached_conditions_summary */
1157 } /* attaching_conditions_to_tables */
1158 },
1159 {
1160 "finalizing_table_conditions": [
1161 {
1162 "table": "`t1`",
1163 "original_table_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))",
1164 "final_table_condition ": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` is null)))"
1165 }
1166 ] /* finalizing_table_conditions */
1167 },
1168 {
1169 "refine_plan": [
1170 {
1171 "table": "`t1`"
1172 }
1173 ] /* refine_plan */
1174 }
1175 ] /* steps */
1176 } /* join_optimization */
1177 },
1178 {
1179 "join_explain": {
1180 "select#": 1,
1181 "steps": [
1182 ] /* steps */
1183 } /* join_explain */
1184 }
1185 ] /* steps */
1186 } 0 0
1187
1188 # 0 equality ranges: should not use index statistics
1189 EXPLAIN SELECT * FROM t1 WHERE a>5 AND (b=2 OR b=3 OR b=4);
1190 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1191 1 SIMPLE t1 NULL range a a 5 NULL 1 29.77 Using where; Using index
1192 Warnings:
1193 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` > 5) and ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`b` = 3) or (`test`.`t1`.`b` = 4)))
1194 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1195 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1196 EXPLAIN SELECT * FROM t1 WHERE a>5 AND (b=2 OR b=3 OR b=4) {
1197 "steps": [
1198 {
1199 "join_preparation": {
1200 "select#": 1,
1201 "steps": [
1202 {
1203 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
1204 }
1205 ] /* steps */
1206 } /* join_preparation */
1207 },
1208 {
1209 "join_optimization": {
1210 "select#": 1,
1211 "steps": [
1212 {
1213 "condition_processing": {
1214 "condition": "WHERE",
1215 "original_condition": "((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
1216 "steps": [
1217 {
1218 "transformation": "equality_propagation",
1219 "resulting_condition": "((`t1`.`a` > 5) and (multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)))"
1220 },
1221 {
1222 "transformation": "constant_propagation",
1223 "resulting_condition": "((`t1`.`a` > 5) and (multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)))"
1224 },
1225 {
1226 "transformation": "trivial_condition_removal",
1227 "resulting_condition": "((`t1`.`a` > 5) and (multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`)))"
1228 }
1229 ] /* steps */
1230 } /* condition_processing */
1231 },
1232 {
1233 "substitute_generated_columns": {
1234 } /* substitute_generated_columns */
1235 },
1236 {
1237 "table_dependencies": [
1238 {
1239 "table": "`t1`",
1240 "row_may_be_null": false,
1241 "map_bit": 0,
1242 "depends_on_map_bits": [
1243 ] /* depends_on_map_bits */
1244 }
1245 ] /* table_dependencies */
1246 },
1247 {
1248 "ref_optimizer_key_uses": [
1249 ] /* ref_optimizer_key_uses */
1250 },
1251 {
1252 "rows_estimation": [
1253 {
1254 "table": "`t1`",
1255 "range_analysis": {
1256 "table_scan": {
1257 "rows": 9,
1258 "cost": 3.25
1259 } /* table_scan */,
1260 "potential_range_indexes": [
1261 {
1262 "index": "a",
1263 "usable": true,
1264 "key_parts": [
1265 "a",
1266 "b"
1267 ] /* key_parts */
1268 }
1269 ] /* potential_range_indexes */,
1270 "best_covering_index_scan": {
1271 "index": "a",
1272 "cost": 1.1539,
1273 "chosen": true
1274 } /* best_covering_index_scan */,
1275 "setup_range_conditions": [
1276 ] /* setup_range_conditions */,
1277 "group_index_range": {
1278 "chosen": false,
1279 "cause": "not_group_by_or_distinct"
1280 } /* group_index_range */,
1281 "skip_scan_range": {
1282 "potential_skip_scan_indexes": [
1283 {
1284 "index": "a",
1285 "usable": false,
1286 "cause": "prefix_not_const_equality"
1287 }
1288 ] /* potential_skip_scan_indexes */
1289 } /* skip_scan_range */,
1290 "analyzing_range_alternatives": {
1291 "range_scan_alternatives": [
1292 {
1293 "index": "a",
1294 "ranges": [
1295 "5 < a"
1296 ] /* ranges */,
1297 "index_dives_for_eq_ranges": true,
1298 "rowid_ordered": false,
1299 "using_mrr": false,
1300 "index_only": true,
1301 "in_memory": 1,
1302 "rows": 1,
1303 "cost": 0.36,
1304 "chosen": true
1305 }
1306 ] /* range_scan_alternatives */,
1307 "analyzing_roworder_intersect": {
1308 "usable": false,
1309 "cause": "too_few_roworder_scans"
1310 } /* analyzing_roworder_intersect */
1311 } /* analyzing_range_alternatives */,
1312 "chosen_range_access_summary": {
1313 "range_access_plan": {
1314 "type": "range_scan",
1315 "index": "a",
1316 "rows": 1,
1317 "ranges": [
1318 "5 < a"
1319 ] /* ranges */
1320 } /* range_access_plan */,
1321 "rows_for_plan": 1,
1322 "cost_for_plan": 0.36,
1323 "chosen": true
1324 } /* chosen_range_access_summary */
1325 } /* range_analysis */
1326 }
1327 ] /* rows_estimation */
1328 },
1329 {
1330 "considered_execution_plans": [
1331 {
1332 "plan_prefix": [
1333 ] /* plan_prefix */,
1334 "table": "`t1`",
1335 "best_access_path": {
1336 "considered_access_paths": [
1337 {
1338 "rows_to_scan": 1,
1339 "filtering_effect": [
1340 ] /* filtering_effect */,
1341 "final_filtering_effect": 0.297668,
1342 "access_type": "range",
1343 "range_details": {
1344 "used_index": "a"
1345 } /* range_details */,
1346 "resulting_rows": 0.297668,
1347 "cost": 0.46,
1348 "chosen": true
1349 }
1350 ] /* considered_access_paths */
1351 } /* best_access_path */,
1352 "condition_filtering_pct": 100,
1353 "rows_for_plan": 0.297668,
1354 "cost_for_plan": 0.46,
1355 "chosen": true
1356 }
1357 ] /* considered_execution_plans */
1358 },
1359 {
1360 "attaching_conditions_to_tables": {
1361 "original_condition": "((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
1362 "attached_conditions_computation": [
1363 ] /* attached_conditions_computation */,
1364 "attached_conditions_summary": [
1365 {
1366 "table": "`t1`",
1367 "attached": "((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
1368 }
1369 ] /* attached_conditions_summary */
1370 } /* attaching_conditions_to_tables */
1371 },
1372 {
1373 "finalizing_table_conditions": [
1374 {
1375 "table": "`t1`",
1376 "original_table_condition": "((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))",
1377 "final_table_condition ": "((`t1`.`a` > 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4)))"
1378 }
1379 ] /* finalizing_table_conditions */
1380 },
1381 {
1382 "refine_plan": [
1383 {
1384 "table": "`t1`"
1385 }
1386 ] /* refine_plan */
1387 }
1388 ] /* steps */
1389 } /* join_optimization */
1390 },
1391 {
1392 "join_explain": {
1393 "select#": 1,
1394 "steps": [
1395 ] /* steps */
1396 } /* join_explain */
1397 }
1398 ] /* steps */
1399 } 0 0
1400 SET eq_range_index_dive_limit=0;
1401
1402 # 1 equality range: should not use index statistics
1403 EXPLAIN SELECT * FROM t1 WHERE a=5;
1404 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1405 1 SIMPLE t1 NULL ref a a 5 const 3 100.00 Using index
1406 Warnings:
1407 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 5)
1408 SELECT * FROM information_schema.OPTIMIZER_TRACE;
1409 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1410 EXPLAIN SELECT * FROM t1 WHERE a=5 {
1411 "steps": [
1412 {
1413 "join_preparation": {
1414 "select#": 1,
1415 "steps": [
1416 {
1417 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 5)"
1418 }
1419 ] /* steps */
1420 } /* join_preparation */
1421 },
1422 {
1423 "join_optimization": {
1424 "select#": 1,
1425 "steps": [
1426 {
1427 "condition_processing": {
1428 "condition": "WHERE",
1429 "original_condition": "(`t1`.`a` = 5)",
1430 "steps": [
1431 {
1432 "transformation": "equality_propagation",
1433 "resulting_condition": "multiple equal(5, `t1`.`a`)"
1434 },
1435 {
1436 "transformation": "constant_propagation",
1437 "resulting_condition": "multiple equal(5, `t1`.`a`)"
1438 },
1439 {
1440 "transformation": "trivial_condition_removal",
1441 "resulting_condition": "multiple equal(5, `t1`.`a`)"
1442 }
1443 ] /* steps */
1444 } /* condition_processing */
1445 },
1446 {
1447 "substitute_generated_columns": {
1448 } /* substitute_generated_columns */
1449 },
1450 {
1451 "table_dependencies": [
1452 {
1453 "table": "`t1`",
1454 "row_may_be_null": false,
1455 "map_bit": 0,
1456 "depends_on_map_bits": [
1457 ] /* depends_on_map_bits */
1458 }
1459 ] /* table_dependencies */
1460 },
1461 {
1462 "ref_optimizer_key_uses": [
1463 {
1464 "table": "`t1`",
1465 "field": "a",
1466 "equals": "5",
1467 "null_rejecting": true
1468 }
1469 ] /* ref_optimizer_key_uses */
1470 },
1471 {
1472 "rows_estimation": [
1473 {
1474 "table": "`t1`",
1475 "range_analysis": {
1476 "table_scan": {
1477 "rows": 9,
1478 "cost": 3.25
1479 } /* table_scan */,
1480 "potential_range_indexes": [
1481 {
1482 "index": "a",
1483 "usable": true,
1484 "key_parts": [
1485 "a",
1486 "b"
1487 ] /* key_parts */
1488 }
1489 ] /* potential_range_indexes */,
1490 "best_covering_index_scan": {
1491 "index": "a",
1492 "cost": 1.1539,
1493 "chosen": true
1494 } /* best_covering_index_scan */,
1495 "setup_range_conditions": [
1496 ] /* setup_range_conditions */,
1497 "group_index_range": {
1498 "chosen": false,
1499 "cause": "not_group_by_or_distinct"
1500 } /* group_index_range */,
1501 "skip_scan_range": {
1502 "potential_skip_scan_indexes": [
1503 {
1504 "index": "a",
1505 "usable": false,
1506 "cause": "no_range_predicate"
1507 }
1508 ] /* potential_skip_scan_indexes */
1509 } /* skip_scan_range */,
1510 "analyzing_range_alternatives": {
1511 "range_scan_alternatives": [
1512 {
1513 "index": "a",
1514 "ranges": [
1515 "a = 5"
1516 ] /* ranges */,
1517 "index_dives_for_eq_ranges": true,
1518 "rowid_ordered": false,
1519 "using_mrr": false,
1520 "index_only": true,
1521 "in_memory": 1,
1522 "rows": 3,
1523 "cost": 0.560975,
1524 "chosen": true
1525 }
1526 ] /* range_scan_alternatives */,
1527 "analyzing_roworder_intersect": {
1528 "usable": false,
1529 "cause": "too_few_roworder_scans"
1530 } /* analyzing_roworder_intersect */
1531 } /* analyzing_range_alternatives */,
1532 "chosen_range_access_summary": {
1533 "range_access_plan": {
1534 "type": "range_scan",
1535 "index": "a",
1536 "rows": 3,
1537 "ranges": [
1538 "a = 5"
1539 ] /* ranges */
1540 } /* range_access_plan */,
1541 "rows_for_plan": 3,
1542 "cost_for_plan": 0.560975,
1543 "chosen": true
1544 } /* chosen_range_access_summary */
1545 } /* range_analysis */
1546 }
1547 ] /* rows_estimation */
1548 },
1549 {
1550 "considered_execution_plans": [
1551 {
1552 "plan_prefix": [
1553 ] /* plan_prefix */,
1554 "table": "`t1`",
1555 "best_access_path": {
1556 "considered_access_paths": [
1557 {
1558 "access_type": "ref",
1559 "index": "a",
1560 "rows": 3,
1561 "cost": 0.550975,
1562 "chosen": true
1563 },
1564 {
1565 "access_type": "range",
1566 "range_details": {
1567 "used_index": "a"
1568 } /* range_details */,
1569 "chosen": false,
1570 "cause": "heuristic_index_cheaper"
1571 }
1572 ] /* considered_access_paths */
1573 } /* best_access_path */,
1574 "condition_filtering_pct": 100,
1575 "rows_for_plan": 3,
1576 "cost_for_plan": 0.550975,
1577 "chosen": true
1578 }
1579 ] /* considered_execution_plans */
1580 },
1581 {
1582 "attaching_conditions_to_tables": {
1583 "original_condition": "(`t1`.`a` = 5)",
1584 "attached_conditions_computation": [
1585 ] /* attached_conditions_computation */,
1586 "attached_conditions_summary": [
1587 {
1588 "table": "`t1`",
1589 "attached": "(`t1`.`a` = 5)"
1590 }
1591 ] /* attached_conditions_summary */
1592 } /* attaching_conditions_to_tables */
1593 },
1594 {
1595 "finalizing_table_conditions": [
1596 {
1597 "table": "`t1`",
1598 "original_table_condition": "(`t1`.`a` = 5)",
1599 "final_table_condition ": null
1600 }
1601 ] /* finalizing_table_conditions */
1602 },
1603 {
1604 "refine_plan": [
1605 {
1606 "table": "`t1`"
1607 }
1608 ] /* refine_plan */
1609 }
1610 ] /* steps */
1611 } /* join_optimization */
1612 },
1613 {
1614 "join_explain": {
1615 "select#": 1,
1616 "steps": [
1617 ] /* steps */
1618 } /* join_explain */
1619 }
1620 ] /* steps */
1621 } 0 0
1622 DROP TABLE t1;
1623 SET eq_range_index_dive_limit=default;