{ "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "dt", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t2.a AS a from t2 group by t2.a order by count(distinct t2.b) limit 1" } ] } }, { "expanded_query": "/* select#1 */ select t1.a AS a from ((/* select#2 */ select t2.a AS a from t2 group by t2.a order by count(distinct t2.b) limit 1) dt join t1 on(dt.a = t1.b))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "dt.a = t1.b", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(dt.a, t1.b)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(dt.a, t1.b)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(dt.a, t1.b)" } ] } }, { "join_optimization": { "select_id": 2, "steps": [ { "rows_estimation": [ { "table": "t2", "range_analysis": { "table_scan": { "rows": 7, "cost": 0.0119958 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["b", "a"] }, { "index": "a", "usable": true, "key_parts": ["a", "b"] } ], "best_covering_index_scan": { "index": "a", "cost": 0.007672715, "chosen": true }, "group_index_range": { "potential_group_range_indexes": [ { "index": "PRIMARY", "covering": true, "usable": false, "cause": "group attribute not prefix in index" }, { "index": "a", "covering": true, "rows": 1, "cost": 0.001693605 } ] }, "best_group_range_summary": { "type": "index_group", "index": "a", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": true, "rows": 1, "cost": 0.001693605, "key_parts_used_for_access": ["a", "b"], "ranges": [], "chosen": true }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "a", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": true, "rows": 1, "cost": 0.001693605, "key_parts_used_for_access": ["a", "b"], "ranges": [] }, "rows_for_plan": 1, "cost_for_plan": 0.001693605, "chosen": true } } } ] }, { "table_dependencies": [ { "table": "t2", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "index_merge", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.001693605, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "rows_read": 1, "rows_out": 1, "cost": 0.001693605, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "t2", "rows_for_plan": 1, "cost_for_plan": 0.001693605 } ] }, { "materialized_output_cardinality": { "join_output_cardinality": 1, "estimation": [ { "table": "t2", "steps": [ { "index_name": "a", "cardinality": 7 } ], "cardinality": 1 } ], "post_group_cardinality": 1 } }, { "best_join_order": ["t2"], "rows": 1, "cost": 0.001693605 }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t2", "attached_condition": null } ] } }, { "make_join_readinfo": [] }, { "test_if_skip_sort_order": [] }, { "prepare_sum_aggregators": { "function": "count(distinct t2.b)", "aggregator_type": "simple" } } ] } }, { "infer_derived_key_statistics": { "table_alias": "dt", "key_name": "key0", "key_parts": 1, "select": ["group_list_in_key"], "rec_per_key_estimate": 1 } }, { "ref_optimizer_key_uses": [ { "table": "", "index": "key0", "field": "a", "equals": "t1.b", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "", "table_scan": { "rows": 2, "read_cost": 0.010020701, "read_and_compare_cost": 0.010089369 } }, { "table": "t1", "table_scan": { "rows": 4, "read_cost": 0.0113788, "read_and_compare_cost": 0.0115068 } } ] }, { "table_dependencies": [ { "table": "", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t1", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 2, "rows_after_filter": 2, "rows_out": 2, "cost": 0.010089369, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 2, "rows_out": 2, "cost": 0.010089369, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t1", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 4, "rows_after_filter": 4, "rows_out": 4, "cost": 0.0115068, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 4, "rows_out": 4, "cost": 0.0115068, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "", "rows_for_plan": 2, "cost_for_plan": 0.010089369, "rest_of_plan": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "plan_details": { "record_count": 2 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 4, "rows_after_filter": 4, "rows_out": 4, "cost": 0.01237146, "cost_without_join_buffer": 0.0221944, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 4, "rows_out": 4, "cost": 0.01237146, "uses_join_buffering": true } } } ] }, { "plan_prefix": "", "table": "t1", "rows_for_plan": 8, "cost_for_plan": 0.022460829 } ] }, { "plan_prefix": "", "table": "t1", "rows_for_plan": 4, "cost_for_plan": 0.0115068, "rest_of_plan": [ { "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "", "plan_details": { "record_count": 4 }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 0.001090776, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "key0", "rows_read": 1, "rows_out": 1, "cost": 0.001090776, "uses_join_buffering": false } } } ] }, { "plan_prefix": "t1", "table": "", "rows_for_plan": 4, "cost_for_plan": 0.012597576 } ] } ] }, { "best_join_order": ["t1", ""], "rows": 4, "cost": 0.012597576 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "dt.a = t1.b" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached_condition": "t1.b is not null" }, { "table": "", "attached_condition": null } ] } }, { "make_join_readinfo": [] } ] } } ] }