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