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