mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> SET optimizer_trace_max_mem_size=1024*1024*16; Query OK, 0 rows affected (0.00 sec) mysql> select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 6889877970355107670 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id); +--------+ | rec_id | +--------+ | 2 | +--------+ 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: select t1.rec_id from tbl1 t1 INNER JOIN (select a.id,a.rec_id,s_date from tbl2 b , tbl1 a WHERE a.id_value2 = b.t_id and a.id = 6889877970355107670 order by s_date desc) t2 on (t1.id = t2.id and t1.rec_id = t2.rec_id) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `a`.`id` AS `id`,`a`.`rec_id` AS `rec_id`,`b`.`s_date` AS `s_date` from `tbl2` `b` join `tbl1` `a` where ((`a`.`id_value2` = `b`.`t_id`) and (`a`.`id` = 6889877970355107670)) order by `b`.`s_date` desc" } ] } }, { "derived": { "table": "``.`` `t2`", "select#": 2, "merged": true, "transformations_to_derived_table": "removed_ordering" } }, { "expanded_query": "/* select#1 */ select `t1`.`rec_id` AS `rec_id` from (`tbl1` `t1` join (`tbl2` `b` join `tbl1` `a`) on(((`t1`.`id` = `a`.`id`) and (`t1`.`rec_id` = `a`.`rec_id`) and (`a`.`id_value2` = `b`.`t_id`) and (`a`.`id` = 6889877970355107670))))" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `t1`.`rec_id` AS `rec_id` from `tbl1` `t1` join `tbl2` `b` join `tbl1` `a` where ((`t1`.`id` = `a`.`id`) and (`t1`.`rec_id` = `a`.`rec_id`) and (`a`.`id_value2` = `b`.`t_id`) and (`a`.`id` = 6889877970355107670))" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`id` = `a`.`id`) and (`t1`.`rec_id` = `a`.`rec_id`) and (`a`.`id_value2` = `b`.`t_id`) and (`a`.`id` = 6889877970355107670))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(6889877970355107670, `t1`.`id`, `a`.`id`) and multiple equal(`t1`.`rec_id`, `a`.`rec_id`) and multiple equal(`a`.`id_value2`, `b`.`t_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(6889877970355107670, `t1`.`id`, `a`.`id`) and multiple equal(`t1`.`rec_id`, `a`.`rec_id`) and multiple equal(`a`.`id_value2`, `b`.`t_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(6889877970355107670, `t1`.`id`, `a`.`id`) and multiple equal(`t1`.`rec_id`, `a`.`rec_id`) and multiple equal(`a`.`id_value2`, `b`.`t_id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`tbl1` `t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tbl2` `b`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] }, { "table": "`tbl1` `a`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`tbl1` `t1`", "field": "id", "equals": "6889877970355107670", "null_rejecting": false }, { "table": "`tbl1` `t1`", "field": "rec_id", "equals": "`a`.`rec_id`", "null_rejecting": false }, { "table": "`tbl2` `b`", "field": "t_id", "equals": "`a`.`id_value2`", "null_rejecting": true }, { "table": "`tbl1` `a`", "field": "id", "equals": "6889877970355107670", "null_rejecting": false }, { "table": "`tbl1` `a`", "field": "rec_id", "equals": "`t1`.`rec_id`", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`tbl1` `t1`", "range_analysis": { "table_scan": { "rows": 10100, "cost": 2055.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id", "rec_id" ] } ], "best_covering_index_scan": { "index": "PRIMARY", "cost": 2050.5, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "6889877970355107670 <= id <= 6889877970355107670" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 6, "cost": 2.2174, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 6, "ranges": [ "6889877970355107670 <= id <= 6889877970355107670" ] }, "rows_for_plan": 6, "cost_for_plan": 2.2174, "chosen": true } } }, { "table": "`tbl2` `b`", "table_scan": { "rows": 10004, "cost": 33 } }, { "table": "`tbl1` `a`", "range_analysis": { "table_scan": { "rows": 10100, "cost": 2055.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id", "rec_id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "6889877970355107670 <= id <= 6889877970355107670" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 6, "cost": 2.2174, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 6, "ranges": [ "6889877970355107670 <= id <= 6889877970355107670" ] }, "rows_for_plan": 6, "cost_for_plan": 2.2174, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tbl1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 6, "cost": 2.2146, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 2.2146, "rest_of_plan": [ { "plan_prefix": [ "`tbl1` `t1`" ], "table": "`tbl1` `a`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7.2, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 9.4146, "rest_of_plan": [ { "plan_prefix": [ "`tbl1` `t1`", "`tbl1` `a`" ], "table": "`tbl2` `b`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7.2, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "cost": 2033.8, "rows": 10004, "chosen": false, "cause": "cost" } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 16.615, "chosen": true } ] } ] }, { "plan_prefix": [ ], "table": "`tbl1` `a`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 6, "cost": 2.2074, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 2.2074, "rest_of_plan": [ { "plan_prefix": [ "`tbl1` `a`" ], "table": "`tbl1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7.2, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 9.4074, "rest_of_plan": [ { "plan_prefix": [ "`tbl1` `a`", "`tbl1` `t1`" ], "table": "`tbl2` `b`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7.2, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "cost": 2033.8, "rows": 10004, "chosen": false, "cause": "cost" } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 16.607, "chosen": true } ] } ] }, { "plan_prefix": [ ], "table": "`tbl2` `b`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 10004, "access_type": "scan", "resulting_rows": 10004, "cost": 2033.8, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 10004, "cost_for_plan": 2033.8, "pruned_by_cost": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`b`.`t_id` = `a`.`id_value2`) and (`t1`.`rec_id` = `a`.`rec_id`) and (`a`.`id` = 6889877970355107670) and (`t1`.`id` = 6889877970355107670))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tbl1` `a`", "attached": "(`a`.`id_value2` is not null)" }, { "table": "`tbl1` `t1`", "attached": null }, { "table": "`tbl2` `b`", "attached": null } ] } }, { "refine_plan": [ { "table": "`tbl1` `a`" }, { "table": "`tbl1` `t1`" }, { "table": "`tbl2` `b`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.01 sec) mysql> SET optimizer_trace="enabled=off"; Query OK, 0 rows affected (0.00 sec)