SET optimizer_trace_max_mem_size=1048576; SET end_markers_in_json=on; SET optimizer_trace="enabled=on,one_line=off"; CREATE TABLE t1 ( key1 INT NOT NULL, INDEX i1(key1) ); Inserting 1024 records into t1 ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2); ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3); ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4); ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5); ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6); ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7); ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8); UPDATE t1 SET key2=key1, key3=key1, key4=key1, key5=key1, key6=key1, key7=key1, key8=1024-key1; CREATE TABLE t2 ( key1a INT NOT NULL, key1b INT NOT NULL, key2 INT NOT NULL, key2_1 INT NOT NULL, key2_2 INT NOT NULL, key3 INT NOT NULL, primary key i1a (key1a, key1b), INDEX i1b (key1b, key1a), INDEX i2_1(key2, key2_1), INDEX i2_2(key2, key2_1) ); Warnings: Warning 1831 Duplicate index 'i2_2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1; EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": [ { "index": "i1", "usable": false, "cause": "not_applicable" }, { "index": "i2", "usable": true, "key_parts": [ "key2" ] /* key_parts */ }, { "index": "i3", "usable": false, "cause": "not_applicable" }, { "index": "i4", "usable": false, "cause": "not_applicable" }, { "index": "i5", "usable": false, "cause": "not_applicable" }, { "index": "i6", "usable": false, "cause": "not_applicable" }, { "index": "i7", "usable": false, "cause": "not_applicable" }, { "index": "i8", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i2", "ranges": [ "key2 < 5", "1020 < key2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 47, "cost": 16.96, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i2", "rows": 47, "ranges": [ "key2 < 5", "1020 < key2" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 47, "cost_for_plan": 16.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 47, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "i2" } /* range_details */, "resulting_rows": 47, "cost": 21.66, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 47, "cost_for_plan": 21.66, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set @@optimizer_trace_features="range_optimizer=off"; EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": "...", "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": "...", "skip_scan_range": "...", "analyzing_range_alternatives": "...", "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i2", "rows": 47, "ranges": [ "key2 < 5", "1020 < key2" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 47, "cost_for_plan": 16.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 47, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "i2" } /* range_details */, "resulting_rows": 47, "cost": 21.66, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 47, "cost_for_plan": 21.66, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set @@optimizer_trace_features="range_optimizer=on"; EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 45 100.00 Using sort_union(i1,i2); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": [ { "index": "i1", "usable": true, "key_parts": [ "key1" ] /* key_parts */ }, { "index": "i2", "usable": true, "key_parts": [ "key2" ] /* key_parts */ }, { "index": "i3", "usable": false, "cause": "not_applicable" }, { "index": "i4", "usable": false, "cause": "not_applicable" }, { "index": "i5", "usable": false, "cause": "not_applicable" }, { "index": "i6", "usable": false, "cause": "not_applicable" }, { "index": "i7", "usable": false, "cause": "not_applicable" }, { "index": "i8", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "analyzing_index_merge_union": [ { "indexes_to_merge": [ { "range_scan_alternatives": [ { "index": "i1", "ranges": [ "key1 < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 3, "cost": 0.5706, "chosen": true } ] /* range_scan_alternatives */, "index_to_merge": "i1", "cumulated_cost": 0.5706 }, { "range_scan_alternatives": [ { "index": "i2", "ranges": [ "1020 < key2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 42, "cost": 4.6781, "chosen": true } ] /* range_scan_alternatives */, "index_to_merge": "i2", "cumulated_cost": 5.2487 } ] /* indexes_to_merge */, "cost_of_reading_ranges": 5.2487, "cost_sort_rowid_and_read_disk": 2.2388, "cost_duplicate_removal": 19.181, "total_cost": 26.668 } ] /* analyzing_index_merge_union */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_merge", "index_merge_of": [ { "type": "range_scan", "index": "i1", "rows": 3, "ranges": [ "key1 < 3" ] /* ranges */ }, { "type": "range_scan", "index": "i2", "rows": 42, "ranges": [ "1020 < key2" ] /* ranges */ } ] /* index_merge_of */ } /* range_access_plan */, "rows_for_plan": 45, "cost_for_plan": 26.668, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 45, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "sort_union(i1,i2)" } /* range_details */, "resulting_rows": 45, "cost": 31.168, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 45, "cost_for_plan": 31.168, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))", "final_table_condition ": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` group by `test`.`t2`.`key2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t2`.`key2` AS `key2`,min(`t2`.`key2_1`) AS `MIN(key2_1)` from `t2` group by `t2`.`key2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t2`", "const_keys_added": { "keys": [ "i2_1", "i2_2" ] /* keys */, "cause": "group_by" } /* const_keys_added */, "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i1b", "usable": false, "cause": "not_applicable" }, { "index": "i2_1", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ }, { "index": "i2_2", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "i2_1", "cost": 109.96, "chosen": false, "cause": "cost" } /* best_covering_index_scan */, "group_index_range": { "potential_group_range_indexes": [ { "index": "i2_1", "covering": true, "rows": 103, "cost": 28.1 }, { "index": "i2_2", "covering": true, "rows": 103, "cost": 28.1 } ] /* potential_group_range_indexes */ } /* group_index_range */, "best_group_range_summary": { "type": "index_group", "index": "i2_1", "group_attribute": "key2_1", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, "rows": 103, "cost": 28.1, "key_parts_used_for_access": [ "key2" ] /* key_parts_used_for_access */, "ranges": [ ] /* ranges */, "chosen": true } /* best_group_range_summary */, "skip_scan_range": { "chosen": false, "cause": "has_group_by" } /* skip_scan_range */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "i2_1", "group_attribute": "key2_1", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, "rows": 103, "cost": 28.1, "key_parts_used_for_access": [ "key2" ] /* key_parts_used_for_access */, "ranges": [ ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 103, "cost_for_plan": 28.1, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 103, "access_type": "range", "range_details": { "used_index": "index_for_group_by(i2_1)" } /* range_details */, "resulting_rows": 103, "cost": 38.4, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 103, "cost_for_plan": 38.4, "sort_cost": 103, "new_cost_for_plan": 141.4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t2`.`key2`", "items": [ { "item": "`t2`.`key2`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t2`.`key2`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t2`", "index_provides_order": true, "order_direction": "asc", "index": "i2_1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT DISTINCT key2 FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t2`.`key2` AS `key2` from `test`.`t2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT DISTINCT key2 FROM t2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select distinct `t2`.`key2` AS `key2` from `t2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t2`", "const_keys_added": { "keys": [ "i2_1", "i2_2" ] /* keys */, "cause": "distinct" } /* const_keys_added */, "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i1b", "usable": false, "cause": "not_applicable" }, { "index": "i2_1", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ }, { "index": "i2_2", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "i2_1", "cost": 109.96, "chosen": false, "cause": "cost" } /* best_covering_index_scan */, "group_index_range": { "distinct_query": true, "potential_group_range_indexes": [ { "index": "i2_1", "covering": true, "rows": 103, "cost": 28.1 }, { "index": "i2_2", "covering": true, "rows": 103, "cost": 28.1 } ] /* potential_group_range_indexes */ } /* group_index_range */, "best_group_range_summary": { "type": "index_group", "index": "i2_1", "group_attribute": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 103, "cost": 28.1, "key_parts_used_for_access": [ "key2" ] /* key_parts_used_for_access */, "ranges": [ ] /* ranges */, "chosen": true } /* best_group_range_summary */, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } /* skip_scan_range */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "i2_1", "group_attribute": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 103, "cost": 28.1, "key_parts_used_for_access": [ "key2" ] /* key_parts_used_for_access */, "ranges": [ ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 103, "cost_for_plan": 28.1, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 103, "access_type": "range", "range_details": { "used_index": "index_for_group_by(i2_1)" } /* range_details */, "resulting_rows": 103, "cost": 38.4, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 103, "cost_for_plan": 38.4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "changed_distinct_to_group_by": true, "simplifying_group_by": { "original_clause": "`t2`.`key2`", "items": [ { "item": "`t2`.`key2`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t2`.`key2`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t2`", "index_provides_order": true, "order_direction": "asc", "index": "i2_1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1 GROUP BY key2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 47 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` where ((`test`.`t2`.`key2` = 5) or (`test`.`t2`.`key2` = 4) or (`test`.`t2`.`key2` = 3) or (`test`.`t2`.`key2` = 2) or (`test`.`t2`.`key2` = 1)) group by `test`.`t2`.`key2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT key2, MIN(key2_1) FROM t2 WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1 GROUP BY key2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))", "steps": [ { "transformation": "equality_propagation", "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`))" }, { "transformation": "constant_propagation", "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`))" }, { "transformation": "trivial_condition_removal", "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`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i1b", "usable": false, "cause": "not_applicable" }, { "index": "i2_1", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ }, { "index": "i2_2", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "i2_1", "cost": 109.96, "chosen": false, "cause": "cost" } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "potential_group_range_indexes": [ { "index": "i2_1", "covering": true, "index_dives_for_eq_ranges": true, "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */, "rows": 5, "cost": 8.5 }, { "index": "i2_2", "covering": true, "index_dives_for_eq_ranges": true, "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */, "rows": 5, "cost": 8.5 } ] /* potential_group_range_indexes */ } /* group_index_range */, "best_group_range_summary": { "type": "index_group", "index": "i2_1", "group_attribute": "key2_1", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, "rows": 5, "cost": 8.5, "key_parts_used_for_access": [ "key2" ] /* key_parts_used_for_access */, "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */, "chosen": true } /* best_group_range_summary */, "skip_scan_range": { "chosen": false, "cause": "has_group_by" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i2_1", "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 47, "cost": 5.2886, "chosen": true }, { "index": "i2_2", "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 47, "cost": 5.2886, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i2_1", "rows": 47, "ranges": [ "1 <= key2 <= 1", "2 <= key2 <= 2", "3 <= key2 <= 3", "4 <= key2 <= 4", "5 <= key2 <= 5" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 47, "cost_for_plan": 5.2886, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 47, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "i2_1" } /* range_details */, "resulting_rows": 47, "cost": 9.9886, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 47, "cost_for_plan": 9.9886, "sort_cost": 47, "new_cost_for_plan": 56.989, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t2`.`key2`", "items": [ { "item": "`t2`.`key2`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t2`.`key2`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t2`", "index_provides_order": true, "order_direction": "asc", "index": "i2_1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))", "final_table_condition ": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ref i2_1,i2_2 i2_1 4 const 10 19.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5))) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))" }, { "transformation": "constant_propagation", "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t2`", "field": "key2", "equals": "1", "null_rejecting": false }, { "table": "`t2`", "field": "key2", "equals": "1", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i1b", "usable": false, "cause": "not_applicable" }, { "index": "i2_1", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ }, { "index": "i2_2", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i2_1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i2_2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i2_1", "ranges": [ "1 <= key2 <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 10, "cost": 3.76, "chosen": true }, { "index": "i2_2", "ranges": [ "1 <= key2 <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 10, "cost": 3.76, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i2_1", "rows": 10, "ranges": [ "1 <= key2 <= 1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 10, "cost_for_plan": 3.76, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i2_1", "rows": 10, "cost": 3.5, "chosen": true }, { "access_type": "ref", "index": "i2_2", "rows": 10, "cost": 3.5, "chosen": false }, { "access_type": "range", "range_details": { "used_index": "i2_1" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 19, "rows_for_plan": 1.9, "cost_for_plan": 3.5, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))", "final_table_condition ": "((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge i2,i3,i4 i2,i3 4,4 NULL 2 100.00 Using union(i2,i3); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": [ { "index": "i1", "usable": false, "cause": "not_applicable" }, { "index": "i2", "usable": true, "key_parts": [ "key2" ] /* key_parts */ }, { "index": "i3", "usable": true, "key_parts": [ "key3" ] /* key_parts */ }, { "index": "i4", "usable": true, "key_parts": [ "key4" ] /* key_parts */ }, { "index": "i5", "usable": false, "cause": "not_applicable" }, { "index": "i6", "usable": false, "cause": "not_applicable" }, { "index": "i7", "usable": false, "cause": "not_applicable" }, { "index": "i8", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ { "impossible_condition": { "cause": "null_field_in_non_null_column" } /* impossible_condition */ } ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i2", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i3", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i4", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "analyzing_index_merge_union": [ { "indexes_to_merge": [ { "range_scan_alternatives": [ { "index": "i2", "ranges": [ "10 <= key2 <= 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.36, "chosen": true } ] /* range_scan_alternatives */, "index_to_merge": "i2", "cumulated_cost": 0.36 }, { "range_scan_alternatives": [ { "index": "i3", "ranges": [ "3 <= key3 <= 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.36, "chosen": true } ] /* range_scan_alternatives */, "index_to_merge": "i3", "cumulated_cost": 0.72 } ] /* indexes_to_merge */, "cost_of_reading_ranges": 0.72, "use_roworder_union": true, "cause": "always_cheaper_than_not_roworder_retrieval", "analyzing_roworder_scans": [ { "type": "range_scan", "index": "i2", "rows": 1, "ranges": [ "10 <= key2 <= 10" ] /* ranges */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ }, { "type": "range_scan", "index": "i3", "rows": 1, "ranges": [ "3 <= key3 <= 3" ] /* ranges */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } ] /* analyzing_roworder_scans */, "index_roworder_union_cost": 1.2922, "members": 2, "chosen": true } ] /* analyzing_index_merge_union */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_roworder_union", "union_of": [ { "type": "range_scan", "index": "i2", "rows": 1, "ranges": [ "10 <= key2 <= 10" ] /* ranges */ }, { "type": "range_scan", "index": "i3", "rows": 1, "ranges": [ "3 <= key3 <= 3" ] /* ranges */ } ] /* union_of */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 1.2922, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "union(i2,i3)" } /* range_details */, "resulting_rows": 2, "cost": 1.4922, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 1.4922, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))", "final_table_condition ": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL i2_1,i2_2 NULL NULL NULL 1024 33.98 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i1b", "usable": false, "cause": "not_applicable" }, { "index": "i2_1", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ }, { "index": "i2_2", "usable": true, "key_parts": [ "key2", "key2_1" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "range_scan_possible": false, "cause": "condition_always_true", "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } /* skip_scan_range */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1024, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 0.3398, "access_type": "scan", "resulting_rows": 347.97, "cost": 104.46, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 347.97, "cost_for_plan": 104.46, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))", "final_table_condition ": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t2`", "field": "key1a", "equals": "5", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "key1a", "key1b" ] /* key_parts */ }, { "index": "i1b", "usable": true, "key_parts": [ "key1b", "key1a" ] /* key_parts */ }, { "index": "i2_1", "usable": false, "cause": "not_applicable" }, { "index": "i2_2", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i1b", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "5 <= key1a <= 5 AND key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true }, { "index": "i1b", "ranges": [ "key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 9, "cost": 3.41, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 1, "ranges": [ "5 <= key1a <= 5 AND key1b < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 10, "cost": 3.56, "chosen": true }, { "rows_to_scan": 1, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" } /* range_details */, "resulting_rows": 1, "cost": 0.71, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.71, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))", "final_table_condition ": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`", "pushed_index_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or (key1a = 4 and key1b < 7 and key1b > 3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3))) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or (key1a = 4 and key1b < 7 and key1b > 3) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "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)))", "steps": [ { "transformation": "equality_propagation", "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`)))" }, { "transformation": "constant_propagation", "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`)))" }, { "transformation": "trivial_condition_removal", "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`)))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "key1a", "key1b" ] /* key_parts */ }, { "index": "i1b", "usable": true, "key_parts": [ "key1b", "key1a" ] /* key_parts */ }, { "index": "i2_1", "usable": false, "cause": "not_applicable" }, { "index": "i2_2", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i1b", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "4 <= key1a <= 4 AND 3 < key1b < 7", "5 <= key1a <= 5 AND 2 < key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 1.21, "chosen": true }, { "index": "i1b", "ranges": [ "2 < key1b <= 3", "3 < key1b < 7", "7 <= key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 6, "cost": 2.86, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "4 <= key1a <= 4 AND 3 < key1b < 7", "5 <= key1a <= 5 AND 2 < key1b < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 1.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" } /* range_details */, "resulting_rows": 2, "cost": 1.41, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 1.41, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "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)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))", "final_table_condition ": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`", "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)))", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and (key1a = 4 or key1a = 5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range PRIMARY,i1b i1b 4 NULL 2 19.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5))) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and (key1a = 4 or key1a = 5) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 106.56 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "key1a", "key1b" ] /* key_parts */ }, { "index": "i1b", "usable": true, "key_parts": [ "key1b", "key1a" ] /* key_parts */ }, { "index": "i2_1", "usable": false, "cause": "not_applicable" }, { "index": "i2_2", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i1b", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "4 <= key1a <= 4 AND 7 < key1b < 10", "5 <= key1a <= 5 AND 7 < key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 1.21, "chosen": true }, { "index": "i1b", "ranges": [ "7 < key1b < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.96, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i1b", "rows": 2, "ranges": [ "7 < key1b < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 0.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 0.19, "access_type": "range", "range_details": { "used_index": "i1b" } /* range_details */, "resulting_rows": 0.38, "cost": 1.16, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 0.38, "cost_for_plan": 1.16, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))", "final_table_condition ": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t2`", "pushed_index_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL i1,i2 NULL NULL NULL 1024 55.55 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": [ { "index": "i1", "usable": true, "key_parts": [ "key1" ] /* key_parts */ }, { "index": "i2", "usable": true, "key_parts": [ "key2" ] /* key_parts */ }, { "index": "i3", "usable": false, "cause": "not_applicable" }, { "index": "i4", "usable": false, "cause": "not_applicable" }, { "index": "i5", "usable": false, "cause": "not_applicable" }, { "index": "i6", "usable": false, "cause": "not_applicable" }, { "index": "i7", "usable": false, "cause": "not_applicable" }, { "index": "i8", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "analyzing_index_merge_union": [ { "indexes_to_merge": [ { "range_scan_alternatives": [ { "index": "i1", "ranges": [ "1 < key1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1023, "cost": 108, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "chosen": false, "cause": "cost" }, { "range_scan_alternatives": [ { "index": "i2", "ranges": [ "2 < key2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1022, "cost": 107.89, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "chosen": false, "cause": "cost" } ] /* indexes_to_merge */, "cost_of_reading_ranges": 0, "chosen": false, "cause": "cost" } ] /* analyzing_index_merge_union */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1024, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 0.5555, "access_type": "scan", "resulting_rows": 568.84, "cost": 104.96, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 568.84, "cost_for_plan": 104.96, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))", "final_table_condition ": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2 WHERE t1.key1=t2.key1a AND t1.key2 > 1020; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range i1,i2 i2 4 NULL 42 100.00 Using index condition 1 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.key1 10 100.00 NULL Warnings: Note 1003 /* select#1 */ select straight_join `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8`,`test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2 WHERE t1.key1=t2.key1a AND t1.key2 > 1020 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ 0 ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "key1", "equals": "`t2`.`key1a`", "null_rejecting": false }, { "table": "`t2`", "field": "key1a", "equals": "`t1`.`key1`", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 1024, "cost": 107.06 } /* table_scan */, "potential_range_indexes": [ { "index": "i1", "usable": false, "cause": "not_applicable" }, { "index": "i2", "usable": true, "key_parts": [ "key2" ] /* key_parts */ }, { "index": "i3", "usable": false, "cause": "not_applicable" }, { "index": "i4", "usable": false, "cause": "not_applicable" }, { "index": "i5", "usable": false, "cause": "not_applicable" }, { "index": "i6", "usable": false, "cause": "not_applicable" }, { "index": "i7", "usable": false, "cause": "not_applicable" }, { "index": "i8", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "skip_scan_range": { "chosen": false, "cause": "not_single_table" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i2", "ranges": [ "1020 < key2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 42, "cost": 14.96, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i2", "rows": 42, "ranges": [ "1020 < key2" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 42, "cost_for_plan": 14.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ }, { "table": "`t2`", "table_scan": { "rows": 1024, "cost": 2.0625 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i1", "usable": false, "chosen": false }, { "rows_to_scan": 42, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "i2" } /* range_details */, "resulting_rows": 42, "cost": 19.16, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 42, "cost_for_plan": 19.16 }, { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 10, "cost": 149.52, "chosen": true }, { "rows_to_scan": 1024, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 1024, "cost": 4302.9, "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 420, "cost_for_plan": 168.68 } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`key1a` = `t1`.`key1`) and (`t1`.`key2` > 1020))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`key2` > 1020)" }, { "table": "`t2`", "attached": "(`t2`.`key1a` = `t1`.`key1`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`key2` > 1020)", "final_table_condition ": "(`t1`.`key2` > 1020)" }, { "table": "`t2`", "original_table_condition": "(`t2`.`key1a` = `t1`.`key1`)", "final_table_condition ": null } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "(`t1`.`key2` > 1020)", "table_condition_attached": null }, { "table": "`t2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1,t2; CREATE TABLE t1 ( cola char(3) not null, colb char(3) not null, filler char(200), key(cola), key(colb) ) CHARSET utf8mb4; INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); Inserting records EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge cola,colb cola,colb 12,12 NULL 29 100.00 Using intersect(cola,colb); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo')) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`cola` = 'foo') and (`t1`.`colb` = 'bar'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "cola", "equals": "'foo'", "null_rejecting": false }, { "table": "`t1`", "field": "colb", "equals": "'bar'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 8704, "cost": 1311.3 } /* table_scan */, "potential_range_indexes": [ { "index": "cola", "usable": true, "key_parts": [ "cola" ] /* key_parts */ }, { "index": "colb", "usable": true, "key_parts": [ "colb" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "cola", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "colb", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "cola", "ranges": [ "foo <= cola <= foo" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 504, "cost": 176.66, "chosen": true }, { "index": "colb", "ranges": [ "bar <= colb <= bar" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 504, "cost": 176.66, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "cola", "index_scan_cost": 4.9074, "cumulated_index_scan_cost": 4.9074, "disk_sweep_cost": 109.54, "cumulated_total_cost": 114.45, "usable": true, "matching_rows_now": 504, "isect_covering_with_this_index": false, "chosen": true }, { "index": "colb", "index_scan_cost": 4.9074, "cumulated_index_scan_cost": 9.8148, "disk_sweep_cost": 7.1924, "cumulated_total_cost": 17.007, "usable": true, "matching_rows_now": 29.184, "isect_covering_with_this_index": false, "chosen": true } ] /* intersecting_indexes */, "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no_clustered_pk_index" } /* clustered_pk */, "rows": 29, "cost": 17.007, "covering": false, "chosen": true } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_roworder_intersect", "rows": 29, "cost": 17.007, "covering": false, "clustered_pk_scan": false, "intersect_of": [ { "type": "range_scan", "index": "cola", "rows": 504, "ranges": [ "foo <= cola <= foo" ] /* ranges */ }, { "type": "range_scan", "index": "colb", "rows": 504, "ranges": [ "bar <= colb <= bar" ] /* ranges */ } ] /* intersect_of */ } /* range_access_plan */, "rows_for_plan": 29, "cost_for_plan": 17.007, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "cola", "rows": 504, "cost": 176.4, "chosen": true }, { "access_type": "ref", "index": "colb", "rows": 504, "cost": 176.4, "chosen": false }, { "rows_to_scan": 29, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "intersect(cola,colb)" } /* range_details */, "resulting_rows": 29, "cost": 19.907, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 29, "cost_for_plan": 19.907, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))", "final_table_condition ": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref cola cola 12 const 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`cola` = 'f\no') SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no' { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`cola` AS `cola`,`t1`.`colb` AS `colb`,`t1`.`filler` AS `filler` from `t1` where (`t1`.`cola` = 'f\\no')" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`cola` = 'f\\no')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('f\\no', `t1`.`cola`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "cola", "equals": "'f\\no'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 8704, "cost": 1311.3 } /* table_scan */, "potential_range_indexes": [ { "index": "cola", "usable": true, "key_parts": [ "cola" ] /* key_parts */ }, { "index": "colb", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "cola", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "cola", "ranges": [ "f\no <= cola <= f\no" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "cola", "rows": 1, "ranges": [ "f\no <= cola <= f\no" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "cola", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "cola" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`cola` = 'f\\no')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`cola` = 'f\\no')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`cola` = 'f\\no')", "final_table_condition ": "(`t1`.`cola` = 'f\\no')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "(`t1`.`cola` = 'f\\no')", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1; CREATE TABLE t1(c INT); INSERT INTO t1 VALUES (),(); CREATE TABLE t2 (b INT, KEY(b)); INSERT INTO t2 VALUES (),(),(); SET optimizer_trace_features="greedy_search=off,dynamic_range=on"; EXPLAIN SELECT 1 FROM (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1) Warnings: Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT 1 FROM (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1" } ] /* steps */ } /* join_preparation */ }, { "derived": { "table": " `d2`", "select#": 2, "materialized": true } /* derived */ }, { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`b` < `t1`.`c`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t1`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 3, "cost": 0.5013 } /* table_scan */ }, { "table": "`t1`", "table_scan": { "rows": 2, "cost": 0.5009 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": "..." }, { "attaching_conditions_to_tables": { "original_condition": "(`t2`.`b` < `t1`.`c`)", "attached_conditions_computation": [ { "table": "`t2`", "rechecking_index_usage": { "recheck_reason": "not_first_table", "range_analysis": { "table_scan": { "rows": 3, "cost": 3.05 } /* table_scan */, "potential_range_indexes": [ { "index": "b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.5616, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "skip_scan_range": { "chosen": false, "cause": "not_single_table" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "b", "chosen": false, "cause": "depends_on_unread_values" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } /* rechecking_index_usage */ } ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": "`t2`", "attached": "(`t2`.`b` < `t1`.`c`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "''", "items": [ { "item": "1", "uses_only_constant_tables": true } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "(`t2`.`b` < `t1`.`c`)", "final_table_condition ": "(`t2`.`b` < `t1`.`c`)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" }, { "table": "`t2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "creating_tmp_table": { "tmp_table_info": { "table": " `d2`", "columns": 1, "row_length": 5, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_execution": { "select#": 2, "steps": [ { "rows_estimation_per_outer_row": { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 3, "cost": 2.9013 } /* table_scan */, "potential_range_indexes": [ { "index": "b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.5616, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */ } /* rows_estimation_per_outer_row */ }, { "rows_estimation_per_outer_row": { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 3, "cost": 2.9013 } /* table_scan */, "potential_range_indexes": [ { "index": "b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.5616, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */ } /* rows_estimation_per_outer_row */ } ] /* steps */ } /* join_execution */ }, { "table_dependencies": [ { "table": " `d2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": " `d2`", "rows": 1, "cost": 1, "table_type": "system", "empty": true } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 SET optimizer_trace_features="greedy_search=off,dynamic_range=off"; EXPLAIN SELECT 1 FROM (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1) Warnings: Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT 1 FROM (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1" } ] /* steps */ } /* join_preparation */ }, { "derived": { "table": " `d2`", "select#": 2, "materialized": true } /* derived */ }, { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`b` < `t1`.`c`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t2`.`b` < `t1`.`c`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t1`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 3, "cost": 0.5013 } /* table_scan */ }, { "table": "`t1`", "table_scan": { "rows": 2, "cost": 0.5009 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": "..." }, { "attaching_conditions_to_tables": { "original_condition": "(`t2`.`b` < `t1`.`c`)", "attached_conditions_computation": [ { "table": "`t2`", "rechecking_index_usage": { "recheck_reason": "not_first_table", "range_analysis": { "table_scan": { "rows": 3, "cost": 3.05 } /* table_scan */, "potential_range_indexes": [ { "index": "b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.5616, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "skip_scan_range": { "chosen": false, "cause": "not_single_table" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "b", "chosen": false, "cause": "depends_on_unread_values" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } /* rechecking_index_usage */ } ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": "`t2`", "attached": "(`t2`.`b` < `t1`.`c`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "''", "items": [ { "item": "1", "uses_only_constant_tables": true } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "(`t2`.`b` < `t1`.`c`)", "final_table_condition ": "(`t2`.`b` < `t1`.`c`)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" }, { "table": "`t2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "creating_tmp_table": { "tmp_table_info": { "table": " `d2`", "columns": 1, "row_length": 5, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_execution": { "select#": 2, "steps": [ { "rows_estimation_per_outer_row": { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 3, "cost": 2.9013 } /* table_scan */, "potential_range_indexes": [ { "index": "b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.5616, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */ } /* rows_estimation_per_outer_row */ } ] /* steps */ } /* join_execution */ }, { "table_dependencies": [ { "table": " `d2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": " `d2`", "rows": 1, "cost": 1, "table_type": "system", "empty": true } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1,t2; SET optimizer_trace_features=default; CREATE TABLE t1 ( i1 int, i2 int, c char(1), KEY k1 (i1), KEY k2 (i1, i2) ) CHARSET utf8mb4; INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1'); EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range k1,k2 k2 5 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`i1` > 2) order by `test`.`t1`.`i1`,`test`.`t1`.`i2` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`i1` > '2')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`i1` > '2')" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`i1` > '2')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`i1` > 2)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8016 } /* table_scan */, "potential_range_indexes": [ { "index": "k1", "usable": true, "key_parts": [ "i1" ] /* key_parts */ }, { "index": "k2", "usable": true, "key_parts": [ "i1", "i2" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "k1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "k2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "k1", "ranges": [ "2 < i1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.96, "chosen": true }, { "index": "k2", "ranges": [ "2 < i1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.96, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "k1", "rows": 2, "ranges": [ "2 < i1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 0.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "k1" } /* range_details */, "resulting_rows": 2, "cost": 1.16, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 1.16, "sort_cost": 2, "new_cost_for_plan": 3.16, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`i1` > 2)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`i1` > 2)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`i1`,`t1`.`i2`", "items": [ { "item": "`t1`.`i1`" }, { "item": "`t1`.`i2`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`i1`,`t1`.`i2`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ { "rows_estimation": { "table": "`t1`", "index": "k2", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8016 } /* table_scan */, "potential_range_indexes": [ { "index": "k1", "usable": false, "cause": "not_applicable" }, { "index": "k2", "usable": true, "key_parts": [ "i1", "i2" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "k2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "k2", "ranges": [ "2 < i1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.96, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "k2", "rows": 2, "ranges": [ "2 < i1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 0.96, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } /* rows_estimation */ } ] /* steps */, "index_order_summary": { "table": "`t1`", "index_provides_order": true, "order_direction": "asc", "index": "k2", "plan_changed": true, "access_type": "range" } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`i1` > 2)", "final_table_condition ": "(`t1`.`i1` > 2)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "(`t1`.`i1` > 2)", "table_condition_attached": null } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index k1,k2 k1 5 NULL 2 100.00 Using where; Backward index scan; Using index Warnings: Note 1003 /* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i1` >= 1) order by `test`.`t1`.`i1` desc SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select distinct `t1`.`i1` AS `i1` from `t1` where (`t1`.`i1` >= '1') order by `t1`.`i1` desc" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`i1` >= '1')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`i1` >= '1')" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`i1` >= '1')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`i1` >= 1)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8016 } /* table_scan */, "potential_range_indexes": [ { "index": "k1", "usable": true, "key_parts": [ "i1" ] /* key_parts */ }, { "index": "k2", "usable": true, "key_parts": [ "i1", "i2" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "k1", "cost": 0.4558, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "distinct_query": true, "potential_group_range_indexes": [ { "index": "k1", "covering": true, "index_dives_for_eq_ranges": true, "ranges": [ "1 <= i1" ] /* ranges */, "rows": 3, "cost": 0.7 }, { "index": "k2", "covering": true, "index_dives_for_eq_ranges": true, "ranges": [ "1 <= i1" ] /* ranges */, "rows": 3, "cost": 0.7 } ] /* potential_group_range_indexes */ } /* group_index_range */, "best_group_range_summary": { "type": "index_group", "index": "k1", "group_attribute": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 3, "cost": 0.7, "key_parts_used_for_access": [ "i1" ] /* key_parts_used_for_access */, "ranges": [ "1 <= i1" ] /* ranges */, "chosen": false, "cause": "cost" } /* best_group_range_summary */, "skip_scan_range": { "chosen": false, "cause": "has_select_distinct" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "k1", "ranges": [ "1 <= i1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 2, "cost": 0.4658, "chosen": false, "cause": "cost" }, { "index": "k2", "ranges": [ "1 <= i1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 2, "cost": 0.4681, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 2, "cost": 0.7016, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 0.7016, "sort_cost": 2, "new_cost_for_plan": 2.7016, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`i1` >= 1)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`i1` >= 1)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`i1` desc", "items": [ { "item": "`t1`.`i1`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`i1` desc" } /* simplifying_order_by */, "unknown_key_1": { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t1`", "index_provides_order": true, "order_direction": "desc", "index": "k1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, "unknown_key_2": { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t1`", "index_provides_order": true, "order_direction": "desc", "index": "k1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, "changed_distinct_to_group_by": true, "simplifying_group_by": { "original_clause": "`t1`.`i1` desc", "items": [ { "item": "`t1`.`i1`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`i1` desc" } /* simplifying_group_by */, "removed_order_by": true } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`t1`", "index_provides_order": true, "order_direction": "desc", "index": "k1", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`i1` >= 1)", "final_table_condition ": "(`t1`.`i1` >= 1)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1; CREATE TABLE t1 ( pk INT PRIMARY KEY, i1 INT, i2 INT, v varchar(1), INDEX i1_idx (i1), INDEX v_idx (v,i1) ) ENGINE=InnoDB, CHARSET utf8mb4; INSERT INTO t1 VALUES (1, 1, 9,'a'), (2, 2, 8,'b'), (3, 3, 7,'c'), (4, 4, 6,'d'), (5, 5, 5,'e'); # Covering ROR intersect not chosen: Index with more keyparts found. EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,i1_idx,v_idx v_idx 16 NULL 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "i1", "equals": "1", "null_rejecting": false }, { "table": "`t1`", "field": "v", "equals": "'a'", "null_rejecting": false }, { "table": "`t1`", "field": "i1", "equals": "1", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 5, "cost": 2.85 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "pk" ] /* key_parts */ }, { "index": "i1_idx", "usable": true, "key_parts": [ "i1", "pk" ] /* key_parts */ }, { "index": "v_idx", "usable": true, "key_parts": [ "v", "i1", "pk" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "v_idx", "cost": 0.7519, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i1_idx", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "v_idx", "usable": false, "cause": "prefix_not_const_equality" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "pk < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.71, "chosen": true }, { "index": "i1_idx", "ranges": [ "1 <= i1 <= 1 AND pk < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true }, { "index": "v_idx", "ranges": [ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.36, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "v_idx", "index_scan_cost": 0.25, "cumulated_index_scan_cost": 0.25, "disk_sweep_cost": 0, "cumulated_total_cost": 0.25, "usable": true, "matching_rows_now": 1, "isect_covering_with_this_index": true, "chosen": true } ] /* intersecting_indexes */, "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "roworder_is_covering" } /* clustered_pk */, "chosen": false, "cause": "too_few_indexes_to_merge" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "v_idx", "rows": 1, "ranges": [ "a <= v <= a AND 1 <= i1 <= 1 AND pk < 3" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.36, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i1_idx", "chosen": false, "cause": "range_uses_more_keyparts" }, { "access_type": "ref", "index": "v_idx", "chosen": false, "cause": "range_uses_more_keyparts" }, { "rows_to_scan": 1, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "v_idx" } /* range_details */, "resulting_rows": 1, "cost": 0.46, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.46, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))", "final_table_condition ": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 # Chooses ROR intersect EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index_merge PRIMARY,i1_idx,v_idx i1_idx,PRIMARY 9,4 NULL 1 100.00 Using intersect(i1_idx,PRIMARY); Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3)) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`pk` < 3))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "i1", "equals": "1", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 5, "cost": 2.85 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "pk" ] /* key_parts */ }, { "index": "i1_idx", "usable": true, "key_parts": [ "i1", "pk" ] /* key_parts */ }, { "index": "v_idx", "usable": true, "key_parts": [ "v", "i1", "pk" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "v_idx", "cost": 0.7519, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "i1_idx", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "v_idx", "tree_travel_cost": 0.15, "num_groups": 6, "rows": 5, "cost": 2.8019 } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "best_skip_scan_summary": { "type": "skip_scan", "index": "v_idx", "key_parts_used_for_access": [ "v", "i1" ] /* key_parts_used_for_access */, "range": [ "1 <= i1 <= 1" ] /* range */, "chosen": false, "cause": "cost" } /* best_skip_scan_summary */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "pk < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.71, "chosen": true }, { "index": "i1_idx", "ranges": [ "1 <= i1 <= 1 AND pk < 3" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true }, { "index": "v_idx", "chosen": false, "cause": "no_valid_range_for_this_index" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "i1_idx", "index_scan_cost": 0.25, "cumulated_index_scan_cost": 0.25, "disk_sweep_cost": 0.25, "cumulated_total_cost": 0.5, "usable": true, "matching_rows_now": 1, "isect_covering_with_this_index": false, "chosen": true } ] /* intersecting_indexes */, "clustered_pk": { "index_scan_cost": 0.05, "cumulated_index_scan_cost": 0.3, "disk_sweep_cost": 0, "clustered_pk_scan_added_to_intersect": true, "cumulated_cost": 0.3 } /* clustered_pk */, "rows": 1, "cost": 0.3, "covering": false, "chosen": true } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_roworder_intersect", "rows": 1, "cost": 0.3, "covering": false, "clustered_pk_scan": true, "intersect_of": [ { "type": "range_scan", "index": "i1_idx", "rows": 1, "ranges": [ "1 <= i1 <= 1 AND pk < 3" ] /* ranges */ } ] /* intersect_of */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.3, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i1_idx", "chosen": false, "cause": "range_uses_more_keyparts" }, { "rows_to_scan": 1, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "intersect(i1_idx,PRIMARY)" } /* range_details */, "resulting_rows": 1, "cost": 0.4, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))", "final_table_condition ": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1; CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); # Test trace for unreliable_ref_cost_and_range_uses_more_keyparts to # ignore ref access on index with overly optimistic cost-estimate EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,b b 4 NULL 2 25.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2)) group by `test`.`t1`.`a` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`b` < 2) and (`t1`.`a` = 1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "a", "equals": "1", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 4, "cost": 2.75 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "a", "b" ] /* key_parts */ }, { "index": "b", "usable": true, "key_parts": [ "b", "a" ] /* key_parts */ } ] /* potential_range_indexes */, "best_covering_index_scan": { "index": "b", "cost": 0.6511, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "potential_group_range_indexes": [ { "index": "PRIMARY", "covering": true, "index_dives_for_eq_ranges": true, "ranges": [ "1 <= a <= 1 AND b < 2" ] /* ranges */, "rows": 2, "cost": 0.55 }, { "index": "b", "covering": true, "usable": false, "cause": "group_attribute_not_prefix_in_index" } ] /* potential_group_range_indexes */ } /* group_index_range */, "best_group_range_summary": { "type": "index_group", "index": "PRIMARY", "group_attribute": "b", "min_aggregate": false, "max_aggregate": true, "distinct_aggregate": false, "rows": 2, "cost": 0.55, "key_parts_used_for_access": [ "a" ] /* key_parts_used_for_access */, "ranges": [ "1 <= a <= 1 AND b < 2" ] /* ranges */, "chosen": true } /* best_group_range_summary */, "skip_scan_range": { "chosen": false, "cause": "has_group_by" } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= a <= 1 AND b < 2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 2, "cost": 0.71, "chosen": false, "cause": "cost" }, { "index": "b", "ranges": [ "b < 2" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 2, "cost": 0.4604, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "b", "rows": 2, "ranges": [ "b < 2" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 0.4604, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "range_uses_more_keyparts" }, { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 0.25, "access_type": "range", "range_details": { "used_index": "b" } /* range_details */, "resulting_rows": 0.5, "cost": 0.6604, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 0.5, "cost_for_plan": 0.6604, "sort_cost": 0.5, "new_cost_for_plan": 1.1604, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`a` = 1) and (`t1`.`b` < 2))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t1`.`a`", "items": [ { "item": "`t1`.`a`", "equals_constant_in_where": true } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))", "final_table_condition ": "((`t1`.`a` = 1) and (`t1`.`b` < 2))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 drop table t1; # # Tracing of when test_if_skip_sort_order() switches to another # index and we abandon ICP, # see "disabling_pushed_condition_on_old_index" in trace. # CREATE TABLE t1 ( c1 VARCHAR(2) NOT NULL, i1 INTEGER NOT NULL, c2 VARCHAR(2) NOT NULL, KEY k1 (c1), KEY k2 (c1, i1) ) CHARSET utf8mb4; INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'); EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref k1,k2 k2 10 const 1 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` = '1') order by `test`.`t1`.`i1` SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "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`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`c1` = '1')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('1', `t1`.`c1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('1', `t1`.`c1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('1', `t1`.`c1`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t1`", "field": "c1", "equals": "'1'", "null_rejecting": false }, { "table": "`t1`", "field": "c1", "equals": "'1'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8024 } /* table_scan */, "potential_range_indexes": [ { "index": "k1", "usable": true, "key_parts": [ "c1" ] /* key_parts */ }, { "index": "k2", "usable": true, "key_parts": [ "c1", "i1" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "k1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "k2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "k1", "ranges": [ "1 <= c1 <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true }, { "index": "k2", "ranges": [ "1 <= c1 <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "k1", "rows": 1, "ranges": [ "1 <= c1 <= 1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "k1", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "ref", "index": "k2", "rows": 1, "cost": 0.35, "chosen": false }, { "access_type": "range", "range_details": { "used_index": "k1" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`c1` = '1')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`c1` = '1')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`i1`", "items": [ { "item": "`t1`.`i1`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`i1`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ { "check_if_range_uses_more_keyparts_than_ref": { "rerunning_range_optimizer_for_single_index": { "range_analysis": { "table_scan": { "rows": 2, "cost": 2e308 } /* table_scan */, "potential_range_indexes": [ { "index": "k1", "usable": false, "cause": "not_applicable" }, { "index": "k2", "usable": true, "key_parts": [ "c1", "i1" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "k2", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "k2", "ranges": [ "1 <= c1 <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "k2", "rows": 1, "ranges": [ "1 <= c1 <= 1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */, "access_type_unchanged": { "ref_key_length": 10, "range_key_length": 10 } /* access_type_unchanged */ } /* rerunning_range_optimizer_for_single_index */ } /* check_if_range_uses_more_keyparts_than_ref */ } ] /* steps */, "index_order_summary": { "table": "`t1`", "index_provides_order": true, "order_direction": "asc", "index": "k2", "plan_changed": true, "access_type": "ref" } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`c1` = '1')", "final_table_condition ": null } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 DROP TABLE t1; # BUG#18023222 OPTIMIZER TRACE ERROR ON RANGE ANALYSIS OF # INDEX ON A BINARY COLUMN CREATE TABLE t(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b)); INSERT INTO t VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C'); INSERT INTO t VALUES (2, NULL); EXPLAIN SELECT * FROM t WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ref i_b i_b 17 const 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c) SELECT trace FROM information_schema.optimizer_trace; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t`.`i` AS `i`,`t`.`b` AS `b` from `t` where (`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(0xd95b94336a9946a39cf5b58cfe772d8c, `t`.`b`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t`", "field": "b", "equals": "0xd95b94336a9946a39cf5b58cfe772d8c", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t`", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8026 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i_b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i_b", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i_b", "ranges": [ "0xd95b94336a9946a39cf5b58cfe772d8c <= b <= 0xd95b94336a9946a39cf5b58cfe772d8c" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i_b", "rows": 1, "ranges": [ "0xd95b94336a9946a39cf5b58cfe772d8c <= b <= 0xd95b94336a9946a39cf5b58cfe772d8c" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i_b", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "i_b" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t`", "attached": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t`", "original_table_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)", "final_table_condition ": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t`", "pushed_index_condition": "(`t`.`b` = 0xd95b94336a9946a39cf5b58cfe772d8c)", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } EXPLAIN SELECT * FROM t WHERE b IS NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ref i_b i_b 17 const 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`b` AS `b` from `test`.`t` where (`test`.`t`.`b` is null) SELECT trace FROM information_schema.optimizer_trace; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t`.`i` AS `i`,`t`.`b` AS `b` from `t` where (`t`.`b` is null)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t`.`b` is null)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t`.`b` is null)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t`.`b` is null)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t`.`b` is null)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t`", "field": "b", "equals": "NULL", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t`", "range_analysis": { "table_scan": { "rows": 2, "cost": 2.8026 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i_b", "usable": true, "key_parts": [ "b" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i_b", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i_b", "ranges": [ "NULL <= b <= NULL" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i_b", "rows": 1, "ranges": [ "NULL <= b <= NULL" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i_b", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "i_b" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t`.`b` is null)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t`", "attached": "(`t`.`b` is null)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`t`", "original_table_condition": "(`t`.`b` is null)", "final_table_condition ": "(`t`.`b` is null)" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t`", "pushed_index_condition": "(`t`.`b` is null)", "table_condition_attached": null } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } drop table t; # # Test trace of geometry fields # CREATE TABLE t1( pk INT AUTO_INCREMENT PRIMARY KEY, col_geom GEOMETRY NOT NULL SRID 0 ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, ST_GeomFromText('POINT(10 10)')); INSERT INTO t1 VALUES (2, ST_GeomFromText('POINT(11 11)')); CREATE SPATIAL INDEX idx3 on t1(col_geom); # Show that geometry data is traced as "unprintable_geometry_value" EXPLAIN SELECT pk, ST_AsText(col_geom) FROM t1 WHERE MBREquals(t1.col_geom, ST_GeomFromText('POINT(12 13)')); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range idx3 idx3 34 NULL 1 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,st_astext(`test`.`t1`.`col_geom`) AS `ST_AsText(col_geom)` from `test`.`t1` where mbrequals(`test`.`t1`.`col_geom`,(st_geomfromtext('POINT(12 13)'))) SELECT show_json_object('"range_scan_alternatives": [', TRACE) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; show_json_object('"range_scan_alternatives": [', TRACE) "range_scan_alternatives": [ { "index": "idx3", "ranges": [ "col_geom unprintable_geometry_value" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 0.61, "chosen": true } ] DROP TABLE t1; DROP FUNCTION show_json_object;