mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: select * from (SELECT @r AS _id, (SELECT @r := sup_local_code FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_local_code, (SELECT a.local_name FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_node_name, (SELECT a.lv FROM p_locations a WHERE a.local_code = _id limit 1) AS sup_lv FROM (SELECT @r:='442000001000') as vars, p_locations AS h) as t where t.sup_lv > -1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "join_preparation": { "select#": 6, "steps": [ { "expanded_query": "/* select#6 */ select (@r:='442000001000') AS `@r:='442000001000'`" } ] } }, { "derived": { "table": " `vars`", "select#": 6, "materialized": true } }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select (@r:=`a`.`sup_local_code`) from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1" } ] } }, { "join_preparation": { "select#": 4, "steps": [ { "expanded_query": "/* select#4 */ select `a`.`local_name` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1" } ] } }, { "join_preparation": { "select#": 5, "steps": [ { "expanded_query": "/* select#5 */ select `a`.`lv` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1" } ] } }, { "expanded_query": "/* select#2 */ select (@`r`) AS `_id`,(/* select#3 */ select (@r:=`a`.`sup_local_code`) from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_local_code`,(/* select#4 */ select `a`.`local_name` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_node_name`,(/* select#5 */ select `a`.`lv` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_lv` from (/* select#6 */ select (@r:='442000001000') AS `@r:='442000001000'`) `vars` join `p_locations` `h`" } ] } }, { "derived": { "table": " `t`", "select#": 2, "materialized": true } }, { "expanded_query": "/* select#1 */ select `t`.`_id` AS `_id`,`t`.`sup_local_code` AS `sup_local_code`,`t`.`sup_node_name` AS `sup_node_name`,`t`.`sup_lv` AS `sup_lv` from (/* select#2 */ select (@`r`) AS `_id`,(/* select#3 */ select (@r:=`a`.`sup_local_code`) from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_local_code`,(/* select#4 */ select `a`.`local_name` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_node_name`,(/* select#5 */ select `a`.`lv` from `p_locations` `a` where (`a`.`local_code` = `_id`) limit 1) AS `sup_lv` from (/* select#6 */ select (@r:='442000001000') AS `@r:='442000001000'`) `vars` join `p_locations` `h`) `t` where (`t`.`sup_lv` > -1)" }, { "condition_pushdown_to_derived": { "table": " `t`", "original_condition": "(`t`.`sup_lv` > -1)", "steps": [ { "condition_pushdown": "checking_for_columns_in_derived_table", "remaining_condition": "(`t`.`sup_lv` > -1)" } ] } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "join_optimization": { "select#": 6, "steps": [ ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": " `vars`", "columns": 1, "row_length": 50, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "join_execution": { "select#": 6, "steps": [ ] } }, { "table_dependencies": [ { "table": " `vars`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`p_locations` `h`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": " `vars`", "rows": 1, "cost": 1, "table_type": "system", "empty": false }, { "table": "`p_locations` `h`", "table_scan": { "rows": 5, "cost": 0.25 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ " `vars`" ], "table": "`p_locations` `h`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 5, "access_type": "scan", "resulting_rows": 5, "cost": 0.75, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 5, "cost_for_plan": 0.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`p_locations` `h`", "attached": null } ] } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`p_locations` `h`" } ] } ] } }, { "join_optimization": { "select#": 5, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`a`.`local_code` = `_id`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`a`.`local_code` = `_id`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`p_locations` `a`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`p_locations` `a`", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`p_locations` `a`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 5, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 5, "cost": 0.75, "chosen": true } ] }, "condition_filtering_pct": 20, "rows_for_plan": 1, "cost_for_plan": 0.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`a`.`local_code` = `_id`)", "attached_conditions_computation": [ { "table": "`p_locations` `a`", "rechecking_index_usage": { "recheck_reason": "not_first_table", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } } ], "attached_conditions_summary": [ { "table": "`p_locations` `a`", "attached": "(`a`.`local_code` = `_id`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`p_locations` `a`", "original_table_condition": "(`a`.`local_code` = `_id`)", "final_table_condition ": "(`a`.`local_code` = `_id`)" } ] }, { "refine_plan": [ { "table": "`p_locations` `a`" } ] } ] } }, { "join_optimization": { "select#": 4, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`a`.`local_code` = `_id`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`a`.`local_code` = `_id`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`p_locations` `a`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`p_locations` `a`", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`p_locations` `a`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 5, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 5, "cost": 0.75, "chosen": true } ] }, "condition_filtering_pct": 20, "rows_for_plan": 1, "cost_for_plan": 0.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`a`.`local_code` = `_id`)", "attached_conditions_computation": [ { "table": "`p_locations` `a`", "rechecking_index_usage": { "recheck_reason": "not_first_table", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } } ], "attached_conditions_summary": [ { "table": "`p_locations` `a`", "attached": "(`a`.`local_code` = `_id`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`p_locations` `a`", "original_table_condition": "(`a`.`local_code` = `_id`)", "final_table_condition ": "(`a`.`local_code` = `_id`)" } ] }, { "refine_plan": [ { "table": "`p_locations` `a`" } ] } ] } }, { "join_optimization": { "select#": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`a`.`local_code` = `_id`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`a`.`local_code` = `_id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`a`.`local_code` = `_id`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`p_locations` `a`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`p_locations` `a`", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`p_locations` `a`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 5, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 5, "cost": 0.75, "chosen": true } ] }, "condition_filtering_pct": 20, "rows_for_plan": 1, "cost_for_plan": 0.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`a`.`local_code` = `_id`)", "attached_conditions_computation": [ { "table": "`p_locations` `a`", "rechecking_index_usage": { "recheck_reason": "not_first_table", "range_analysis": { "table_scan": { "rows": 5, "cost": 3.75 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_id", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_local_code", "usable": true, "key_parts": [ "local_code", "id" ] }, { "index": "index_p_locations_local_name", "usable": false, "cause": "not_applicable" }, { "index": "index_p_locations_sup_local_code", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "chosen": false, "cause": "disjuntive_predicate_present" } } } } ], "attached_conditions_summary": [ { "table": "`p_locations` `a`", "attached": "(`a`.`local_code` = `_id`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`p_locations` `a`", "original_table_condition": "(`a`.`local_code` = `_id`)", "final_table_condition ": "(`a`.`local_code` = `_id`)" } ] }, { "refine_plan": [ { "table": "`p_locations` `a`" } ] } ] } }, { "condition_processing": { "condition": "WHERE", "original_condition": "(`t`.`sup_lv` > -1)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t`.`sup_lv` > -1)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t`.`sup_lv` > -1)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t`.`sup_lv` > -1)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": " `t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": " `t`" } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": " `t`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 5, "access_type": "scan", "resulting_rows": 5, "cost": 3.0625, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 5, "cost_for_plan": 3.0625, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t`.`sup_lv` > -1)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": " `t`", "attached": "(`t`.`sup_lv` > -1)" } ] } }, { "finalizing_table_conditions": [ { "table": " `t`", "original_table_condition": "(`t`.`sup_lv` > -1)", "final_table_condition ": "(`t`.`sup_lv` > -1)" } ] }, { "refine_plan": [ { "table": " `t`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": " `t`", "in_plan_at_position": 0, "columns": 4, "row_length": 1014, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "subselect_execution": { "select#": 3, "steps": [ { "join_execution": { "select#": 3, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 4, "steps": [ { "join_execution": { "select#": 4, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 5, "steps": [ { "join_execution": { "select#": 5, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 3, "steps": [ { "join_execution": { "select#": 3, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 4, "steps": [ { "join_execution": { "select#": 4, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 5, "steps": [ { "join_execution": { "select#": 5, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 3, "steps": [ { "join_execution": { "select#": 3, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 4, "steps": [ { "join_execution": { "select#": 4, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 5, "steps": [ { "join_execution": { "select#": 5, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 3, "steps": [ { "join_execution": { "select#": 3, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 4, "steps": [ { "join_execution": { "select#": 4, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 5, "steps": [ { "join_execution": { "select#": 5, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 3, "steps": [ { "join_execution": { "select#": 3, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 4, "steps": [ { "join_execution": { "select#": 4, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 5, "steps": [ { "join_execution": { "select#": 5, "steps": [ ] } } ] } } ] } } ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)