Edit File: general_no_prot_all.result
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES set optimizer_trace_max_mem_size=1048576; set @@session.optimizer_trace="enabled=on"; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES CREATE TABLE t5 (c int); INSERT INTO t5 VALUES (NULL); CREATE TABLE t6 (d int , KEY (d)); INSERT INTO t6 VALUES (NULL),(NULL); SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ; RESULT NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = `t5`.`c`)" } ] } }, { "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `t6` where (`t6`.`d` = `t5`.`c`)) AS `RESULT` from `t5`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ ] } }, { "refine_plan": [ ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = NULL)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(NULL, `t6`.`d`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(NULL, `t6`.`d`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(NULL, `t6`.`d`)" } ] } }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "NULL", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 }, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] } ], "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true }, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } } ], "impossible_range": true }, "rows": 0, "cause": "impossible_where_condition" } ] } ], "empty_result": { "cause": "no matching row in const table" } } }, { "join_execution": { "select#": 2, "steps": [ ] } } ] } } ] } } ] } 0 0 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100 from information_schema.OPTIMIZER_TRACE; (1-length(replace(TRACE, " ", ""))/length(TRACE))*100 62.7018 set optimizer_trace="one_line=on"; SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ; RESULT NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": [{"expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = `t5`.`c`)"}]}},{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `t6` where (`t6`.`d` = `t5`.`c`)) AS `RESULT` from `t5`"}]}},{"join_optimization": {"select#": 1,"steps": [{"table_dependencies": [{"table": "`t5`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`t5`","rows": 1,"cost": 1,"table_type": "system","empty": false}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": []}},{"refine_plan": []}]}},{"join_execution": {"select#": 1,"steps": [{"subselect_execution": {"select#": 2,"steps": [{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`t6`.`d` = NULL)","steps": [{"transformation": "equality_propagation","resulting_condition": "multiple equal(NULL, `t6`.`d`)"},{"transformation": "constant_propagation","resulting_condition": "multiple equal(NULL, `t6`.`d`)"},{"transformation": "trivial_condition_removal","resulting_condition": "multiple equal(NULL, `t6`.`d`)"}]}},{"table_dependencies": [{"table": "`t6`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": [{"table": "`t6`","field": "d","equals": "NULL","null_rejecting": true}]},{"rows_estimation": [{"table": "`t6`","range_analysis": {"table_scan": {"rows": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"setup_range_conditions": [{"impossible_condition": {"cause": "comparison_with_null_always_false"}}],"impossible_range": true},"rows": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}},{"join_execution": {"select#": 2,"steps": []}}]}}]}}]} 0 0 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100 from information_schema.OPTIMIZER_TRACE; (1-length(replace(TRACE, " ", ""))/length(TRACE))*100 6.0651 set end_markers_in_json=on; set optimizer_trace="one_line=off"; EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t5 system NULL NULL NULL NULL 1 NULL 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))) AS `RESULT` from `t5`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = ifnull(NULL,NULL))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "ifnull(NULL,NULL)", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */, "rows": 0, "cause": "impossible_where_condition" } ] /* rows_estimation */ }, { "condition_on_constant_tables": "0", "condition_value": false } ] /* steps */, "empty_result": { "cause": "Impossible WHERE noticed after reading const tables" } /* empty_result */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ; RESULT NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))) AS `RESULT` from `t5`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = ifnull(NULL,NULL))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "ifnull(NULL,NULL)", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */, "rows": 0, "cause": "impossible_where_condition" } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } /* subselect_execution */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set @@session.optimizer_trace="enabled=off"; SELECT /* bug if you see this*/ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ; RESULT NULL select QUERY from information_schema.OPTIMIZER_TRACE; QUERY SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 set @@session.optimizer_trace="enabled=on"; SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ; RESULT NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select 2 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" } ] /* steps */ } /* join_preparation */ }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 9, "key_length": 8, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL)) union /* select#3 */ select 2 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))) AS `RESULT` from `t5`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = ifnull(NULL,NULL))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "ifnull(NULL,NULL)", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */, "rows": 0, "cause": "impossible_where_condition" } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_optimization": { "select#": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = ifnull(NULL,NULL))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` = ifnull(NULL,NULL))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "ifnull(NULL,NULL)", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */, "rows": 0, "cause": "impossible_where_condition" } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_execution": { "select#": 3, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_preparation": { "select#": "fake", "steps": [ { "expanded_query": "/* select#fake */ select `1` from dual" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": "fake", "steps": [ { "table_dependencies": [ { "table": "``.``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "``.``", "table_scan": { "rows": 0, "cost": 10 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "``.``", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 0, "cost": 10, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 10, "rows_for_plan": 0, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "``.``", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "``.``", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": "fake", "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } /* subselect_execution */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)); c select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select 2 from `t6` where (`t6`.`d` = ifnull(`t5`.`c`,NULL))" }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 9, "key_length": 8, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "expanded_query": "/* select#1 */ select `t5`.`c` AS `c` from `t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 2 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 2))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 2 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 2))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 2 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 2))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 2 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 2))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 2 from `t6` where ((`t6`.`d` = ifnull(`t5`.`c`,NULL)) and (<cache>(5) = 2))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t6`.`d` = NULL) and (<cache>(5) = 1))" }, { "transformation": "trivial_condition_removal", "resulting_condition": null } ] /* steps */ } /* condition_processing */ } ] /* steps */, "empty_result": { "cause": "Impossible WHERE" } /* empty_result */ } /* join_optimization */ }, { "join_optimization": { "select#": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t6`.`d` = NULL) and (<cache>(5) = 2))" }, { "transformation": "trivial_condition_removal", "resulting_condition": null } ] /* steps */ } /* condition_processing */ } ] /* steps */, "empty_result": { "cause": "Impossible WHERE" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_execution": { "select#": 3, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_preparation": { "select#": "fake", "steps": [ { "expanded_query": "/* select#fake */ select `1` from dual" }, { "transformation": { "select#": "fake", "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": "fake", "steps": [ { "table_dependencies": [ { "table": "``.``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "``.``", "table_scan": { "rows": 0, "cost": 10 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "``.``", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 0, "cost": 10, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 10, "rows_for_plan": 0, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": "fake", "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "put_1_in_SELECT_list": true } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "``.``", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "``.``", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": "fake", "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } /* subselect_execution */ }, { "condition_on_constant_tables": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `t6` where 0 union /* select#3 */ select 2 from `t6` where 0))", "condition_value": false } ] /* steps */, "empty_result": { "cause": "Impossible WHERE noticed after reading const tables" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE; (@query:=QUERY)+NULL (@trace:=TRACE)+NULL NULL NULL select length(@trace); length(@trace) 13792 set @max_mem_size=13900; set optimizer_trace_max_mem_size=@max_mem_size; select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size; length(@query)+length(@trace) > @@optimizer_trace_max_mem_size 1 SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)); c select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE; (@missing_bytes:=missing_bytes_beyond_max_mem_size) 15 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE; (@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL NULL NULL select length(@trace2), (length(@trace2) + @missing_bytes) = length(@trace), @query2 = @query; length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query 13777 1 1 select length(@query2) + length(@trace2) between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200); length(@query2) + length(@trace2) between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200) 1 select instr(@trace, @trace2) = 1; instr(@trace, @trace2) = 1 1 set optimizer_trace_max_mem_size=1; select 1; 1 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select 1 521 0 set optimizer_trace_max_mem_size=0; select 1; 1 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 529 0 set optimizer_trace_max_mem_size=1048576; explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 system NULL NULL NULL NULL 1 NULL 1 SIMPLE t6 ref d d 5 const 1 Using where; Using index; FirstMatch(t5) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select (`t6`.`d` + 1) from `t6` where isnull(`t6`.`d`)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t5`.`c` AS `c` from `t5` where (`t5`.`c` + 1) in (/* select#2 */ select (`t6`.`d` + 1) from `t6` where isnull(`t6`.`d`))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ] /* transformations */, "expanded_query": "/* select#1 */ select `t5`.`c` AS `c` from `t5` semi join (`t6`) where (1 and isnull(`t6`.`d`) and ((`t5`.`c` + 1) = (`t6`.`d` + 1)))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and isnull(`t6`.`d`) and ((`t5`.`c` + 1) = (`t6`.`d` + 1)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and isnull(`t6`.`d`) and ((`t5`.`c` + 1) = (`t6`.`d` + 1)))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and isnull(`t6`.`d`) and ((`t5`.`c` + 1) = (`t6`.`d` + 1)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(isnull(`t6`.`d`) and ((`t5`.`c` + 1) = (`t6`.`d` + 1)))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t6`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "NULL", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false }, { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "NULL <= d <= NULL" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 2.21, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "d", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */ } /* execution_plan_for_potential_materialization */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`t5`" ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "d", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ ] /* tables */ } /* recalculate_access_paths_and_cost */, "cost": 1.2, "rows": 1, "chosen": true }, { "strategy": "MaterializeLookup", "cost": 3.6, "rows": 1, "duplicate_tables_left": false, "chosen": false }, { "strategy": "DuplicatesWeedout", "cost": 3.6, "rows": 1, "duplicate_tables_left": false, "chosen": false } ] /* semijoin_strategy_choice */, "chosen": true }, { "final_semijoin_strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ ] /* tables */ } /* recalculate_access_paths_and_cost */ } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(isnull(`t6`.`d`) and ((NULL + 1) = (`t6`.`d` + 1)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(isnull(`t6`.`d`) and ((NULL + 1) = (`t6`.`d` + 1)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set @old_opt_switch=@@optimizer_switch; explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t6 ref d d 5 const 1 Using where; Using index select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select (`t6`.`d` + 1) from `t6` where isnull(`t6`.`d`)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t5`.`c` AS `c` from `t5` where <in_optimizer>((`t5`.`c` + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((`t5`.`c` + 1)) = (`t6`.`d` + 1)))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<in_optimizer>((`t5`.`c` + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((`t5`.`c` + 1)) = (`t6`.`d` + 1)))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t5`.`c` + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((`t5`.`c` + 1)) = (`t6`.`d` + 1)))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t5`.`c` + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((`t5`.`c` + 1)) = (`t6`.`d` + 1)))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t5`.`c` + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((`t5`.`c` + 1)) = (`t6`.`d` + 1)))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "condition_on_constant_tables": "<in_optimizer>((NULL + 1),<exists>(/* select#2 */ select (`t6`.`d` + 1) from `t6` where (isnull(`t6`.`d`) and (<cache>((NULL + 1)) = (`t6`.`d` + 1)))))", "condition_value": false } ] /* steps */, "empty_result": { "cause": "Impossible WHERE noticed after reading const tables" } /* empty_result */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(isnull(`t6`.`d`) and (<cache>((NULL + 1)) = (`t6`.`d` + 1)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(isnull(`t6`.`d`) and (<cache>((NULL + 1)) = (`t6`.`d` + 1)))" }, { "transformation": "constant_propagation", "resulting_condition": "(isnull(`t6`.`d`) and (<cache>((NULL + 1)) = (`t6`.`d` + 1)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(isnull(`t6`.`d`) and (<cache>((NULL + 1)) = (`t6`.`d` + 1)))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "NULL", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.4233, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "NULL <= d <= NULL" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 2.21, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "d", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "has_nullable_expressions": true, "treat_UNKNOWN_as_FALSE": true, "possible": true } /* transformation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "d", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */, "subq_mat_decision": { "parent_fanouts": [ { "select#": 1, "subq_attached_to_const_table": true, "fanout": 1, "cacheable": true } ] /* parent_fanouts */, "cost_to_create_and_fill_materialized_table": 3.399, "cost_of_one_EXISTS": 1.199, "number_of_subquery_evaluations": 1, "cost_of_materialization": 3.599, "cost_of_EXISTS": 1.199, "chosen": true } /* subq_mat_decision */ } /* execution_plan_for_potential_materialization */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "chosen": true, "unknown_key_1": { "creating_tmp_table": { "tmp_table_info": { "row_length": 9, "key_length": 9, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } /* tmp_table_info */ } /* creating_tmp_table */ } } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": "isnull(`t6`.`d`)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "isnull(`t6`.`d`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set optimizer_switch=@old_opt_switch; CREATE TABLE t1 (s1 CHAR(5), s2 CHAR(5)); INSERT INTO t1 VALUES ('z','?'),('y','!'); explain extended select * from t1 where s1 > any (select s2 from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,`test`.`t1`.`s2` AS `s2` from `test`.`t1` where <nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`))) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where s1 > any (select s2 from t1) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`s2` from `t1`" }, { "transformation": { "select#": 2, "from": "> ALL/ANY (SELECT)", "to": "SELECT(MIN)", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`s1` AS `s1`,`t1`.`s2` AS `s2` from `t1` where <nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "<nop>((`t1`.`s1` > (/* select#2 */ select min(`t1`.`s2`) from `t1`)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 explain extended select * from t1 where s1 > any (select max(s2) from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,`test`.`t1`.`s2` AS `s2` from `test`.`t1` where <nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`))) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where s1 > any (select max(s2) from t1) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select max(`t1`.`s2`) from `t1`" }, { "transformation": { "select#": 2, "from": "> ALL/ANY (SELECT)", "to": "MIN (SELECT)", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`s1` AS `s1`,`t1`.`s2` AS `s2` from `t1` where <nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "<nop>((`t1`.`s1` > <min>(/* select#2 */ select max(`t1`.`s2`) from `t1`)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 explain extended select * from t1 where s1 in (select s2 from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,`test`.`t1`.`s2` AS `s2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`))) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where s1 in (select s2 from t1) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`s2` from `t1`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`s1` AS `s1`,`t1`.`s2` AS `s2` from `t1` where <in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "<in_optimizer>(`t1`.`s1`,<exists>(/* select#2 */ select `t1`.`s2` from `t1` where (<cache>(`t1`.`s1`) = `t1`.`s2`)))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(<cache>(`t1`.`s1`) = `t1`.`s2`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(<cache>(`t1`.`s1`) = `t1`.`s2`)" }, { "transformation": "constant_propagation", "resulting_condition": "(<cache>(`t1`.`s1`) = `t1`.`s2`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(<cache>(`t1`.`s1`) = `t1`.`s2`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "put_1_in_SELECT_list": true } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": "(<cache>(`t1`.`s1`) = `t1`.`s2`)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(<cache>(`t1`.`s1`) = `t1`.`s2`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 explain extended select * from t1 where (s1,s2) in (select s2,s1 from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,`test`.`t1`.`s2` AS `s2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`)))) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where (s1,s2) in (select s2,s1 from t1) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`s1` AS `s1`,`t1`.`s2` AS `s2` from `t1` where <in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "<in_optimizer>((`t1`.`s1`,`t1`.`s2`),<exists>(/* select#2 */ select `t1`.`s2`,`t1`.`s1` from `t1` where ((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))" }, { "transformation": "constant_propagation", "resulting_condition": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4054, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4054, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "put_1_in_SELECT_list": true } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((<cache>(`t1`.`s1`) = `t1`.`s2`) and (<cache>(`t1`.`s2`) = `t1`.`s1`))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set optimizer_switch=@old_opt_switch; drop table t1; create table t1(a int); create table t2(a int); insert into t1 values(1),(2),(3); insert into t2 values(1),(2); set @@session.optimizer_prune_level=0; explain select * from t1,t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (Block Nested Loop) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1,t2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`a` AS `a` from `t1` join `t2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4034, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4034, "rows_for_plan": 2, "rest_of_plan": [ { "plan_prefix": [ "`t2`" ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 3, "cost": 3.2052, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6086, "rows_for_plan": 6, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 2, "cost": 3.2035, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.8087, "rows_for_plan": 6, "pruned_by_cost": true } ] /* rest_of_plan */ } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null }, { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 select @@optimizer_trace_features; @@optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on set @@optimizer_trace_features="greedy_search=off"; explain select * from t1,t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (Block Nested Loop) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1,t2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`a` AS `a` from `t1` join `t2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": "..." }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null }, { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set @@optimizer_trace_features=default; set @@session.optimizer_prune_level=default; drop table t1, t2; set @prefix_len = 6; set @blob_len = 16; set @suffix_len = @blob_len - @prefix_len; create table t1_16 (a1 blob(16), a2 blob(16)); create table t2_16 (b1 blob(16), b2 blob(16)); create table t3_16 (c1 blob(16), c2 blob(16)); insert into t1_16 values (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); insert into t1_16 values (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); insert into t1_16 values (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); insert into t2_16 values (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); insert into t2_16 values (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); insert into t2_16 values (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); insert into t3_16 values (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); insert into t3_16 values (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); insert into t3_16 values (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); insert into t3_16 values (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); explain extended select left(a1,7), left(a2,7) from t1_16 where a1 in (select b1 from t2_16 where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 /* select#1 */ select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`)))) select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select left(a1,7), left(a2,7) from t1_16 where a1 in (select b1 from t2_16 where b1 > '0') { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t2_16`.`b1` from `t2_16` where (`t2_16`.`b1` > '0')" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select left(`t1_16`.`a1`,7) AS `left(a1,7)`,left(`t1_16`.`a2`,7) AS `left(a2,7)` from `t1_16` where <in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1_16`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1_16`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1_16`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6293, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6293, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1_16`", "attached": "<in_optimizer>(`t1_16`.`a1`,<exists>(/* select#2 */ select `t2_16`.`b1` from `t2_16` where ((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1_16`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t2_16`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2_16`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2_16`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6293, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6293, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "possible": false, "cause": "inner blob" } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "put_1_in_SELECT_list": true } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2_16`", "attached": "((`t2_16`.`b1` > '0') and (<cache>(`t1_16`.`a1`) = `t2_16`.`b1`))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2_16`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 drop table t1_16,t2_16,t3_16; set @@optimizer_switch=@old_opt_switch; CREATE table t1 ( c1 integer ); INSERT INTO t1 VALUES ( 1 ); INSERT INTO t1 VALUES ( 2 ); INSERT INTO t1 VALUES ( 3 ); CREATE TABLE t2 ( c2 integer ); INSERT INTO t2 VALUES ( 1 ); INSERT INTO t2 VALUES ( 4 ); INSERT INTO t2 VALUES ( 5 ); SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ); c1 c2 1 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t2`.`c2` from `t2` where (`t2`.`c2` = 1)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t2`.`c2` AS `c2` from (`t1` left join `t2` on((`t1`.`c1` = `t2`.`c2`))) where `t2`.`c2` in (/* select#2 */ select `t2`.`c2` from `t2` where (`t2`.`c2` = 1))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin", "outer_join_to_inner_join", "JOIN_condition_to_WHERE", "parenthesis_removal" ] /* transformations */, "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t2`.`c2` AS `c2` from `t1` semi join (`t2`) join `t2` where (1 and (`t2`.`c2` = 1) and (`t2`.`c2` = `t2`.`c2`) and (`t1`.`c1` = `t2`.`c2`))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t2`.`c2` = 1) and (`t2`.`c2` = `t2`.`c2`) and (`t1`.`c1` = `t2`.`c2`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and multiple equal(1, `t2`.`c2`, `t2`.`c2`, `t1`.`c1`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and multiple equal(1, `t2`.`c2`, `t2`.`c2`, `t1`.`c1`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, `t2`.`c2`, `t2`.`c2`, `t1`.`c1`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */ } /* execution_plan_for_potential_materialization */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 3, "cost": 3.8052, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 6.4104, "rows_for_plan": 9, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t1`", "`t2`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 3, "cost": 7.4059, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 13.816, "rows_for_plan": 27, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 3, "cost": 7.4059, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */, "cost": 13.816, "rows": 9, "chosen": true }, { "strategy": "MaterializeLookup", "cost": 13.415, "rows": 9, "duplicate_tables_left": false, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 23.016, "rows": 9, "duplicate_tables_left": false, "chosen": false } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 3, "cost": 3.8052, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 6.4104, "rows_for_plan": 9, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "choice": "deferred" } ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "choice": "deferred" } ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "final_semijoin_strategy": "MaterializeLookup" } ] /* considered_execution_plans */ }, { "creating_tmp_table": { "tmp_table_info": { "row_length": 5, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 209715 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`c1` = 1) and (`t2`.`c2` = 1) and (`<subquery2>`.`c2` = 1) and (`t2`.`c2` = 1))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`c1` = 1) and (`t1`.`c1` is not null))" }, { "table": "`t2`", "attached": "(`t2`.`c2` = 1)" }, { "table": "``.`<subquery2>`", "attached": "(`<subquery2>`.`c2` = 1)" }, { "table": "`t2`", "attached": "(`t2`.`c2` = 1)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" }, { "table": "`t2`", "access_type": "table_scan" }, { "table": "``.`<subquery2>`" }, { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 SELECT * FROM t1 WHERE c1=5 UNION SELECT * FROM t2 WHERE c2=5; c1 5 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE c1=5 UNION SELECT * FROM t2 WHERE c2=5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1` from `t1` where (`t1`.`c1` = 5)" } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t2`.`c2` AS `c2` from `t2` where (`t2`.`c2` = 5)" } ] /* steps */ } /* join_preparation */ }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 5, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 209715 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`c1` = 5)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(5, `t1`.`c1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(5, `t1`.`c1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(5, `t1`.`c1`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`c1` = 5)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`c1` = 5)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`c2` = 5)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(5, `t2`.`c2`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(5, `t2`.`c2`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(5, `t2`.`c2`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t2`.`c2` = 5)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "(`t2`.`c2` = 5)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ }, { "join_preparation": { "select#": "fake", "steps": [ { "expanded_query": "/* select#fake */ select `c1` AS `c1` from dual" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": "fake", "steps": [ { "table_dependencies": [ { "table": "``.``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "``.``", "table_scan": { "rows": 1, "cost": 10 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "``.``", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 10.25, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 10.25, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "``.``", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "``.``", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": "fake", "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 explain select * from t1 where concat(c1,'x') IN (select left(c2,8) from t2) and concat(c1,'y') IN (select left(c2,9) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 3 SUBQUERY t2 ALL NULL NULL NULL NULL 3 NULL 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where concat(c1,'x') IN (select left(c2,8) from t2) and concat(c1,'y') IN (select left(c2,9) from t2) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select left(`t2`.`c2`,8) from `t2`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select left(`t2`.`c2`,9) from `t2`" }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1` from `t1` where (<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(<in_optimizer>(concat(`t1`.`c1`,'x'),<exists>(/* select#2 */ select left(`t2`.`c2`,8) from `t2` where (<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8)))) and <in_optimizer>(concat(`t1`.`c1`,'y'),<exists>(/* select#3 */ select left(`t2`.`c2`,9) from `t2` where (<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9)))))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "join_optimization": { "select#": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9))" }, { "transformation": "constant_propagation", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'y')) = left(`t2`.`c2`,9))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "materialization", "has_nullable_expressions": true, "treat_UNKNOWN_as_FALSE": true, "possible": true } /* transformation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */, "subq_mat_decision": { "parent_fanouts": [ { "select#": 1, "subq_attached_to_table": true, "table": "`t1`", "fanout": 3, "cacheable": true } ] /* parent_fanouts */, "cost_to_create_and_fill_materialized_table": 5.2041, "cost_of_one_EXISTS": 2.6041, "number_of_subquery_evaluations": 3, "cost_of_materialization": 5.8041, "cost_of_EXISTS": 7.8124, "chosen": true } /* subq_mat_decision */ } /* execution_plan_for_potential_materialization */ }, { "transformation": { "select#": 3, "from": "IN (SELECT)", "to": "materialization", "chosen": true, "unknown_key_1": { "creating_tmp_table": { "tmp_table_info": { "row_length": 11, "key_length": 12, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 95325 } /* tmp_table_info */ } /* creating_tmp_table */ } } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 3, "steps": [ ] /* steps */ } /* join_explain */ }, { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8))" }, { "transformation": "constant_propagation", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(<cache>(concat(`t1`.`c1`,'x')) = left(`t2`.`c2`,8))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "has_nullable_expressions": true, "treat_UNKNOWN_as_FALSE": true, "possible": true } /* transformation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */, "subq_mat_decision": { "parent_fanouts": [ { "select#": 1, "subq_attached_to_table": true, "table": "`t1`", "fanout": 3, "cacheable": true } ] /* parent_fanouts */, "cost_to_create_and_fill_materialized_table": 5.2041, "cost_of_one_EXISTS": 2.6041, "number_of_subquery_evaluations": 3, "cost_of_materialization": 5.8041, "cost_of_EXISTS": 7.8124, "chosen": true } /* subq_mat_decision */ } /* execution_plan_for_potential_materialization */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "chosen": true, "unknown_key_2": { "creating_tmp_table": { "tmp_table_info": { "row_length": 10, "key_length": 11, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 104857 } /* tmp_table_info */ } /* creating_tmp_table */ } } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 set optimizer_switch=@old_opt_switch; DROP TABLE t1,t2; create table t1 (a int); insert into t1 values(1); create table t2 (a int); insert into t2 values(1); select * from t1,t2; a a 1 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1,t2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`a` AS `a` from `t1` join `t2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "rows": 1, "cost": 1, "table_type": "system", "empty": false }, { "table": "`t2`", "rows": 1, "cost": 1, "table_type": "system", "empty": false } ] /* rows_estimation */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 create table t3 (a int, b int); create table t4 (a int primary key); insert into t4 values(1),(2); prepare stmt from 'select * from t3 where (a,a,b) in (select * from t1,t2,t4)'; select trace from information_schema.OPTIMIZER_TRACE; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`a`,`t2`.`a`,`t4`.`a` from `t1` join `t2` join `t4`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from `t3` where (`t3`.`a`,`t3`.`a`,`t3`.`b`) in (/* select#2 */ select `t1`.`a`,`t2`.`a`,`t4`.`a` from `t1` join `t2` join `t4`)" } ] /* steps */ } /* join_preparation */ } ] /* steps */ } execute stmt; a b select trace from information_schema.OPTIMIZER_TRACE; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`a`,`t2`.`a`,`t4`.`a` from `t1` join `t2` join `t4`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from `t3` where (`t3`.`a`,`t3`.`a`,`t3`.`b`) in (/* select#2 */ select `t1`.`a`,`t2`.`a`,`t4`.`a` from `t1` join `t2` join `t4`)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ] /* transformations */, "expanded_query": "/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from `t3` semi join (`t1` join `t2` join `t4`) where (1 and (`t3`.`a` = `t1`.`a`) and (`t3`.`a` = `t2`.`a`) and (`t3`.`b` = `t4`.`a`))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t3`.`a` = `t1`.`a`) and (`t3`.`a` = `t2`.`a`) and (`t3`.`b` = `t4`.`a`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t3`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t1`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t4`", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t4`", "field": "a", "equals": "`t3`.`b`", "null_rejecting": true } ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ { "table": "`t4`", "functionally_dependent": true } ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t3`", "rows": 1, "cost": 1, "table_type": "system", "empty": true }, { "table": "`t1`", "table_scan": { "rows": 1, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 1, "cost": 2 } /* table_scan */ }, { "table": "`t4`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "execution_plan_for_potential_materialization": { "steps": [ ] /* steps */ } /* execution_plan_for_potential_materialization */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.2017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 4.4035, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`", "`t2`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`", "`t1`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`", "`t4`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2018, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "pruned_by_cost": true } ] /* rest_of_plan */ } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.2017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t4`" ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t4`", "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2018, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ }, { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */, "cost": 5.6034, "rows": 1, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": false, "chosen": false } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`", "`t4`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true } ] /* rest_of_plan */ }, { "final_semijoin_strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ }, { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */ } ] /* considered_execution_plans */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } execute stmt; a b select trace from information_schema.OPTIMIZER_TRACE; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from `t4` semi join (`t1` join `t2`) join `t3` where (1 and (`t3`.`a` = `t1`.`a`) and (`t3`.`a` = `t2`.`a`) and (`t3`.`b` = `t4`.`a`))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t3`.`a` = `t1`.`a`) and (`t3`.`a` = `t2`.`a`) and (`t3`.`b` = `t4`.`a`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`t3`.`a`, `t1`.`a`, `t2`.`a`) and multiple equal(`t3`.`b`, `t4`.`a`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t3`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t1`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t4`", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t4`", "field": "a", "equals": "`t3`.`b`", "null_rejecting": true } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t3`", "rows": 1, "cost": 1, "table_type": "system", "empty": true }, { "table": "`t1`", "table_scan": { "rows": 1, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 1, "cost": 2 } /* table_scan */ }, { "table": "`t4`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "execution_plan_for_potential_materialization": { "steps": [ ] /* steps */ } /* execution_plan_for_potential_materialization */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.2017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 4.4035, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`", "`t2`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`", "`t1`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t1`", "`t4`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2018, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "pruned_by_cost": true } ] /* rest_of_plan */ } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.2017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "plan_prefix": [ "`t3`" ] /* plan_prefix */, "table": "`t4`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "scan", "cost": 2.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.2, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t4`" ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t3`", "`t4`", "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2018, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.6035, "rows_for_plan": 1, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ }, { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */, "cost": 5.6034, "rows": 1, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 8.0035, "rows": 1, "duplicate_tables_left": false, "chosen": false } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ "`t3`", "`t4`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4017, "rows_for_plan": 1, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true } ] /* rest_of_plan */ }, { "final_semijoin_strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ }, { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.2017, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */ } ] /* considered_execution_plans */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } DROP TABLE t1,t2,t3,t4; create table t1(a int); insert into t1 values(1),(1); create table t2(a int); insert into t2 values(1),(1); select * from t1 left join t2 on t2.a=500 where t2.a is NULL; a a 1 NULL 1 NULL select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1 left join t2 on t2.a=500 where t2.a is NULL { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`a` AS `a` from (`t1` left join `t2` on((`t2`.`a` = 500))) where isnull(`t2`.`a`)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformations_to_nested_joins": { "transformations": [ "parenthesis_removal" ] /* transformations */, "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`a` AS `a` from `t1` left join `t2` on((`t2`.`a` = 500)) where isnull(`t2`.`a`)" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "isnull(`t2`.`a`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "isnull(`t2`.`a`)" }, { "transformation": "constant_propagation", "resulting_condition": "isnull(`t2`.`a`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "isnull(`t2`.`a`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": [ 0 ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4034, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4034, "rows_for_plan": 2, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 2, "cost": 2.8035, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.2069, "rows_for_plan": 4, "chosen": true } ] /* rest_of_plan */ } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "isnull(`t2`.`a`)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": "`t2`", "attached": "(<if>(found_match(t2), isnull(`t2`.`a`), true) and <if>(is_not_null_compl(t2), (`t2`.`a` = 500), true))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" }, { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 drop table t1,t2; create table t1(a int, b int); insert into t1 values(1,NULL),(NULL,2); create table t2(c int, d int); insert into t2 values(1,1),(2,2); select * from t1 where (t1.a,t1.b) not in (select c,d from t2 where c>0); a b select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1 where (t1.a,t1.b) not in (select c,d from t2 where c>0) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where (`t2`.`c` > 0)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] /* evaluating_constant_where_conditions */, "evaluating_constant_having_conditions": [ ] /* evaluating_constant_having_conditions */ } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))" }, { "transformation": "constant_propagation", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))" }, { "transformation": "trivial_condition_removal", "subselect_evaluation": [ ] /* subselect_evaluation */, "resulting_condition": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4044, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4044, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(not(<in_optimizer>((`t1`.`a`,`t1`.`b`),<exists>(/* select#2 */ select `t2`.`c`,`t2`.`d` from `t2` where ((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))))))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))" } ] /* steps */ } /* condition_processing */ }, { "condition_processing": { "condition": "HAVING", "original_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))", "steps": [ { "transformation": "constant_propagation", "resulting_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`c`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`t2`.`d`), true))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t2`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4044, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4044, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "materialization", "has_nullable_expressions": true, "treat_UNKNOWN_as_FALSE": false, "possible": false, "cause": "cannot_handle_partial_matches" } /* transformation */ }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "put_1_in_SELECT_list": true } /* transformation */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "((`t2`.`c` > 0) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`a`) = `t2`.`c`) or isnull(`t2`.`c`)), true) and <if>(outer_field_is_not_null, ((<cache>(`t1`.`b`) = `t2`.`d`) or isnull(`t2`.`d`)), true))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } /* subselect_execution */ }, { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } /* subselect_execution */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 select t1.a,avg(t2.c) as moyenne from t1, t2 where t2.c>-1 group by t1.a having moyenne<>0; a moyenne NULL 1.5000 1 1.5000 select trace from information_schema.OPTIMIZER_TRACE; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,avg(`t2`.`c`) AS `moyenne` from `t1` join `t2` where (`t2`.`c` > -(1)) group by `t1`.`a` having (`moyenne` <> 0)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`c` > -(1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t2`.`c` > -(1))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t2`.`c` > -(1))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t2`.`c` > -(1))" } ] /* steps */ } /* condition_processing */ }, { "condition_processing": { "condition": "HAVING", "original_condition": "(`moyenne` <> 0)", "steps": [ { "transformation": "constant_propagation", "resulting_condition": "(`moyenne` <> 0)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`moyenne` <> 0)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4044, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4044, "rows_for_plan": 2, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 2, "cost": 2.8045, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 5.2089, "rows_for_plan": 4, "sort_cost": 4, "new_cost_for_plan": 9.2089, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4044, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4044, "rows_for_plan": 2, "pruned_by_heuristic": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t2`.`c` > -(1))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": "`t2`", "attached": "(`t2`.`c` > -(1))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "clause_processing": { "clause": "GROUP BY", "original_clause": "`t1`.`a`", "items": [ { "item": "`t1`.`a`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`a`" } /* clause_processing */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" }, { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 30, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 34952 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "a" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "rows": 2, "examined_rows": 2, "number_of_tmp_files": 0, "sort_buffer_size": "NNN", "sort_mode": "<sort_key, rowid>" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } select t1.a,avg(t2.c) as moyenne from t1, t2 where t2.c>-1 group by t1.a having 4=5; a moyenne select trace from information_schema.OPTIMIZER_TRACE; trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,avg(`t2`.`c`) AS `moyenne` from `t1` join `t2` where (`t2`.`c` > -(1)) group by `t1`.`a` having (4 = 5)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`c` > -(1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t2`.`c` > -(1))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t2`.`c` > -(1))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t2`.`c` > -(1))" } ] /* steps */ } /* condition_processing */ }, { "condition_processing": { "condition": "HAVING", "original_condition": "(4 = 5)", "steps": [ { "transformation": "constant_propagation", "resulting_condition": "(4 = 5)" }, { "transformation": "trivial_condition_removal", "resulting_condition": null } ] /* steps */ } /* condition_processing */ } ] /* steps */, "empty_result": { "cause": "Impossible HAVING" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } drop table t1,t2; update t6 set d=5 where d is NULL; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES update t6 set d=5 where d is NULL { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "isnull(`t6`.`d`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "isnull(`t6`.`d`)" }, { "transformation": "constant_propagation", "resulting_condition": "isnull(`t6`.`d`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "isnull(`t6`.`d`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "no_join" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "NULL <= d <= NULL" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "d", "rows": 1, "ranges": [ "NULL <= d <= NULL" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* steps */ } 0 0 delete from t6 where d=5; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES delete from t6 where d=5 { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = 5)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(5, `t6`.`d`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(5, `t6`.`d`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(5, `t6`.`d`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 2, "cost": 4.5034 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "no_join" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "5 <= d <= 5" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] /* range_scan_alternatives */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "d", "rows": 1, "ranges": [ "5 <= d <= 5" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* steps */ } 0 0 insert into t6 values(6),(7),(8); select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES insert into t6 values(6),(7),(8) { "steps": [ ] /* steps */ } 0 0 insert into t6 select * from t6 where d>7; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES insert into t6 select * from t6 where d>7 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where (`t6`.`d` > 7)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` > 7)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` > 7)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` > 7)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` > 7)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7051 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.6465, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "7 < d" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 2, "cost": 3.41, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.6051, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6051, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t6`.`d` > 7)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(`t6`.`d` > 7)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`", "access_type": "index_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 5, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 209715 } /* tmp_table_info */ } /* creating_tmp_table */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t5` join `t6` where (`t6`.`d` > 7000)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` > 7000)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` > 7000)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` > 7000)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` > 7000)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t6`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false }, { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 4, "cost": 4.9068 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "7000 < d" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "d", "rows": 1, "ranges": [ "7000 < d" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`t5`" ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 1, "cost": 2.41, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.41, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 12, "key_length": 7, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 87381 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t6`.`d` > 7000)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(`t6`.`d` > 7000)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`", "access_type": "range" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 delete t6 from t5, t6 where d>7000; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES delete t6 from t5, t6 where d>7000 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select NULL AS `NULL` from `t5` join `t6` where (`t6`.`d` > 7000)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` > 7000)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` > 7000)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` > 7000)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` > 7000)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t5`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t6`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t5`", "rows": 1, "cost": 1, "table_type": "system", "empty": false }, { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 4, "cost": 4.9068 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.8698, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_single_table" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "7000 < d" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 2.21, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`t5`" ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 1, "cost": 2.8068, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.8068, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t6`.`d` > 7000)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(`t6`.`d` > 7000)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set optimizer_trace_offset=2,optimizer_trace_limit=2; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select 1; 1 1 select 2; 2 2 select 3; 3 3 select 4; 4 4 select 5; 5 5 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select 3 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 3 AS `3`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 select 4 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 4 AS `4`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set optimizer_trace_offset=-2,optimizer_trace_limit=2; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select 1; 1 1 select 2; 2 2 select 3; 3 3 select 4; 4 4 select 5; 5 5 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select 4 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 4 AS `4`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 select 5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 5 AS `5`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set optimizer_trace_offset=default,optimizer_trace_limit=default; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES create table t1 ( id char(16) not null default '', data int not null ); create table t2 ( s char(16), i int, d double ); insert into t1 values ("a", 1), ("b", 2) | insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | create function f1() returns int begin declare ret int; insert into t1 values("z",0); delete from t1 where id="z"; select sum(data) into ret from t1; return ret; end| select f1()| f1() 3 select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select s, f1() from t2 order by s desc| s f1() c 3 b 3 a 3 select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select * from t6 where d in (select f1() from t2 where s="c")| d select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set @old_opt_switch=@@optimizer_switch; # Test was created when sj-mat-scan could not handle this query... set optimizer_switch="materialization=off"; set optimizer_trace_offset=-60, optimizer_trace_limit=60| select * from t6 where d in (select f1() from t2 where s="c")| d select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t6 where d in (select f1() from t2 where s="c") { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = 'c')" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = 'c'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ] /* transformations */, "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = 'c') and (`t6`.`d` = `f1`()))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t2`.`s` = 'c') and (`t6`.`d` = `f1`()))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal('c', `t2`.`s`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal('c', `t2`.`s`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t6`.`d` = `f1`()) and multiple equal('c', `t2`.`s`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t6`", "table_scan": { "rows": 4, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6212, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6212, "rows_for_plan": 3, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t2`" ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 4, "cost": 4.4072, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 7.0285, "rows_for_plan": 12, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 12.228, "rows": 4, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4, "cost": 2.8068, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.8068, "rows_for_plan": 4, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "final_semijoin_strategy": "DuplicateWeedout" } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`s` = 'c') and (`t6`.`d` = `f1`()))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "(`t2`.`s` = 'c')" }, { "table": "`t6`", "attached": "(`t6`.`d` = `f1`())" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "creating_tmp_table": { "tmp_table_info": { "row_length": 9, "key_length": 7, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "table": "`t2`", "access_type": "table_scan" }, { "table": "`t6`", "access_type": "index_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select count(*) from information_schema.OPTIMIZER_TRACE| count(*) 21 set optimizer_trace_offset=3, optimizer_trace_limit=1| select * from t6 where d in (select f1() from t2 where s="c")| d select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 set @@optimizer_switch=@old_opt_switch; # Stored procedures create procedure p1(arg char(1)) begin declare res int; select d into res from t6 where d in (select f1() from t2 where s=arg); select d+1 into res from t6 where d=res+1; end| set @old_opt_switch=@@optimizer_switch; # Test was created when sj-mat-scan could not handle this query... set optimizer_switch="materialization=off"; set optimizer_trace_offset=0, optimizer_trace_limit=100; call p1("c")| Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES call p1("c") { "steps": [ ] /* steps */ } 0 0 set res@1 NULL { "steps": [ ] /* steps */ } 0 0 select d into res from t6 where d in (select f1() from t2 where s=arg) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ] /* transformations */, "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t6`", "table_scan": { "rows": 4, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6212, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6212, "rows_for_plan": 3, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t2`" ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 4, "cost": 4.4072, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 7.0285, "rows_for_plan": 12, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 12.228, "rows": 4, "duplicate_tables_left": true, "chosen": true } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4, "cost": 2.8068, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.8068, "rows_for_plan": 4, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "final_semijoin_strategy": "DuplicateWeedout" } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t2`", "attached": "(`t2`.`s` = arg@0)" }, { "table": "`t6`", "attached": "(`t6`.`d` = `f1`())" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "creating_tmp_table": { "tmp_table_info": { "row_length": 9, "key_length": 7, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "table": "`t2`", "access_type": "table_scan" }, { "table": "`t6`", "access_type": "index_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select d+1 into res from t6 where d=res+1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select (`t6`.`d` + 1) AS `d+1` from `t6` where (`t6`.`d` = (res@1 + 1))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` = (res@1 + 1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal((res@1 + 1), `t6`.`d`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal((res@1 + 1), `t6`.`d`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal((res@1 + 1), `t6`.`d`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`t6`", "field": "d", "equals": "(res@1 + 1)", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 4, "cost": 4.9068 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.8698, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ { "impossible_condition": { "cause": "comparison_with_null_always_false" } /* impossible_condition */ } ] /* setup_range_conditions */, "impossible_range": true } /* range_analysis */, "rows": 0, "cause": "impossible_where_condition" } ] /* rows_estimation */ } ] /* steps */, "empty_result": { "cause": "no matching row in const table" } /* empty_result */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set @@optimizer_switch=@old_opt_switch; # Triggers create trigger trg1 before insert on t2 for each row begin set new.s=f1(); end| set optimizer_trace_offset=0, optimizer_trace_limit=100| insert into t2 select d,100,200 from t6 where d is not null| select * from information_schema.OPTIMIZER_TRACE| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES insert into t2 select d,100,200 from t6 where d is not null { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d`,100 AS `100`,200 AS `200` from `t6` where (`t6`.`d` is not null)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t6`.`d` is not null)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t6`.`d` is not null)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t6`.`d` is not null)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t6`.`d` is not null)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t6`", "range_analysis": { "table_scan": { "rows": 4, "cost": 4.9068 } /* table_scan */, "potential_range_indices": [ { "index": "d", "usable": true, "key_parts": [ "d" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "d", "cost": 1.8698, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "d", "ranges": [ "NULL < d" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 4, "cost": 1.8798, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4, "cost": 2.8068, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.8068, "rows_for_plan": 4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t6`.`d` is not null)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(`t6`.`d` is not null)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`", "access_type": "index_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set_trigger_field NEW.s:=`f1`() { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set_trigger_field NEW.s:=`f1`() { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set_trigger_field NEW.s:=`f1`() { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set_trigger_field NEW.s:=`f1`() { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select * from t2| s i d a 1 1 b 2 2 c 3 3 3 100 200 3 100 200 3 100 200 3 100 200 prepare stmt from 'select count(*) from t1 where t1.data=?'; set @param="c"; set optimizer_trace_offset=0, optimizer_trace_limit=100; execute stmt using @param; count(*) 0 select count(*) from information_schema.OPTIMIZER_TRACE; count(*) 1 select TRACE into @trace from information_schema.OPTIMIZER_TRACE; select @trace; @trace { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `t1` where (`t1`.`data` = 'c')" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`data` = 'c')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`data` = 'c')" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`data` = 'c')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`data` = 'c')" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`data` = 'c')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`data` = 'c')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } set optimizer_trace_offset=0, optimizer_trace_limit=100; execute stmt using @param; count(*) 0 select count(*) from information_schema.OPTIMIZER_TRACE; count(*) 1 select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE; select @trace=@trace2; @trace=@trace2 1 drop procedure p1; create temporary table optt like information_schema.OPTIMIZER_TRACE; create procedure p1(arg char(1)) begin declare res int; set optimizer_trace="enabled=off"; # want to see all of SELECT below set optimizer_trace_offset=0, optimizer_trace_limit=100; set optimizer_trace="enabled=on"; select d into res from t6 where d in (select f1() from t2 where s=arg); set optimizer_trace="enabled=off"; # and not more insert into optt select * from information_schema.OPTIMIZER_TRACE; set optimizer_trace_offset=default, optimizer_trace_limit=default; select d+1 into res from t6 where d=res+1; end| call p1("c")| Warnings: Warning 1329 No data - zero rows fetched, selected, or processed select * from optt| QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select d into res from t6 where d in (select f1() from t2 where s=arg) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } /* transformation */ } ] /* steps */ } /* join_preparation */ }, { "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] /* evaluating_constant_semijoin_conditions */ } /* transformation */ }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ] /* transformations */, "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and (`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t6`.`d` = `f1`()) and multiple equal(arg@0, `t2`.`s`))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t6`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ }, { "table": "`t2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "pulled_out_semijoin_tables": [ ] /* pulled_out_semijoin_tables */ }, { "rows_estimation": [ { "table": "`t6`", "table_scan": { "rows": 4, "cost": 2 } /* table_scan */ }, { "table": "`t2`", "table_scan": { "rows": 7, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 7, "cost": 3.4496, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4496, "rows_for_plan": 7, "chosen": true } ] /* considered_execution_plans */ } ] /* steps */ } /* execution_plan_for_potential_materialization */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t6`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4, "cost": 2.8068, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.8068, "rows_for_plan": 4, "semijoin_strategy_choice": [ ] /* semijoin_strategy_choice */, "rest_of_plan": [ { "plan_prefix": [ "`t6`" ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 7, "cost": 7.6497, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 10.457, "rows_for_plan": 28, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 7, "cost": 7.6497, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */ } ] /* tables */ } /* recalculate_access_paths_and_cost */, "cost": 10.457, "rows": 4, "chosen": true }, { "strategy": "MaterializeLookup", "cost": 10.456, "rows": 4, "duplicate_tables_left": false, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 18.857, "rows": 4, "duplicate_tables_left": false, "chosen": false } ] /* semijoin_strategy_choice */, "chosen": true } ] /* rest_of_plan */ }, { "plan_prefix": [ ] /* plan_prefix */, "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 7, "cost": 3.4496, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 3.4496, "rows_for_plan": 7, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "choice": "deferred" } ] /* semijoin_strategy_choice */, "pruned_by_heuristic": true }, { "final_semijoin_strategy": "MaterializeLookup" } ] /* considered_execution_plans */ }, { "creating_tmp_table": { "tmp_table_info": { "row_length": 5, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 209715 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`s` = arg@0) and (`t6`.`d` = `<subquery2>`.`f1()`))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t6`", "attached": "(`t6`.`d` is not null)" }, { "table": "``.`<subquery2>`", "attached": null }, { "table": "`t2`", "attached": "(`t2`.`s` = arg@0)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t6`", "access_type": "index_scan" }, { "table": "``.`<subquery2>`" }, { "table": "`t2`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 set ret@0 NULL { "steps": [ ] /* steps */ } 0 0 insert into t1 values("z",0) { "steps": [ ] /* steps */ } 0 0 delete from t1 where id="z" { "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` = 'z')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('z', `t1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('z', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 select sum(data) into ret from t1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 freturn 3 ret@0 { "steps": [ ] /* steps */ } 0 0 select @@optimizer_trace| @@optimizer_trace enabled=off,one_line=off set optimizer_trace="enabled=on"; drop temporary table optt; drop function f1; drop procedure p1; drop trigger trg1; create view v1 as select * from t1 where id < "c"; explain select * from v1 where id="b"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v1 where id="b" { "steps": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "merged": true } /* view */ }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`data` AS `data` from (`t1`) where ((`t1`.`id` = 'b') and (`t1`.`id` < 'c'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "transformations_to_nested_joins": { "transformations": [ "parenthesis_removal" ] /* transformations */, "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`data` AS `data` from `t1` where ((`t1`.`id` = 'b') and (`t1`.`id` < 'c'))" } /* transformations_to_nested_joins */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`id` = 'b') and (`t1`.`id` < 'c'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(('b' < 'c') and multiple equal('b', `t1`.`id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(('b' < 'c') and multiple equal('b', `t1`.`id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('b', `t1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 2, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 2.4154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.4154, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`id` = 'b')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`id` = 'b')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 insert into v1 values("z", 100); select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES insert into v1 values("z", 100) { "steps": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "merged": true } /* view */ } ] /* steps */ } 0 0 delete from v1 where data=100; select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES delete from v1 where data=100 { "steps": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "merged": true } /* view */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`data` = 100) and (`t1`.`id` < 'c'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`id` < 'c') and multiple equal(100, `t1`.`data`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`id` < 'c') and multiple equal(100, `t1`.`data`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`id` < 'c') and multiple equal(100, `t1`.`data`))" } ] /* steps */ } /* condition_processing */ }, { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 4.7154 } /* table_scan */ } /* range_analysis */ } ] /* steps */ } 0 0 drop view v1; create view v1 as select * from t1 where id < "c" limit 2; explain select * from v1 where id="b"; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 16 const 0 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v1 where id="b" { "steps": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "materialized": true } /* view */ }, { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`id` AS `id`,`t1`.`data` AS `data` from `t1` where (`t1`.`id` < 'c') limit 2" } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `v1`.`id` AS `id`,`v1`.`data` AS `data` from `v1` where (`v1`.`id` = 'b')" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`id` < 'c')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`id` < 'c')" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`id` < 'c')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`id` < 'c')" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 3, "cost": 2 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 3, "cost": 2.6154, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 2.6154, "rows_for_plan": 3, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`id` < 'c')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`id` < 'c')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "condition_processing": { "condition": "WHERE", "original_condition": "(`v1`.`id` = 'b')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('b', `v1`.`id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('b', `v1`.`id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('b', `v1`.`id`)" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`v1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`v1`", "field": "id", "equals": "'b'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`v1`" } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "rows": 0.2, "cost": 0.24, "chosen": true }, { "access_type": "scan", "cost": 10.4, "rows": 2, "cause": "cost", "chosen": false } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 0.24, "rows_for_plan": 0.2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`v1`.`id` = 'b')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`v1`", "attached": "(`v1`.`id` = 'b')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`v1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "row_length": 21, "key_length": 16, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 49932 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_explain": { "select#": 2, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ } /* join_explain */ } ] /* steps */ } 0 0 drop view v1; select * from information_schema.session_variables where VARIABLE_NAME="optimizer_trace"; VARIABLE_NAME VARIABLE_VALUE OPTIMIZER_TRACE enabled=on,one_line=off select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from information_schema.session_variables where VARIABLE_NAME="optimizer_trace" { "steps": [ { "creating_tmp_table": { "tmp_table_info": { "row_length": 3268, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 320 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `information_schema`.`session_variables`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`information_schema`.`session_variables`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `information_schema`.`session_variables` where (`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" }, { "transformation": "constant_propagation", "resulting_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`information_schema`.`session_variables`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`information_schema`.`session_variables`", "table_scan": { "rows": 2, "cost": 10 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`information_schema`.`session_variables`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 10.5, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 10.5, "rows_for_plan": 2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`information_schema`.`session_variables`", "attached": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`information_schema`.`session_variables`", "access_type": "table_scan" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 set end_markers_in_json=off; select 1 union select 2; 1 1 2 select TRACE into dumpfile 'MYSQLTEST_VARDIR/tmp/optimizer_trace.txt' from information_schema.OPTIMIZER_TRACE;; { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 1 AS `1`" } ] } }, { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 2 AS `2`" } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 9, "key_length": 8, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 116508 } } }, { "join_optimization": { "select#": 1, "steps": [ ] } }, { "join_optimization": { "select#": 2, "steps": [ ] } }, { "join_execution": { "select#": 1, "steps": [ ] } }, { "join_execution": { "select#": 2, "steps": [ ] } }, { "join_preparation": { "select#": "fake", "steps": [ { "expanded_query": "/* select#fake */ select `1` AS `1` from dual" } ] } }, { "join_optimization": { "select#": "fake", "steps": [ { "table_dependencies": [ { "table": "``.``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "``.``", "table_scan": { "rows": 2, "cost": 10 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "``.``", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2, "cost": 10.5, "chosen": true } ] }, "cost_for_plan": 10.5, "rows_for_plan": 2, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "``.``", "attached": null } ] } }, { "refine_plan": [ { "table": "``.``", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": "fake", "steps": [ ] } } ] }set optimizer_switch='default,index_merge=on,index_merge=off,default'; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default' select @@optimizer_switch=@old_opt_switch; @@optimizer_switch=@old_opt_switch 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select "abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def" as col { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select 'abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def' AS `col`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 drop table t1,t2; DROP TABLE t5,t6; set optimizer_trace=default;