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> update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.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) SET t1.rec_id = @ROWNUM:= @ROWNUM+1; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: update tbl1 t1 INNER JOIN (select a.id,a.rec_id,b.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) SET t1.rec_id = @ROWNUM:= @ROWNUM+1 TRACE: { "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select straight_join `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, "materialized": true } }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`rec_id` AS `rec_id` from (`tbl1` `t1` join (/* select#2 */ select straight_join `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) `t2` on(((`t1`.`id` = `t2`.`id`) and (`t1`.`rec_id` = `t2`.`rec_id`))))" }, { "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 (/* select#2 */ select straight_join `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) `t2` where ((`t1`.`id` = `t2`.`id`) and (`t1`.`rec_id` = `t2`.`rec_id`))" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`a`.`id_value2` = `b`.`t_id`) and (`a`.`id` = 6889877970355107670))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(`a`.`id_value2`, `b`.`t_id`) and multiple equal(6889877970355107670, `a`.`id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(`a`.`id_value2`, `b`.`t_id`) and multiple equal(6889877970355107670, `a`.`id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`a`.`id_value2`, `b`.`t_id`) and multiple equal(6889877970355107670, `a`.`id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`tbl2` `b`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tbl1` `a`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ 0 ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`tbl2` `b`", "field": "t_id", "equals": "`a`.`id_value2`", "null_rejecting": true }, { "table": "`tbl1` `a`", "field": "id", "equals": "6889877970355107670", "null_rejecting": false } ] }, { "rows_estimation": [ { "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": "`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, "use_tmp_table": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 10004, "cost_for_plan": 2033.8 }, { "plan_prefix": [ "`tbl2` `b`" ], "table": "`tbl1` `a`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 6, "cost": 22082, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 10, "rows_for_plan": 6002.4, "cost_for_plan": 24116 } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`a`.`id` = 6889877970355107670) and (`a`.`id_value2` = `b`.`t_id`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tbl2` `b`", "attached": null }, { "table": "`tbl1` `a`", "attached": "(`a`.`id_value2` = `b`.`t_id`)" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`b`.`s_date` desc", "items": [ { "item": "`b`.`s_date`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`b`.`s_date` desc" } }, { "refine_plan": [ { "table": "`tbl2` `b`" }, { "table": "`tbl1` `a`" } ] } ] } }, { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`id` = `t2`.`id`) and (`t1`.`rec_id` = `t2`.`rec_id`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(`t1`.`id`, `t2`.`id`) and multiple equal(`t1`.`rec_id`, `t2`.`rec_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(`t1`.`id`, `t2`.`id`) and multiple equal(`t1`.`rec_id`, `t2`.`rec_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`t1`.`id`, `t2`.`id`) and multiple equal(`t1`.`rec_id`, `t2`.`rec_id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`tbl1` `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": "`tbl1` `t1`", "field": "id", "equals": "`t2`.`id`", "null_rejecting": false }, { "table": "`tbl1` `t1`", "field": "rec_id", "equals": "`t2`.`rec_id`", "null_rejecting": false }, { "table": " `t2`", "field": "id", "equals": "`t1`.`id`", "null_rejecting": false }, { "table": " `t2`", "field": "rec_id", "equals": "`t1`.`rec_id`", "null_rejecting": false }, { "table": " `t2`", "field": "id", "equals": "`t1`.`id`", "null_rejecting": false }, { "table": " `t2`", "field": "rec_id", "equals": "`t1`.`rec_id`", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`tbl1` `t1`", "table_scan": { "rows": 10100, "cost": 33 } }, { "table": " `t2`", "table_scan": { "rows": 6002, "cost": 310 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": " `t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "rows_to_scan": 6002, "access_type": "scan", "resulting_rows": 6002, "cost": 1510.5, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6002, "cost_for_plan": 1510.5, "rest_of_plan": [ { "plan_prefix": [ " `t2`" ], "table": "`tbl1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7202.4, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "rows_to_scan": 10100, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 10100, "cost": 1.21e7, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 6002, "cost_for_plan": 8712.9, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`tbl1` `t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 10100, "access_type": "scan", "resulting_rows": 10100, "cost": 2053, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 10100, "cost_for_plan": 2053, "rest_of_plan": [ { "plan_prefix": [ "`tbl1` `t1`" ], "table": " `t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "rows": 10.003, "cost": 121240, "chosen": true }, { "access_type": "ref", "index": "", "rows": 10.003, "cost": 121240, "chosen": false }, { "rows_to_scan": 6002, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 6002, "cost": 1.21e7, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 101034, "cost_for_plan": 123293, "pruned_by_cost": true } ] } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`rec_id` = `t2`.`rec_id`) and (`t1`.`id` = `t2`.`id`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": " `t2`", "attached": null }, { "table": "`tbl1` `t1`", "attached": null } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 17, "key_length": 12, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 986895 } } }, { "refine_plan": [ { "table": " `t2`" }, { "table": "`tbl1` `t1`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": " `t2`", "row_length": 18, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 932067 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`tbl2` `b`", "field": "s_date" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 10000, "examined_rows": 10000, "number_of_tmp_files": 2, "sort_buffer_size": 262136, "sort_mode": "" } } ] } } ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) mysql> SET optimizer_trace="enabled=off"; Query OK, 0 rows affected (0.00 sec)