{ "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`col2` = `t2`.`col1`) and (`t2`.`col4` = par1@0))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(`t1`.`col2`, `t2`.`col1`) and multiple equal(par1@0, `t2`.`col4`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(`t1`.`col2`, `t2`.`col1`) and multiple equal(par1@0, `t2`.`col4`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`t1`.`col2`, `t2`.`col1`) and multiple equal(par1@0, `t2`.`col4`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`table1` `t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`table2` `t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`table1` `t1`", "field": "col2", "equals": "`t2`.`col1`", "null_rejecting": true }, { "table": "`table2` `t2`", "field": "col1", "equals": "`t1`.`col2`", "null_rejecting": true }, { "table": "`table2` `t2`", "field": "col4", "equals": "par1@0", "null_rejecting": true }, { "table": "`table2` `t2`", "field": "col1", "equals": "`t1`.`col2`", "null_rejecting": true }, { "table": "`table2` `t2`", "field": "col4", "equals": "par1@0", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`table1` `t1`", "table_scan": { "rows": 171, "cost": 0.25 } }, { "table": "`table2` `t2`", "range_analysis": { "table_scan": { "rows": 5622, "cost": 588.55 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "fk_table2_1", "usable": false, "cause": "not_applicable" }, { "index": "fk_table2_2", "usable": true, "key_parts": [ "col4", "col1" ] }, { "index": "fk_table2_3", "usable": false, "cause": "not_applicable" }, { "index": "idx_1", "usable": false, "cause": "not_applicable" }, { "index": "idx_2", "usable": false, "cause": "not_applicable" }, { "index": "table2_col4_1", "usable": true, "key_parts": [ "col4", "Action", "col1" ] }, { "index": "table2_3", "usable": false, "cause": "not_applicable" } ], "best_covering_index_scan": { "index": "fk_table2_2", "cost": 563.993, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_single_table" }, "skip_scan_range": { "chosen": false, "cause": "not_single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "fk_table2_2", "ranges": [ "col4 = 26" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 1, "rows": 20, "cost": 2.26521, "chosen": true }, { "index": "table2_col4_1", "ranges": [ "col4 = 26" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 1, "rows": 20, "cost": 2.37047, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "fk_table2_2", "rows": 20, "ranges": [ "col4 = 26" ] }, "rows_for_plan": 20, "cost_for_plan": 2.26521, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`table2` `t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "fk_table2_2", "rows": 20, "cost": 2.25521, "chosen": true }, { "access_type": "ref", "index": "table2_col4_1", "rows": 20, "cost": 2.36047, "chosen": false }, { "access_type": "range", "range_details": { "used_index": "fk_table2_2" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 20, "cost_for_plan": 2.25521, "rest_of_plan": [ { "plan_prefix": [ "`table2` `t2`" ], "table": "`table1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "col2_UNIQUE", "rows": 1, "cost": 7, "chosen": true }, { "rows_to_scan": 171, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 171, "cost": 342.25, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 20, "cost_for_plan": 9.25521, "sort_cost": 20, "new_cost_for_plan": 29.2552, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`table1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "col2_UNIQUE", "usable": false, "chosen": false }, { "rows_to_scan": 171, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 171, "cost": 17.35, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 171, "cost_for_plan": 17.35, "rest_of_plan": [ { "plan_prefix": [ "`table1` `t1`" ], "table": "`table2` `t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 59.85, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "rows_to_scan": 20, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "fk_table2_2" }, "resulting_rows": 20, "cost": 729.352, "chosen": false } ] }, "condition_filtering_pct": 5, "rows_for_plan": 8.55, "cost_for_plan": 77.2, "pruned_by_cost": true } ] } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`col4` = par1@0) and (`t1`.`col2` = `t2`.`col1`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`table2` `t2`", "attached": "(`t2`.`col4` = par1@0)" }, { "table": "`table1` `t1`", "attached": "(`t1`.`col2` = `t2`.`col1`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`col1` desc", "items": [ { "item": "`t1`.`col1`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`t1`.`col1` desc" } } }, { "finalizing_table_conditions": [ { "table": "`table2` `t2`", "original_table_condition": "(`t2`.`col4` = par1@0)", "final_table_condition ": "(`t2`.`col4` = (par1@0))" }, { "table": "`table1` `t1`", "original_table_condition": "(`t1`.`col2` = `t2`.`col1`)", "final_table_condition ": null } ] }, { "refine_plan": [ { "table": "`table2` `t2`" }, { "table": "`table1` `t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 2, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "desc", "expression": "`t1`.`col1`" } ], "filesort_priority_queue_optimization": { "limit": 1, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 8, "row_size": 15, "max_rows_per_buffer": 2, "num_rows_estimate": 20, "num_rows_found": 1, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": 46, "sort_algorithm": "none", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } } ] }