10.5.27-opt>set optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.000 sec) 10.5.27-opt> 10.5.27-opt>EXECUTE stmt; +------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | | +------+-------------+-------+--------+---------------+------+---------+-------+------+-------+ 2 rows in set (0.000 sec) 10.5.27-opt>select * from information_schema.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1 TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.c1 AS c1,t2.c1 AS c1 from t2 where t2.c1 = 1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t2.c1 = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t2.c1)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t2.c1)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t2.c1)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t2", "field": "c1", "equals": "1", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t1", "rows": 1, "cost": 1, "table_type": "system" }, { "table": "t2", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.608544922 }, "potential_range_indexes": [ { "index": "c1", "usable": true, "key_parts": ["c1"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "c1", "ranges": ["(1) <= (c1) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345585794, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "c1", "rows": 1, "ranges": ["(1) <= (c1) <= (1)"] }, "rows_for_plan": 1, "cost_for_plan": 1.345585794, "chosen": true } } }, { "table": "t2", "rowid_filters": [ { "key": "c1", "build_cost": 0.130585794, "rows": 1 } ] }, { "selectivity_for_indexes": [ { "index_name": "c1", "selectivity_from_index": 0.333333333 } ], "selectivity_for_columns": [], "cond_selectivity": 0.333333333 } ] }, { "considered_execution_plans": [ { "plan_prefix": ["t1"], "table": "t2", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "c1", "used_range_estimates": true, "rows": 1, "cost": 1.125585794, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 1.125585794, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 1.325585794, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1", "t2"] }, { "attaching_conditions_to_tables": { "original_condition": "t2.c1 = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t2", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.030 sec) 10.5.27-opt>EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1; +------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | t2 | ref | c1 | c1 | 5 | const | 1 | Using index | +------+-------------+-------+--------+---------------+------+---------+-------+------+-------------+ 2 rows in set (0.000 sec) 10.5.27-opt> 10.5.27-opt>select * from information_schema.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t2.c1=1 TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.c1 AS c1,t2.c1 AS c1 from (t1 join t2) where t2.c1 = 1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t2.c1 = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t2.c1)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t2.c1)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t2.c1)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t2", "field": "c1", "equals": "1", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t1", "rows": 1, "cost": 1, "table_type": "system" }, { "table": "t2", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.608544922 }, "potential_range_indexes": [ { "index": "c1", "usable": true, "key_parts": ["c1"] } ], "best_covering_index_scan": { "index": "c1", "cost": 1.601757383, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "c1", "ranges": ["(1) <= (c1) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.345585794, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "c1", "rows": 1, "ranges": ["(1) <= (c1) <= (1)"] }, "rows_for_plan": 1, "cost_for_plan": 0.345585794, "chosen": true } } }, { "table": "t2", "rowid_filters": [ { "key": "c1", "build_cost": 0.130585794, "rows": 1 } ] }, { "selectivity_for_indexes": [ { "index_name": "c1", "selectivity_from_index": 0.333333333 } ], "selectivity_for_columns": [], "cond_selectivity": 0.333333333 } ] }, { "considered_execution_plans": [ { "plan_prefix": ["t1"], "table": "t2", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "c1", "used_range_estimates": true, "rows": 1, "cost": 0.125585794, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.125585794, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 0.325585794, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1", "t2"] }, { "attaching_conditions_to_tables": { "original_condition": "t2.c1 = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t2", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.000 sec) 10.5.27-opt>