SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE"; table_name column_name OPTIMIZER_TRACE QUERY OPTIMIZER_TRACE TRACE OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES set optimizer_trace="enabled=on"; show variables like 'optimizer_trace'; Variable_name Value optimizer_trace enabled=on set optimizer_trace="enabled=off"; show variables like 'optimizer_trace'; Variable_name Value optimizer_trace enabled=off create table t1 (a int, b int); insert into t1 values (1,2),(2,3); create table t2 (b int); insert into t2 values (1),(2); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK create function f1 (a int) returns INT return 1; create view v1 as select * from t1 where t1.a=1; create view v2 as select * from t1 where t1.a=1 group by t1.b; set optimizer_trace="enabled=on"; # Mergeable views/derived tables select * from v1; a b 1 2 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from v1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "view": { "table": "v1", "select_id": 2, "algorithm": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } ] } }, { "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t1.a)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [ { "column_name": "a", "ranges": ["1 <= a <= 1"], "selectivity_from_histogram": 0.5 } ], "cond_selectivity": 0.5 }, { "table": "t1", "table_scan": { "rows": 2, "cost": 2.004394531 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 2.204394531, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 2.204394531, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = 1" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.a = 1" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 select * from (select * from t1 where t1.a=1)q; a b 1 2 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from (select * from t1 where t1.a=1)q { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "q", "select_id": 2, "algorithm": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } ] } }, { "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t1.a)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [ { "column_name": "a", "ranges": ["1 <= a <= 1"], "selectivity_from_histogram": 0.5 } ], "cond_selectivity": 0.5 }, { "table": "t1", "table_scan": { "rows": 2, "cost": 2.004394531 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 2.204394531, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 2.204394531, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = 1" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.a = 1" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # Non-Mergeable views select * from v2; a b 1 2 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from v2 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "view": { "table": "v2", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b" } ] } }, { "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "join_optimization": { "select_id": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t1.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t1.a)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [ { "column_name": "a", "ranges": ["1 <= a <= 1"], "selectivity_from_histogram": 0.5 } ], "cond_selectivity": 0.5 }, { "table": "t1", "table_scan": { "rows": 2, "cost": 2.004394531 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 2.204394531, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 2.204394531, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531, "cost_for_sorting": 1 } ] }, { "check_split_materialized": { "not_applicable": "no candidate field can be accessed through ref" } }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = 1" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.a = 1" } ] } } ] } }, { "table_dependencies": [ { "table": "", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "", "table_scan": { "rows": 2, "cost": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 2, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 2, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "", "rows_for_plan": 2, "cost_for_plan": 2.4 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [] } } ] } } ] } 0 0 drop table t1,t2; drop view v1,v2; drop function f1; create table t1(a int, b int); insert into t1 values (0,0),(1,1),(2,1),(3,2),(4,3), (5,3),(6,3),(7,3),(8,3),(9,3); create table t2(a int, b int); insert into t2 values (0,0),(1,1),(2,1),(3,2),(4,3), (5,3),(6,3),(7,3),(8,3),(9,3); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK create view v1 as select a from t1 group by b; create view v2 as select a from t2; # Mergeable view explain select * from v2 ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v2 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "view": { "table": "v2", "select_id": 2, "algorithm": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t2.a AS a from t2" } ] } }, { "expanded_query": "/* select#1 */ select t2.a AS a from v2" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "t2", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t2", "table_scan": { "rows": 10, "cost": 2.021972656 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t2", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } ] }, { "best_join_order": ["t2"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t2", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # Non-Mergeable view explain select * from v1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 10 2 DERIVED t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "view": { "table": "v1", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b" } ] } }, { "expanded_query": "/* select#1 */ select v1.a AS a from v1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "join_optimization": { "select_id": 2, "steps": [ { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 10, "cost": 2.021972656 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "cost_for_sorting": 10 } ] }, { "check_split_materialized": { "not_applicable": "group list has no candidates" } }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "table_dependencies": [ { "table": "", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "", "table_scan": { "rows": 10, "cost": 10 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 10, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 10, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "", "rows_for_plan": 10, "cost_for_plan": 12 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [] } } ] } } ] } 0 0 drop table t1,t2; drop view v1,v2; # # print ref-keyues array # create table t0 (a int); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, key(a)); insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; create table t2(a int, b int, c int , key(a)); insert into t2 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where 1 SIMPLE t2 ref a a 5 test.t1.b 1 Using where select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = t2.b + 2 and t2.a = t1.b", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" }, { "transformation": "constant_propagation", "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t1", "index": "a", "field": "a", "equals": "t2.b + 2", "null_rejecting": true }, { "table": "t2", "index": "a", "field": "a", "equals": "t1.b", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 100, "cost": 2.317382812 } }, { "table": "t2", "table_scan": { "rows": 100, "cost": 2.317382812 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 100, "cost": 2.317382812, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 100, "cost": 2.317382812, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 100, "cost": 2.317382812, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 100, "cost": 2.317382812, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 200.0585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 100, "cost": 2.317382812, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 200.0585794, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 100, "cost_for_plan": 242.3759623 } ] }, { "plan_prefix": [], "table": "t2", "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ { "plan_prefix": ["t2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 200.0585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 100, "cost": 2.317382812, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 200.0585794, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t2"], "table": "t1", "rows_for_plan": 100, "cost_for_plan": 242.3759623, "pruned_by_cost": true, "current_cost": 242.3759623, "best_cost": 242.3759623 } ] } ] }, { "best_join_order": ["t1", "t2"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t2.a = t1.b and t1.a = t2.b + 2" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.b is not null" }, { "table": "t2", "attached": "t1.a = t2.b + 2" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t1,t2,t0; # # group_by min max optimization # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT DISTINCT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT DISTINCT a FROM t1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select distinct t1.a AS a from t1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 65536, "cost": 13255.2 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "a", "usable": true, "key_parts": ["a"] } ], "best_covering_index_scan": { "index": "a", "cost": 13377.39141, "chosen": false, "cause": "cost" }, "group_index_range": { "distinct_query": true, "potential_group_range_indexes": [ { "index": "a", "covering": true, "rows": 5, "cost": 6.25 } ] }, "best_group_range_summary": { "type": "index_group", "index": "a", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 5, "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "a", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 5, "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, "cost_for_plan": 6.25, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "index_merge", "resulting_rows": 5, "cost": 6.25, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 5, "cost": 6.25, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 5, "cost_for_plan": 7.25 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t1; # # With group by , where clause and MIN/MAX function # CREATE TABLE t1 (a INT, b INT, c int, d int, KEY(a,b,c,d)); INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (1,0,1,1), (3,2,3,3), (4,5,4,4); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 20 NULL 8 Using where; Using index for group-by select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.b = 2 and t1.c = 3", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 7, "cost": 5.429052734 }, "potential_range_indexes": [ { "index": "a", "usable": true, "key_parts": ["a", "b", "c", "d"] } ], "best_covering_index_scan": { "index": "a", "cost": 2.409226263, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "potential_group_range_indexes": [ { "index": "a", "covering": true, "ranges": ["(2,3) <= (b,c) <= (2,3)"], "rows": 8, "cost": 2.2 } ] }, "best_group_range_summary": { "type": "index_group", "index": "a", "min_max_arg": "d", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, "rows": 8, "cost": 2.2, "key_parts_used_for_access": ["a", "b", "c"], "ranges": ["(2,3) <= (b,c) <= (2,3)"], "chosen": true }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "a", "min_max_arg": "d", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, "rows": 8, "cost": 2.2, "key_parts_used_for_access": ["a", "b", "c"], "ranges": ["(2,3) <= (b,c) <= (2,3)"] }, "rows_for_plan": 8, "cost_for_plan": 2.2, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "index_merge", "resulting_rows": 8, "cost": 2.2, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", "records": 8, "cost": 2.2, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 8, "cost_for_plan": 3.8, "cost_for_sorting": 8 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.b = 2 and t1.c = 3" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.b = 2 and t1.c = 3" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 DROP TABLE t1; CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)); INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'), (1,'2001-01-03'),(1,'2001-01-04'), (2,'2001-01-01'),(2,'2001-01-02'), (2,'2001-01-03'),(2,'2001-01-04'), (3,'2001-01-01'),(3,'2001-01-02'), (3,'2001-01-03'),(3,'2001-01-04'), (4,'2001-01-01'),(4,'2001-01-02'), (4,'2001-01-03'),(4,'2001-01-04'); set optimizer_trace='enabled=on'; EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a >= 20010104e0", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "t1.a >= 20010104e0" }, { "transformation": "constant_propagation", "resulting_condition": "t1.a >= 20010104e0" }, { "transformation": "trivial_condition_removal", "resulting_condition": "t1.a >= 20010104e0" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 16, "cost": 7.23125 }, "potential_range_indexes": [ { "index": "id", "usable": true, "key_parts": ["id", "a"] } ], "best_covering_index_scan": { "index": "id", "cost": 4.21171589, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "potential_group_range_indexes": [ { "index": "id", "covering": true, "ranges": ["(2001-01-04) <= (a)"], "rows": 9, "cost": 2.35 } ] }, "best_group_range_summary": { "type": "index_group", "index": "id", "min_max_arg": "a", "min_aggregate": true, "max_aggregate": true, "distinct_aggregate": false, "rows": 9, "cost": 2.35, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"], "chosen": true }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "id", "min_max_arg": "a", "min_aggregate": true, "max_aggregate": true, "distinct_aggregate": false, "rows": 9, "cost": 2.35, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"] }, "rows_for_plan": 9, "cost_for_plan": 2.35, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "index_merge", "resulting_rows": 9, "cost": 2.35, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", "records": 9, "cost": 2.35, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a >= 20010104e0" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.a >= 20010104e0" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = 20010104e0", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "t1.a = 20010104e0" }, { "transformation": "constant_propagation", "resulting_condition": "t1.a = 20010104e0" }, { "transformation": "trivial_condition_removal", "resulting_condition": "t1.a = 20010104e0" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 16, "cost": 7.23125 }, "potential_range_indexes": [ { "index": "id", "usable": true, "key_parts": ["id", "a"] } ], "best_covering_index_scan": { "index": "id", "cost": 4.21171589, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "potential_group_range_indexes": [ { "index": "id", "covering": true, "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "rows": 9, "cost": 2.35 } ] }, "best_group_range_summary": { "type": "index_group", "index": "id", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 9, "cost": 2.35, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "chosen": true }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_group", "index": "id", "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, "rows": 9, "cost": 2.35, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"] }, "rows_for_plan": 9, "cost_for_plan": 2.35, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "index_merge", "resulting_rows": 9, "cost": 2.35, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", "records": 9, "cost": 2.35, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = 20010104e0" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": "t1.a = 20010104e0" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t1; # # Late ORDER BY optimization # create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int primary key); insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 ( pk int not null, a int, b int, c int, filler char(100), KEY a_a(c), KEY a_c(a,c), KEY a_b(a,b) ); insert into t1 select a, a,a,a, 'filler-dataaa' from test.one_k; update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where a=1 and b=2 order by c limit 1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.a = 1 and t1.b = 2", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t1", "index": "a_c", "field": "a", "equals": "1", "null_rejecting": true }, { "table": "t1", "index": "a_b", "field": "a", "equals": "1", "null_rejecting": true }, { "table": "t1", "index": "a_b", "field": "b", "equals": "2", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 1000, "cost": 232.5644531 }, "potential_range_indexes": [ { "index": "a_a", "usable": false, "cause": "not applicable" }, { "index": "a_c", "usable": true, "key_parts": ["a", "c"] }, { "index": "a_b", "usable": true, "key_parts": ["a", "b"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a_c", "ranges": ["(1) <= (a) <= (1)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 180, "cost": 216.2943776, "chosen": true }, { "index": "a_b", "ranges": ["(1,2) <= (a,b) <= (1,2)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 21, "cost": 25.36242739, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a_b", "rows": 21, "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, "rows_for_plan": 21, "cost_for_plan": 25.36242739, "chosen": true } } }, { "table": "t1", "rowid_filters": [ { "key": "a_b", "build_cost": 0.886777098, "rows": 21 }, { "key": "a_c", "build_cost": 10.52169992, "rows": 180 } ] }, { "selectivity_for_indexes": [ { "index_name": "a_b", "selectivity_from_index": 0.021 } ], "selectivity_for_columns": [ { "column_name": "a", "ranges": ["1 <= a <= 1"], "selectivity_from_histogram": 0.181 }, { "column_name": "b", "ranges": ["2 <= b <= 2"], "selectivity_from_histogram": 0.021 } ], "cond_selectivity": 0.021 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "ref", "index": "a_c", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 180, "cost": 180.2743776, "chosen": true }, { "access_type": "ref", "index": "a_b", "used_range_estimates": true, "rows": 21, "cost": 21.14242739, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 21, "cost": 21.14242739, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 21, "cost_for_plan": 25.34242739 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = 1 and t1.b = 2" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "fanout": 1, "read_time": 21.14342739, "table": "t1", "rows_estimation": 21, "possible_keys": [ { "index": "a_a", "can_resolve_order": true, "direction": 1, "updated_limit": 47, "index_scan_time": 47, "usable": false, "cause": "cost" }, { "index": "a_c", "can_resolve_order": true, "direction": 1, "updated_limit": 47, "range_scan_time": 4.331020747, "index_scan_time": 4.331020747, "records": 180, "chosen": true }, { "index": "a_b", "can_resolve_order": false, "cause": "not usable index for the query" } ] } }, { "table": "t1", "range_analysis": { "table_scan": { "rows": 1000, "cost": 1.79769e308 }, "potential_range_indexes": [ { "index": "a_a", "usable": false, "cause": "not applicable" }, { "index": "a_c", "usable": true, "key_parts": ["a", "c"] }, { "index": "a_b", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a_c", "ranges": ["(1) <= (a) <= (1)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 180, "cost": 216.2943776, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a_c", "rows": 180, "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, "cost_for_plan": 216.2943776, "chosen": true } } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t1,ten,one_k; # # TABLE ELIMINATION # create table t1 (a int); insert into t1 values (0),(1),(2),(3); create table t0 as select * from t1; create table t2 (a int primary key, b int) as select a, a as b from t1 where a in (1,2); create table t3 (a int primary key, b int) as select a, a as b from t1 where a in (1,3); set optimizer_trace='enabled=on'; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK analyze table t3; Table Op Msg_type Msg_text test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK # table t2 should be eliminated explain select t1.a from t1 left join t2 on t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select t1.a from t1 left join t2 on t1.a=t2.a { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "t2", "resulting_condition": "multiple equal(t1.a, t2.a)" } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "t2", "index": "PRIMARY", "field": "a", "equals": "t1.a", "null_rejecting": true } ] }, { "eliminated_tables": ["t2"] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 4, "cost": 2.006835938 } }, { "table": "t2", "rows": 1, "cost": 1, "table_type": "const" } ] }, { "considered_execution_plans": [ { "plan_prefix": ["t2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4, "cost": 2.006835938, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4, "cost": 2.006835938, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t2"], "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } ] }, { "best_join_order": ["t2", "t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # no tables should be eliminated explain select * from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 left join t2 on t2.a=t1.a { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "t2", "resulting_condition": "multiple equal(t2.a, t1.a)" } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "t2", "index": "PRIMARY", "field": "a", "equals": "t1.a", "null_rejecting": true } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 4, "cost": 2.006835938 } }, { "table": "t2", "table_scan": { "rows": 2, "cost": 2.004394531 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4, "cost": 2.006835938, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4, "cost": 2.006835938, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937, "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4, "chosen": true }, { "access_type": "scan", "resulting_rows": 2, "cost": 8.017578125, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 4, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 4, "cost_for_plan": 7.606835937 } ] } ] }, { "best_join_order": ["t1", "t2"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "t2", "resulting_condition": "t2.a = t1.a" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null }, { "table": "t2", "attached": "trigcond(trigcond(t1.a is not null))" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # multiple tables are eliminated explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "t3", "resulting_condition": "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t2", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": ["0"] }, { "table": "t3", "row_may_be_null": true, "map_bit": 2, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "t2", "index": "PRIMARY", "field": "a", "equals": "t1.a", "null_rejecting": true }, { "table": "t2", "index": "PRIMARY", "field": "a", "equals": "t3.a", "null_rejecting": true }, { "table": "t3", "index": "PRIMARY", "field": "a", "equals": "t2.a", "null_rejecting": true }, { "table": "t3", "index": "PRIMARY", "field": "a", "equals": "t1.a", "null_rejecting": true } ] }, { "eliminated_tables": ["t3", "t2"] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 4, "cost": 2.006835938 } }, { "table": "t2", "rows": 1, "cost": 1, "table_type": "const" }, { "table": "t3", "rows": 1, "cost": 1, "table_type": "const" } ] }, { "considered_execution_plans": [ { "plan_prefix": ["t3", "t2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4, "cost": 2.006835938, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4, "cost": 2.006835938, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t3", "t2"], "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } ] }, { "best_join_order": ["t3", "t2", "t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t0, t1, t2, t3; # # IN subquery to sem-join is traced # create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); insert into t1 values (0,0),(1,1),(2,2); create table t2 as select * from t1; create table t11(a int, b int); create table t10 (pk int, a int); insert into t10 select a,a from t0; create table t12 like t10; insert into t12 select * from t10; analyze table t1,t10; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t10 analyze status Engine-independent statistics collected test.t10 analyze status OK set optimizer_trace='enabled=on'; explain extended select * from t1 where a in (select pk from t10); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t10 ALL NULL NULL NULL NULL 10 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where a in (select pk from t10) { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "join_preparation": { "select_id": 2, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#2 */ select t10.pk from t10" } ] } }, { "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "condition_processing": { "condition": "WHERE", "original_condition": "1 and t1.a = t10.pk", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" }, { "transformation": "constant_propagation", "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(t1.a, t10.pk)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t10", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 3, "cost": 2.006591797 } }, { "table": "t10", "table_scan": { "rows": 10, "cost": 2.021972656 } } ] }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t10", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t10", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.006591797, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.006591797, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t10", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.606591797, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t10", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t1"], "table": "t10", "rows_for_plan": 30, "cost_for_plan": 10.62856445, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 3, "read_time": 10.62856445 }, { "strategy": "SJ-Materialization", "records": 3, "read_time": 5.278564453 }, { "strategy": "DuplicateWeedout", "records": 3, "read_time": 27.12856445 }, { "chosen_strategy": "SJ-Materialization" } ] } ] }, { "plan_prefix": [], "table": "t10", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "fix_semijoin_strategies_for_picked_join_order": [ { "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t10" } ] } ] }, { "best_join_order": ["t1", ""] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null }, { "table": "t10", "attached": null }, { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t0,t1,t11,t10,t12,t2; # # Selectivities for columns and indexes. # create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int, a int, b int, key pk(pk), key pk_a(pk,a), key pk_a_b(pk,a,b)); insert into t1 select a,a,a from t0; ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set @save_use_stat_tables= @@use_stat_tables; set @@optimizer_use_condition_selectivity=4; set @@use_stat_tables= PREFERABLY; set optimizer_trace='enabled=on'; explain select * from t1 where pk = 2 and a=5 and b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref pk,pk_a,pk_a_b pk_a_b 15 const,const,const 1 Using index select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where pk = 2 and a=5 and b=1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t1", "index": "pk", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "index": "pk_a", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "index": "pk_a", "field": "a", "equals": "5", "null_rejecting": true }, { "table": "t1", "index": "pk_a_b", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "index": "pk_a_b", "field": "a", "equals": "5", "null_rejecting": true }, { "table": "t1", "index": "pk_a_b", "field": "b", "equals": "1", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 10, "cost": 6.031738281 }, "potential_range_indexes": [ { "index": "pk", "usable": true, "key_parts": ["pk"] }, { "index": "pk_a", "usable": true, "key_parts": ["pk", "a"] }, { "index": "pk_a_b", "usable": true, "key_parts": ["pk", "a", "b"] } ], "best_covering_index_scan": { "index": "pk_a_b", "cost": 3.010739566, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "pk", "ranges": ["(2) <= (pk) <= (2)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345585794, "chosen": true }, { "index": "pk_a", "ranges": ["(2,5) <= (pk,a) <= (2,5)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345829876, "chosen": false, "cause": "cost" }, { "index": "pk_a_b", "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.346073957, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "pk", "index_scan_cost": 1.000585794, "cumulated_index_scan_cost": 1.000585794, "disk_sweep_cost": 0.90078125, "cumulative_total_cost": 1.901367044, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, "chosen": true }, { "index": "pk_a", "usable": false, "cause": "does not reduce cost of intersect" }, { "index": "pk_a_b", "usable": false, "cause": "does not reduce cost of intersect" } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no clustered pk index" }, "chosen": false, "cause": "cost" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "pk_a_b", "rows": 1, "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, "cost_for_plan": 0.346073957, "chosen": true } } }, { "table": "t1", "rowid_filters": [ { "key": "pk", "build_cost": 0.130585794, "rows": 1 }, { "key": "pk_a", "build_cost": 0.130829876, "rows": 1 }, { "key": "pk_a_b", "build_cost": 0.131073957, "rows": 1 } ] }, { "selectivity_for_indexes": [ { "index_name": "pk_a_b", "selectivity_from_index": 0.1 } ], "selectivity_for_columns": [ { "column_name": "a", "ranges": ["5 <= a <= 5"], "selectivity_from_histogram": 0.1 }, { "column_name": "b", "ranges": ["1 <= b <= 1"], "selectivity_from_histogram": 0.1 } ], "cond_selectivity": 0.1 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "ref", "index": "pk", "used_range_estimates": true, "rows": 1, "cost": 1.125585794, "chosen": true }, { "access_type": "ref", "index": "pk_a", "used_range_estimates": true, "rows": 1, "cost": 1.125829876, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "pk_a_b", "used_range_estimates": true, "rows": 1, "cost": 0.126073957, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.126073957, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, "cost_for_plan": 0.326073957 } ] }, { "best_join_order": ["t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; drop table t0,t1; set optimizer_trace="enabled=off"; # # Tests added to show that sub-statements are not traced # create table t1(a int); insert into t1 values (1),(2),(3),(4); create table t2(a int); insert into t2 values (1),(2),(3),(4); create function f1(a int) returns int begin declare a int default 0; set a= a+ (select count(*) from t2); return a; end| create function f2(a int) returns int begin declare a int default 0; select count(*) from t2 into a; return a; end| Warnings: Warning 1287 ' INTO FROM...' instead set optimizer_trace='enabled=on'; select f1(a) from t1; f1(a) 4 4 4 4 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select f1(a) from t1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select f1(t1.a) AS `f1(a)` from t1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 4, "cost": 2.006835938 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4, "cost": 2.006835938, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4, "cost": 2.006835938, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 select f2(a) from t1; f2(a) 4 4 4 4 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select f2(a) from t1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select f2(t1.a) AS `f2(a)` from t1" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 4, "cost": 2.006835938 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4, "cost": 2.006835938, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4, "cost": 2.006835938, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table t1,t2; drop function f1; drop function f2; set optimizer_trace='enabled=off'; # # MDEV-18489: Limit the memory used by the optimizer trace # create table t1 (a int); insert into t1 values (1),(2); set optimizer_trace='enabled=on'; set @save_optimizer_trace_max_mem_size= @@optimizer_trace_max_mem_size; select * from t1; a 1 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) 2360 set optimizer_trace_max_mem_size=100; select * from t1; a 1 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ 2260 0 set optimizer_trace_max_mem_size=0; select * from t1; a 1 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1 2360 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; # # MDEV-18527: Optimizer trace for DELETE query shows table:null # create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t0 (a int, b int); insert into t0 select a,a from ten; alter table t0 add key(a); set optimizer_trace=1; explain delete from t0 where t0.a<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 range a a 5 NULL 3 Using where select * from information_schema.optimizer_trace; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { "table": "t0", "range_analysis": { "table_scan": { "rows": 10, "cost": 6.021972656 }, "potential_range_indexes": [ { "index": "a", "usable": true, "key_parts": ["a"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a", "ranges": ["(NULL) < (a) < (3)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 3.746757383, "chosen": true } ], "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no join" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a", "rows": 3, "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, "cost_for_plan": 3.746757383, "chosen": true } } } ] } 0 0 drop table ten,t0; set optimizer_trace='enabled=off'; # # MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE # set optimizer_trace=1; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t0 (a int, b int); insert into t0 select a,a from ten; alter table t0 add key(a); create table t1 like t0; insert into t1 select * from t0; explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 range a a 5 NULL 3 Using where 1 SIMPLE t1 ref a a 5 test.t0.a 1 select * from information_schema.optimizer_trace; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "delete from t0,t1 using t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t0.a = t1.a and t1.a < 3", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" }, { "transformation": "constant_propagation", "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" } ] } }, { "table_dependencies": [ { "table": "t0", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t1", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "t0", "index": "a", "field": "a", "equals": "t1.a", "null_rejecting": true }, { "table": "t1", "index": "a", "field": "a", "equals": "t0.a", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "t0", "range_analysis": { "table_scan": { "rows": 10, "cost": 6.021972656 }, "potential_range_indexes": [ { "index": "a", "usable": true, "key_parts": ["a"] } ], "best_covering_index_scan": { "index": "a", "cost": 3.005857945, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a", "ranges": ["(NULL) < (a) < (3)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 3, "cost": 0.746757383, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a", "rows": 3, "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, "cost_for_plan": 0.746757383, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "a", "selectivity_from_index": 0.3 } ], "selectivity_for_columns": [], "cond_selectivity": 0.3 }, { "table": "t1", "range_analysis": { "table_scan": { "rows": 10, "cost": 6.021972656 }, "potential_range_indexes": [ { "index": "a", "usable": true, "key_parts": ["a"] } ], "best_covering_index_scan": { "index": "a", "cost": 3.005857945, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a", "ranges": ["(NULL) < (a) < (3)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 3, "cost": 0.746757383, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a", "rows": 3, "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, "cost_for_plan": 0.746757383, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "a", "selectivity_from_index": 0.3 } ], "selectivity_for_columns": [], "cond_selectivity": 0.3 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t0", "considered_access_paths": [ { "access_type": "range", "resulting_rows": 3, "cost": 0.746757383, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, "cost": 0.746757383, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "range", "resulting_rows": 3, "cost": 0.746757383, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, "cost": 0.746757383, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t0", "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ { "plan_prefix": ["t0"], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "reason": "not better than ref estimates", "rows": 1, "cost": 3.001757383, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 3.001757383, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t0"], "table": "t1", "rows_for_plan": 3, "cost_for_plan": 4.948514767 } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t0", "considered_access_paths": [ { "access_type": "ref", "index": "a", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not better than ref estimates", "rowid_filter_skipped": "worst/max seeks clipping", "rows": 2, "cost": 3.003514767, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 2, "cost": 3.003514767, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t1"], "table": "t0", "rows_for_plan": 6, "cost_for_plan": 5.55027215, "pruned_by_cost": true, "current_cost": 5.55027215, "best_cost": 4.948514767 } ] } ] }, { "best_join_order": ["t0", "t1"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t1.a = t0.a and t0.a < 3" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t0", "attached": "t0.a < 3 and t0.a is not null" }, { "table": "t1", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 drop table ten,t0,t1; set optimizer_trace='enabled=off'; # # Merged to Materialized for derived tables # set optimizer_trace=1; create table t1 (a int); insert into t1 values (1),(2),(3); explain select * from (select rand() from t1)q; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 3 2 DERIVED t1 ALL NULL NULL NULL NULL 3 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from (select rand() from t1)q { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "q", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1" } ] } }, { "expanded_query": "/* select#1 */ select q.`rand()` AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "join_optimization": { "select_id": 2, "steps": [ { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 3, "cost": 2.005126953 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null } ] } } ] } }, { "table_dependencies": [ { "table": "", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "", "table_scan": { "rows": 3, "cost": 3 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 3, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 3, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "", "rows_for_plan": 3, "cost_for_plan": 3.6 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [] } } ] } } ] } 0 0 drop table t1; set optimizer_trace='enabled=off'; # # Semi-join nest # set optimizer_trace=1; create table t1 (a int); insert into t1 values (1),(2),(3); create table t2(a int); insert into t2 values (1),(2),(3),(1),(2),(3),(1),(2),(3); set @save_optimizer_switch= @@optimizer_switch; explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2) { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "join_preparation": { "select_id": 2, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" } ] } }, { "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "condition_processing": { "condition": "WHERE", "original_condition": "1 and t1.a = t_inner_1.a", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)" }, { "transformation": "constant_propagation", "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(t1.a, t_inner_1.a)" } ] } }, { "table_dependencies": [ { "table": "t1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t_inner_1", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "t_inner_2", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_inner_2", "table_scan": { "rows": 3, "cost": 2.005126953 } } ] }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 6.410253906 } ] }, { "plan_prefix": [], "table": "t_inner_2", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "pruned_by_heuristic": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t1"], "table": "t_inner_1", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1", "t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t1", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 3, "read_time": 33.86665039 }, { "strategy": "SJ-Materialization", "records": 3, "read_time": 7.215380859 }, { "strategy": "DuplicateWeedout", "records": 3, "read_time": 18.31538086 }, { "chosen_strategy": "SJ-Materialization" } ] } ] }, { "plan_prefix": ["t1"], "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "fix_semijoin_strategies_for_picked_join_order": [ { "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" }, { "table": "t_inner_2" } ] } ] }, { "best_join_order": ["t1", ""] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", "attached": null }, { "table": "t_inner_1", "attached": null }, { "table": "t_inner_2", "attached": null }, { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # with Firstmatch, mostly for tracing fix_semijoin_strategies_for_picked_join_order set optimizer_switch='materialization=off'; explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) 1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) 1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "join_preparation": { "select_id": 2, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" } ] } }, { "join_preparation": { "select_id": 3, "steps": [ { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" } ] } }, { "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "condition_processing": { "condition": "WHERE", "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" }, { "transformation": "constant_propagation", "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" } ] } }, { "table_dependencies": [ { "table": "t_outer_1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t_outer_2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "t_inner_2", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "t_inner_1", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "t_inner_3", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [] }, { "table": "t_inner_4", "row_may_be_null": false, "map_bit": 5, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t_outer_1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, "cost": 2.005126953 } } ] }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "execution_plan_for_potential_materialization": { "steps": [] } }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t_outer_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_outer_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_1", "rows_for_plan": 81, "cost_for_plan": 28.22563477, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 729, "cost_for_plan": 176.0410156, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 389.4047852 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 289.4410156 }, { "chosen_strategy": "DuplicateWeedout" } ], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 307.6461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 455.4615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 668.825293 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 568.8615234 }, { "chosen_strategy": "DuplicateWeedout" } ] } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 340.0563965, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_4", "rows_for_plan": 243, "cost_for_plan": 78.83076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_4" ], "table": "t_inner_2", "rows_for_plan": 2187, "cost_for_plan": 518.2461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_4", "t_inner_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_4", "t_inner_2" ], "table": "t_inner_3", "rows_for_plan": 19683, "cost_for_plan": 4456.861523, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 9562.749707 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 7413.361523 }, { "chosen_strategy": "FirstMatch" } ], "pruned_by_cost": true, "current_cost": 9562.749707, "best_cost": 568.8615234 } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 2187, "cost_for_plan": 518.2461426, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 176.0410156, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "rows_for_plan": 243, "cost_for_plan": 60.63588867, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 28.22563477, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 60.63588867, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_1", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_outer_2", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_2", "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, "read_time": 285.8410156 }, { "chosen_strategy": "DuplicateWeedout" } ], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2" ], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 304.0461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 451.8615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 665.225293 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 565.2615234 }, { "chosen_strategy": "DuplicateWeedout" } ] } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2" ], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 336.4563965, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 243, "cost_for_plan": 75.23076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4" ], "table": "t_inner_2", "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4", "t_inner_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4", "t_inner_2" ], "table": "t_inner_3", "rows_for_plan": 19683, "cost_for_plan": 4453.261523, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, "read_time": 7409.761523 }, { "chosen_strategy": "DuplicateWeedout" } ], "pruned_by_cost": true, "current_cost": 7409.761523, "best_cost": 565.2615234 } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 3, "read_time": 44.75893555 }, { "strategy": "DuplicateWeedout", "records": 3, "read_time": 37.22563477 }, { "chosen_strategy": "DuplicateWeedout" } ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_outer_2", "rows_for_plan": 27, "cost_for_plan": 44.64101563, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 62.84614258, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 210.6615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 424.025293 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 324.0615234 }, { "chosen_strategy": "DuplicateWeedout" } ] } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 95.25639648, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_4", "rows_for_plan": 9, "cost_for_plan": 41.03076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_outer_2", "rows_for_plan": 81, "cost_for_plan": 59.24614258, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4", "t_outer_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4", "t_outer_2" ], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 207.0615234, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, "read_time": 320.4615234 }, { "chosen_strategy": "DuplicateWeedout" } ] } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 59.24614258, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 44.64101563, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_4", "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_outer_2", "rows_for_plan": 243, "cost_for_plan": 64.43076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_4", "t_outer_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_4", "t_outer_2" ], "table": "t_inner_2", "rows_for_plan": 2187, "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 503.8461426, "best_cost": 320.4615234 }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_4", "t_outer_2" ], "table": "t_inner_3", "rows_for_plan": 2187, "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 503.8461426, "best_cost": 320.4615234 } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_inner_2", "rows_for_plan": 243, "cost_for_plan": 64.43076172, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 64.43076172, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_4", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_outer_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_4", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_3", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "fix_semijoin_strategies_for_picked_join_order": [ { "semi_join_strategy": "DuplicateWeedout" }, { "semi_join_strategy": "DuplicateWeedout" } ] }, { "best_join_order": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4", "t_outer_2", "t_inner_3" ] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t_outer_1", "attached": null }, { "table": "t_inner_1", "attached": "t_inner_1.a = t_outer_1.a" }, { "table": "t_inner_2", "attached": null }, { "table": "t_inner_4", "attached": null }, { "table": "t_outer_2", "attached": null }, { "table": "t_inner_3", "attached": "t_inner_3.a = t_outer_2.a" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 set optimizer_switch='materialization=on'; explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 3 MATERIALIZED t_inner_4 ALL NULL NULL NULL NULL 3 3 MATERIALIZED t_inner_3 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "join_preparation": { "select_id": 2, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" } ] } }, { "join_preparation": { "select_id": 3, "steps": [ { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" } ] } }, { "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } }, { "condition_processing": { "condition": "WHERE", "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" }, { "transformation": "constant_propagation", "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" } ] } }, { "table_dependencies": [ { "table": "t_outer_1", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "t_outer_2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "t_inner_2", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "t_inner_1", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "t_inner_3", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [] }, { "table": "t_inner_4", "row_may_be_null": false, "map_bit": 5, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t_outer_1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, "cost": 2.005126953 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, "cost": 2.015380859 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, "cost": 2.005126953 } } ] }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "semijoin_table_pullout": { "pulled_out_tables": [] } }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } ] }, { "plan_prefix": [], "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t_inner_4", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_4"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_inner_4"], "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } ] }, { "plan_prefix": [], "table": "t_inner_3", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t_outer_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_outer_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_1", "rows_for_plan": 81, "cost_for_plan": 28.22563477, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 729, "cost_for_plan": 176.0410156, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 389.4047852 }, { "strategy": "SJ-Materialization", "records": 27, "read_time": 16.74101562 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 289.4410156 }, { "chosen_strategy": "SJ-Materialization" } ], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 34.94614258, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 182.7615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 396.125293 }, { "strategy": "SJ-Materialization", "records": 27, "read_time": 23.46152344 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 296.1615234 }, { "chosen_strategy": "SJ-Materialization" } ] } ] }, { "plan_prefix": [ "t_outer_1", "t_outer_2", "t_inner_1", "t_inner_2" ], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 67.35639648, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 67.35639648, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_4", "rows_for_plan": 243, "cost_for_plan": 78.83076172, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 78.83076172, "best_cost": 23.46152344 }, { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 176.0410156, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 176.0410156, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "rows_for_plan": 243, "cost_for_plan": 60.63588867, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 60.63588867, "best_cost": 23.46152344 }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 28.22563477, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 28.22563477, "best_cost": 23.46152344 }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 60.63588867, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 60.63588867, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_1", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_outer_2", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 24.62563477, "best_cost": 23.46152344 }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 3, "read_time": 44.75893555 }, { "strategy": "SJ-Materialization", "records": 3, "read_time": 8.125634766 }, { "strategy": "DuplicateWeedout", "records": 3, "read_time": 37.22563477 }, { "chosen_strategy": "SJ-Materialization" } ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_outer_2", "rows_for_plan": 27, "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 3, "cost": 2.005126953, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 3, "cost": 2.005126953, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 33.74614258, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 33.74614258, "best_cost": 23.46152344 }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_3", "rows_for_plan": 243, "cost_for_plan": 66.15639648, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 66.15639648, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_4", "rows_for_plan": 9, "cost_for_plan": 11.93076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } }, { "best_access_path": { "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 9, "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 9, "cost": 2.015380859, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_outer_2", "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 30.14614258, "best_cost": 23.46152344 }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 30.14614258, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_4", "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "pruned_by_cost": true, "current_cost": 24.62563477, "best_cost": 23.46152344 } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_4", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_outer_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_4", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_3", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "fix_semijoin_strategies_for_picked_join_order": [ { "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_4" }, { "table": "t_inner_3" } ] }, { "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" }, { "table": "t_inner_2" } ] } ] }, { "best_join_order": [ "t_outer_1", "t_outer_2", "", "" ] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "1" } }, { "condition_on_constant_tables": "1", "computing_condition": [] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t_outer_1", "attached": null }, { "table": "t_outer_2", "attached": null }, { "table": "t_inner_1", "attached": null }, { "table": "t_inner_2", "attached": null }, { "table": "", "attached": null }, { "table": "t_inner_4", "attached": null }, { "table": "t_inner_3", "attached": null }, { "table": "", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; # # MDEV-18942: Json_writer::add_bool: Conditional jump or move depends on uninitialised value upon # fulltext search under optimizer trace # CREATE TABLE t1 (f VARCHAR(255), FULLTEXT(f)); CREATE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES ('fooba'),('abcde'),('xyzab'); SET optimizer_trace = 'enabled=on'; SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba'); COUNT(*) 1 DROP VIEW v1; DROP TABLE t1; # # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly. # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k (a int); insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; create table t1 ( a int, b int, key a_b(a,b)); insert into t1 select a,a from one_k; set optimizer_trace='enabled=on'; explain select * from t1 force index (a_b) where a=2 and b=4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a_b a_b 10 const,const 1 Using index select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "a_b", "ranges": ["(2,4) <= (a,b) <= (2,4)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.345829876, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] explain select * from t1 where a >= 900 and b between 10 and 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_b a_b 10 NULL 107 Using where; Using index select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "a_b", "ranges": ["(900,10) <= (a,b)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 107, "cost": 21.63379668, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t0,t1; create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ; insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k; explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range start_date start_date 8 NULL 1000 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "start_date", "ranges": ["(2019-02-10,NULL) < (start_date,end_date)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1000, "cost": 1203.877243, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1,one_k; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( a int not null, b int not null, c int not null, d int not null, key a_b_c(a,b,c) ); insert into t1 select a,a, a,a from ten; explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_b_c a_b_c 8 NULL 4 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "a_b_c", "ranges": ["(1) <= (a,b) < (4,50)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4, "cost": 4.948710032, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table ten,t1; # Ported test from MYSQL for ranges involving Binary column CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b)); INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C'); INSERT INTO t1 VALUES (2, NULL); EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.346171589, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] EXPLAIN SELECT * FROM t1 WHERE b IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(NULL) <= (b) <= (NULL)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.346171589, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1; # # MDEV-18880: Optimizer trace prints date in hexadecimal # CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b)); INSERT INTO t1 VALUES (1, 'ab\n'); INSERT INTO t1 VALUES (2, NULL); set optimizer_trace=1; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345927508, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] ALTER TABLE t1 modify column b BINARY(10) AFTER i; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345878692, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] ALTER TABLE t1 modify column b VARBINARY(10) AFTER i; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345927508, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1; CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b)); INSERT INTO t1 VALUES (1, 'ab\n'); INSERT INTO t1 VALUES (2, NULL); EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\n) <= (b) <= (ab\n)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345878692, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1; CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b)); Warnings: Note 1071 Specified key was too long; max key length is 1000 bytes INSERT INTO t1 VALUES (1, 'ab\n'); INSERT INTO t1 VALUES (2, NULL); set optimizer_trace=1; EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 1003 const 1 Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.394255553, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1; CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b)); INSERT INTO t1 VALUES (1, 'ab\n'); INSERT INTO t1 VALUES (2, 'ab\n'); set optimizer_trace=1; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "i_b", "ranges": ["(ab\n) <= (b) <= (ab\n)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2, "cost": 2.546855016, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k (a int); insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ; insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k; explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01'; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["start_date"], "key": "start_date", "key_length": "8", "used_key_parts": ["start_date", "end_date"], "rows": 1000, "filtered": 100, "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'" } } ] } } select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "start_date", "ranges": ["(2019-02-10,NULL) < (start_date,end_date)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1000, "cost": 1203.877243, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] drop table t1, t0, one_k; # # MDEV-19776: Assertion `to_len >= 8' failed in convert_to_printable with optimizer trace enabled # CREATE TABLE t1 (f VARBINARY(16) NOT NULL, KEY(f)); INSERT INTO t1 VALUES ('a'),('b'); SET optimizer_trace = 'enabled=on'; DELETE FROM t1 WHERE f = 'x'; DROP TABLE t1; # # Print cost_for_plan and rows_for_plan for join prefix # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k (a int, b int, key(b)); insert into one_k select A.a + B.a*10 + C.a*100, A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; analyze table t0, one_k persistent for all; Table Op Msg_type Msg_text test.t0 analyze status Engine-independent statistics collected test.t0 analyze status OK test.one_k analyze status Engine-independent statistics collected test.one_k analyze status Table is already up to date set @tmp_jcl=@@join_cache_level; set join_cache_level=0; set optimizer_trace=1; # Check cost/row numbers when multiple tables are joined # (cost_for_plan is the same as best_access_path.cost for single-table SELECTs # but for joins using condition selectivity it is not as trivial. So, # now we are printing it) explain select * from t0 A, one_k B where A.a<5 and B.a<800; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE B ALL NULL NULL NULL NULL 1000 Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "A", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 5, "cost": 3.017089844, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 5, "cost": 3.017089844, "uses_join_buffering": false } } }, { "best_access_path": { "table": "B", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 800, "cost": 44.19726562, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 800, "cost": 44.19726562, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "A", "rows_for_plan": 5, "cost_for_plan": 4.017089844, "rest_of_plan": [ { "plan_prefix": ["A"], "get_costs_for_tables": [ { "best_access_path": { "table": "B", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 800, "cost": 220.9863281, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 800, "cost": 220.9863281, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["A"], "table": "B", "rows_for_plan": 4000, "cost_for_plan": 1025.003418 } ] }, { "plan_prefix": [], "table": "B", "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_heuristic": true } ] ] set join_cache_level=@tmp_jcl; # This shows post-join selectivity explain select * from t0 A, one_k B where A.a=B.b and B.a<800; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE B ref b b 5 test.A.a 1 Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "A", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.017089844, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.017089844, "uses_join_buffering": false } } }, { "best_access_path": { "table": "B", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 800, "cost": 44.19726562, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 800, "cost": 44.19726562, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "A", "rows_for_plan": 10, "cost_for_plan": 4.017089844, "rest_of_plan": [ { "plan_prefix": ["A"], "get_costs_for_tables": [ { "best_access_path": { "table": "B", "considered_access_paths": [ { "access_type": "ref", "index": "b", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 20.00585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 800, "cost": 44.19726562, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 20.00585794, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["A"], "table": "B", "rows_for_plan": 10, "cost_for_plan": 26.02294779, "selectivity": 0.8, "estimated_join_cardinality": 8 } ] }, { "plan_prefix": [], "table": "B", "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_cost": true, "current_cost": 204.1972656, "best_cost": 26.02294779 } ] ] drop table t0, one_k; # # Assertion `to_len >= 8' failed in convert_to_printable # CREATE TABLE t1 ( a blob, KEY (a(255))); insert into t1 values ('foo'), ('bar'); EXPLAIN SELECT * FROM t1 WHERE a= REPEAT('a', 0); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 258 const 1 Using where SELECT * FROM t1 WHERE a= REPEAT('a', 0); a select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ { "range_scan_alternatives": [ { "index": "a", "ranges": ["() <= (a) <= ()"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.357887479, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ] DROP TABLE t1; # # Test for Semi-Join table pullout element # create table t1 (a int primary key, b int); insert into t1 (a) values (1),(2),(3),(4),(5); create table t2 (a int primary key, b int); insert into t2 (a) values (1),(2),(3),(4),(5); create table t3 (a int); insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); explain select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) [ { "pulled_out_tables": [ "t2", "t1" ] } ] drop table t1,t2,t3; # # MDEV-22401: Optimizer trace: multi-component range is not printed correctly # create table t1 (kp1 int, kp2 int, key(kp1, kp2)); insert into t1 values (1,1),(1,5),(5,1),(5,5); set optimizer_trace=1; select * from t1 force index(kp1) where (kp1=2 and kp2 >=4); kp1 kp2 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) [ [ { "index": "kp1", "ranges": ["(2,4) <= (kp1,kp2) <= (2)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.345829876, "chosen": true } ] ] drop table t1; # # MDEV-21626: Optimizer misses the details about the picked join order # CREATE TABLE t1(a INT, b INT, key(a)); INSERT INTO t1 SELECT seq, seq from seq_1_to_10; CREATE TABLE t2(a INT, b INT, key(a)); INSERT INTO t2 SELECT seq, seq from seq_1_to_100; SET OPTIMIZER_TRACE=1; EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where; Using temporary; Using filesort 1 SIMPLE t2 ref a a 5 test.t1.a 1 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": false } } }, { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 100, "cost": 2.219726562, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 100, "cost": 2.219726562, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "rest_of_plan": [ { "plan_prefix": ["t1"], "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 20.00585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 100, "cost": 2.219726562, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 20.00585794, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 10, "cost_for_plan": 26.0278306, "cost_for_sorting": 10 } ] }, { "plan_prefix": [], "table": "t2", "rows_for_plan": 100, "cost_for_plan": 22.21972656, "rest_of_plan": [ { "plan_prefix": ["t2"], "get_costs_for_tables": [ { "best_access_path": { "table": "t1", "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "reason": "not available", "rows": 1, "cost": 200.0585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 10, "cost": 2.021972656, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10, "cost": 2.021972656, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t2"], "table": "t1", "rows_for_plan": 1000, "cost_for_plan": 224.2416992, "pruned_by_cost": true, "current_cost": 224.2416992, "best_cost": 36.0278306 } ] } ] ] DROP TABLE t1,t2; # # MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when # optimizer_use_condition_selectivity >2 # CREATE TABLE t1(a INT, b INT); INSERT INTO t1 SELECT seq, seq from seq_1_to_100; SET optimizer_trace=1; ANALYZE TABLE t1 PERSISTENT FOR ALL; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 0.25 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ [ { "column_name": "a", "ranges": ["1 <= a <= 5"], "selectivity_from_histogram": 0.05 }, { "column_name": "b", "ranges": ["NULL < b <= 5"], "selectivity_from_histogram": 0.05 } ] ] EXPLAIN EXTENDED SELECT * from t1 WHERE a != 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 99.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` <> 5 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ [ { "column_name": "a", "ranges": [ "NULL < a < 5", "5 < a" ], "selectivity_from_histogram": 0.99 } ] ] EXPLAIN EXTENDED SELECT * from t1 WHERE b >= 10 and b < 25; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 15.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 10 and `test`.`t1`.`b` < 25 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ [ { "column_name": "b", "ranges": ["10 <= b < 25"], "selectivity_from_histogram": 0.15 } ] ] drop table t1; # # MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name # (on optimized builds) # CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) ); SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101; sum(b) row_number() OVER (order by b) NULL 1 UPDATE t1 SET b=10 WHERE a=1; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) [ [ { "index": "PRIMARY", "ranges": ["(1) <= (a) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 0, "cost": 0.145, "chosen": true } ] ] DROP TABLE t1; set optimizer_trace='enabled=off'; # # MDEV-24975 Server consumes extra 4G memory upon querying INFORMATION_SCHEMA.OPTIIMIZER_TRACE # set max_session_mem_used=1024*1024*1024; select count(*) from information_schema.optimizer_trace; select * from information_schema.optimizer_trace; set max_session_mem_used=default; # # MDEV-22380 Assertion `name.length == strlen(name.str)' failed in Item::print_item_w_name on SELECT w/ optimizer_trace enabled # SET optimizer_trace="enabled=on"; SELECT 'a\0' LIMIT 0; a\x00 SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; query trace SELECT 'a\0' LIMIT 0 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select 'a\0' AS `a\x00` limit 0" } ] } }, { "join_optimization": { "select_id": 1, "steps": [] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } SET optimizer_trace=DEFAULT; # # MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object # CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY; SET optimizer_trace=1; INSERT INTO t1 VALUES (0,0); SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); a DROP TABLE t1; # # MDEV-31085: multi-update using view with optimizer trace enabled # SET SESSION optimizer_trace = 'enabled=on'; CREATE TABLE t (a int, b int); CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; INSERT INTO t VALUES (0,4),(5,6); UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; SELECT * FROM information_schema.optimizer_trace; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { "steps": [ { "view": { "table": "v", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 AS c" } ] } }, { "join_preparation": { "select_id": 3, "steps": [ { "expanded_query": "/* select#3 */ select 2 AS c" } ] } }, { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "v.c < t.a", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "v.c < t.a" }, { "transformation": "constant_propagation", "resulting_condition": "v.c < t.a" }, { "transformation": "trivial_condition_removal", "resulting_condition": "v.c < t.a" } ] } }, { "join_optimization": { "select_id": 2, "steps": [] } }, { "join_optimization": { "select_id": 3, "steps": [] } }, { "table_dependencies": [ { "table": "t", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "t", "table_scan": { "rows": 2, "cost": 2.004394531 } }, { "table": "", "table_scan": { "rows": 2, "cost": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 2.004394531, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 2.004394531, "uses_join_buffering": false } } }, { "best_access_path": { "table": "", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 2, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 2, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t", "rows_for_plan": 2, "cost_for_plan": 2.404394531, "rest_of_plan": [ { "plan_prefix": ["t"], "get_costs_for_tables": [ { "best_access_path": { "table": "", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 2, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 2, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t"], "table": "", "rows_for_plan": 4, "cost_for_plan": 5.204394531 } ] }, { "plan_prefix": [], "table": "", "rows_for_plan": 2, "cost_for_plan": 2.4, "rest_of_plan": [ { "plan_prefix": [""], "get_costs_for_tables": [ { "best_access_path": { "table": "t", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 2.004394531, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 2.004394531, "uses_join_buffering": true } } } ] }, { "plan_prefix": [""], "table": "t", "rows_for_plan": 4, "cost_for_plan": 5.204394531, "pruned_by_cost": true, "current_cost": 5.204394531, "best_cost": 5.204394531 } ] } ] }, { "best_join_order": ["t", ""] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "v.c < t.a" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t", "attached": null }, { "table": "", "attached": "v.c < t.a" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [] } }, { "join_execution": { "select_id": 3, "steps": [] } }, { "join_preparation": { "select_id": "fake", "steps": [ { "expanded_query": "select c AS c from dual" } ] } }, { "join_optimization": { "select_id": "fake", "steps": [ { "table_dependencies": [ { "table": "union", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "union", "table_scan": { "rows": 2, "cost": 10.1 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "union", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 2, "cost": 10.1, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 2, "cost": 10.1, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "union", "rows_for_plan": 2, "cost_for_plan": 10.5 } ] }, { "best_join_order": ["union"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "union", "attached": null } ] } } ] } }, { "join_execution": { "select_id": "fake", "steps": [] } } ] } } ] } 0 0 SELECT * FROM t; a b 0 4 1 5 SET optimizer_trace=DEFAULT; DROP VIEW v; DROP TABLE t; # # MDEV-26301: Split optimization improvements: Optimizer Trace coverage # create table t1(a int, b int); insert into t1 select seq,seq from seq_1_to_5; create table t2(a int, b int, key(a)); insert into t2 select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; create table t3(a int, b int, key(a)); insert into t3 select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; analyze table t1,t2,t3 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date test.t3 analyze status Engine-independent statistics collected test.t3 analyze status Table is already up to date create table t10 ( grp_id int, col1 int, key(grp_id) ); insert into t10 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B; create table t11 ( col1 int, col2 int ); insert into t11 select A.seq, A.seq from seq_1_to_10 A; analyze table t10,t11 persistent for all; Table Op Msg_type Msg_text test.t10 analyze status Engine-independent statistics collected test.t10 analyze status Table is already up to date test.t11 analyze status Engine-independent statistics collected test.t11 analyze status OK set optimizer_trace=1; explain select * from ( (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where 1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where 1 PRIMARY ref key0 key0 5 test.t1.b 10 Using where 2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS from information_schema.optimizer_trace; JS [ { "split_candidates": ["t10.grp_id"] } ] select json_detailed( json_remove( json_extract(trace, '$**.choose_best_splitting') , '$[0].split_plan_search[0]' ) ) as JS from information_schema.optimizer_trace; JS [ { "considered_keys": [ { "table_name": "t10", "index": "grp_id", "rec_per_key": 100, "param_tables": 1 } ], "refills": 5, "spl_pd_boundary": 2, "split_plan_search": [], "lead_table": "t10", "index": "grp_id", "parts": 1, "split_sel": 0.001, "cost": 2535.968504, "unsplit_cost": 253440.0075, "records": 100, "chosen": true } ] drop table t1,t2,t3,t10,t11; set optimizer_trace=DEFAULT; # # End of 10.4 tests # set optimizer_trace='enabled=on'; # # Test many rows to see output of big cost numbers # select count(*) from seq_1_to_10000000; count(*) 10000000 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select count(*) from seq_1_to_10000000 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "table_dependencies": [ { "table": "seq_1_to_10000000", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "seq_1_to_10000000", "table_scan": { "rows": 10000000, "cost": 10000000 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "seq_1_to_10000000", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 10000000, "cost": 10000000, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 10000000, "cost": 10000000, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "seq_1_to_10000000", "rows_for_plan": 10000000, "cost_for_plan": 12000000 } ] }, { "best_join_order": ["seq_1_to_10000000"] }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "seq_1_to_10000000", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 0 0 # # MDEV-22891: Optimizer trace: const tables are not clearly visible # create table t0(a int primary key); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (pk int primary key, a int); insert into t1 select a,a from t0; create table t2 (pk int primary key, a int); insert into t2 select a,a from t0; create table t3 (pk int primary key, a int); insert into t3 select a,a from t0; explain select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition 1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) from information_schema.optimizer_trace; JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) [ { "members": [ "t3", "t2" ] } ] drop table t0, t1, t2, t3; # # MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace # create table t0 (a int); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); set @tmp=@@in_predicate_conversion_threshold; set in_predicate_conversion_threshold=3; explain select * from t0 where a in (1,2,3,4,5,6); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY ref key0 key0 4 test.t0.a 2 FirstMatch(t0) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ { "item": "t0.a in (1,2,3,4,5,6)", "conversion": [ { "join_preparation": { "select_id": 2, "steps": [ { "derived": { "table": "tvc_0", "select_id": 3, "algorithm": "materialized" } }, { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } }, { "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0" } ] } } ] } ] explain select * from t0 where a in (1,2,3,4,5,a+1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ { "item": "t0.a in (1,2,3,4,5,t0.a + 1)", "done": false, "reason": "non-constant element in the IN-list" } ] explain select * from t0 where a in ('1','2','3','4','5','6'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ { "item": "t0.a in ('1','2','3','4','5','6')", "done": false, "reason": "type mismatch" } ] set in_predicate_conversion_threshold=@tmp; drop table t0; # # End of 10.5 tests # # # MDEV-23677: Optimizer trace ... (test coverage) # create table t1(a int, b int, c int, primary key (a,b,c)); insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); create table t2 (a int, b int); insert into t2 values (1,1),(2,2); create table t3 (a int, b int, c int); insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t3.a,test.t2.b 1 Using index set @trace=(select trace from information_schema.optimizer_trace); set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); select @sub_path; @sub_path $.steps[1].join_optimization.steps[4].considered_execution_plans[1].rest_of_plan[0].get_costs_for_tables[0] select json_detailed(json_extract( @trace, concat(@sub_path,'.best_access_path.considered_access_paths[0]') )) as S; S { "access_type": "ref", "index": "PRIMARY", "rows": 1.79769e308, "cost": 1.79769e308, "chosen": false, "cause": "no predicate for first keypart" } drop table t1,t2,t3; # # MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field # create table t1 (a int, b int, c int); insert into t1 values (1,1,1),(2,2,2); create table t2 as select * from t1; insert into t2 select * from t2; create table t3 as select * from t2; insert into t3 select * from t3; # Check how HAVING is printed explain select a,b, count(*) from t1 where a=3 group by b,b having a+b < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort select json_detailed(json_extract(trace, '$**.substitute_best_equal')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.substitute_best_equal')) [ { "condition": "WHERE", "resulting_condition": "t1.a = 3" }, { "condition": "HAVING", "resulting_condition": "t1.a + t1.b < 10" } ] # Check ON expression explain select * from t1 left join t2 on t2.a=t1.a and t2.a<3 where t1.b > 5555; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) select json_detailed(json_extract(trace, '$**.substitute_best_equal')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.substitute_best_equal')) [ { "condition": "WHERE", "resulting_condition": "t1.b > 5555" }, { "condition": "ON expr", "attached_to": "t2", "resulting_condition": "t2.a = t1.a and t1.a < 3" } ] # Check nested ON expression explain select * from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000 where t1.b > 5555; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) select json_detailed(json_extract(trace, '$**.substitute_best_equal')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.substitute_best_equal')) [ { "condition": "WHERE", "resulting_condition": "t1.b > 5555" }, { "condition": "ON expr", "attached_to": "t2", "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" } ] # The next query is test for: # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing select json_detailed(json_extract(trace, '$**.condition_processing')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.condition_processing')) [ { "condition": "WHERE", "original_condition": "t1.b > 5555", "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "t3", "resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)" } }, { "transformation": "equality_propagation", "resulting_condition": "t1.b > 5555" }, { "transformation": "constant_propagation", "resulting_condition": "t1.b > 5555" }, { "transformation": "trivial_condition_removal", "resulting_condition": "t1.b > 5555" } ] } ] drop table t1,t2,t3; # # MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED # create table t1 (a int, b int, index idx_b(b)) engine=myisam; insert into t1 values (8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3), (9,3), (8,1), (4,5), (2,3); create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam; insert into t2 values (7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'), (4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'), (11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'), (17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'), (24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'), (31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'), (37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'), (54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'), (61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v'); insert into t2 select a+10, b+10, concat(c,'f') from t2; analyze table t1,t2; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain select t1.a,t.s,t.m from t1 join (select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t on t1.a=t.a where t1.b < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range idx_b idx_b 5 NULL 4 Using index condition; Using where 1 PRIMARY ref key0 key0 5 test.t1.a 2 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 1 select json_detailed(json_extract(trace, '$**.choose_best_splitting')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.choose_best_splitting')) [ { "considered_keys": [] }, { "considered_keys": [ { "table_name": "t2", "index": "idx_a", "rec_per_key": 1.8367, "param_tables": 1 } ], "refills": 4, "spl_pd_boundary": 2, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t2", "considered_access_paths": [ { "access_type": "ref", "index": "idx_a", "used_range_estimates": false, "reason": "not available", "rows": 1.8367, "cost": 2.000585794, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1.8367, "cost": 2.000585794, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t2", "rows_for_plan": 1.8367, "cost_for_plan": 2.367925794, "cost_for_sorting": 1.8367 } ] } ], "lead_table": "t2", "index": "idx_a", "parts": 1, "split_sel": 0.020407778, "cost": 2.488945919, "unsplit_cost": 25.72361682, "records": 1, "chosen": true } ] drop table t1,t2; # # Test table functions. # CREATE TABLE t1(id INT, f1 JSON); INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'), (4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}'); SELECT * FROM t1 WHERE id IN (SELECT id FROM t1 as tt2, JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); id f1 1 {"1": 1} 2 {"1": 2} 3 {"1": 3} 4 {"1": 4} 5 {"1": 5} 6 {"1": 6} select json_detailed(json_extract(trace, '$**.best_join_order')) from information_schema.OPTIMIZER_TRACE; json_detailed(json_extract(trace, '$**.best_join_order')) [ [ "t1", "" ] ] DROP TABLE t1; # # MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace # set optimizer_trace=0; set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; seq 1 # The trace must not be empty: select left(trace, 100) from information_schema.optimizer_trace; left(trace, 100) { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ set optimizer_trace='enabled=off'; # End of 10.6 tests