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 show variables like 'optimizer_trace'; Variable_name Value optimizer_trace enabled=off set optimizer_trace="enabled=on"; show variables like 'optimizer_trace'; Variable_name Value optimizer_trace enabled=on set 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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 1, "cost_for_plan": 2.404394531, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 1, "cost_for_plan": 2.404394531, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 1, "cost_for_plan": 2.404394531, "cost_for_sorting": 1, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", "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": [], "table": "", "best_access_path": { "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 } }, "rows_for_plan": 2, "cost_for_plan": 2.4, "estimated_join_cardinality": 2 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "original_condition": null, "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 select * from information_schema.OPTIMIZER_TRACE; 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": [], "table": "t2", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "estimated_join_cardinality": 10 } ] }, { "best_join_order": ["t2"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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 select * from information_schema.OPTIMIZER_TRACE; 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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "cost_for_sorting": 10, "estimated_join_cardinality": 10 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": [], "table": "", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 12, "estimated_join_cardinality": 10 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "original_condition": null, "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", "field": "a", "equals": "t2.b + 2", "null_rejecting": true }, { "table": "t2", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ { "plan_prefix": ["t1"], "table": "t2", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "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 } }, "rows_for_plan": 100, "cost_for_plan": 242.3759623, "estimated_join_cardinality": 100 } ] }, { "plan_prefix": [], "table": "t2", "best_access_path": { "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 } }, "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ { "plan_prefix": ["t2"], "table": "t1", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "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 } }, "rows_for_plan": 100, "cost_for_plan": 242.3759623, "pruned_by_cost": true } ] } ] }, { "best_join_order": ["t1", "t2"] }, { "attaching_conditions_to_tables": { "original_condition": "t2.a = t1.b and t1.a = t2.b + 2", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 5, "cost_for_plan": 7.25, "estimated_join_cardinality": 5 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 8, "cost_for_plan": 3.8, "cost_for_sorting": 8, "estimated_join_cardinality": 8 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.b = 2 and t1.c = 3", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9, "estimated_join_cardinality": 9 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a >= 20010104e0", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9, "estimated_join_cardinality": 9 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 20010104e0", "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", "field": "a", "equals": "1", "null_rejecting": true }, { "table": "t1", "field": "a", "equals": "1", "null_rejecting": true }, { "table": "t1", "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.1796875 }, { "column_name": "b", "ranges": ["2 <= b <= 2"], "selectivity_from_histogram": 0.015625 } ], "cond_selectivity": 0.021 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "t1", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a_c", "used_range_estimates": true, "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 } }, "rows_for_plan": 21, "cost_for_plan": 25.34242739, "estimated_join_cardinality": 21 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1 and t1.b = 2", "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, "updated_limit": 47, "index_scan_time": 47, "usable": false, "cause": "cost" }, { "index": "a_c", "can_resolve_order": true, "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": [ { "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", "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"], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 2.806835937, "estimated_join_cardinality": 4 } ] }, { "best_join_order": ["t2", "t1"] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "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": [ { "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", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 2.806835937, "rest_of_plan": [ { "plan_prefix": ["t1"], "table": "t2", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 7.606835937, "estimated_join_cardinality": 4 } ] } ] }, { "best_join_order": ["t1", "t2"] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "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": [ { "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", "field": "a", "equals": "t1.a", "null_rejecting": true }, { "table": "t2", "field": "a", "equals": "t3.a", "null_rejecting": true }, { "table": "t3", "field": "a", "equals": "t2.a", "null_rejecting": true }, { "table": "t3", "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"], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 2.806835937, "estimated_join_cardinality": 4 } ] }, { "best_join_order": ["t3", "t2", "t1"] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "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": [], "table": "t10", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "estimated_join_cardinality": 10 } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.606591797, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], "table": "t10", "best_access_path": { "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 } }, "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" } ], "estimated_join_cardinality": 3 } ] }, { "plan_prefix": [], "table": "t10", "best_access_path": { "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 } }, "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", ""] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "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", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "field": "a", "equals": "5", "null_rejecting": true }, { "table": "t1", "field": "pk", "equals": "2", "null_rejecting": true }, { "table": "t1", "field": "a", "equals": "5", "null_rejecting": true }, { "table": "t1", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 1, "cost_for_plan": 0.326073957, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 2.806835937, "estimated_join_cardinality": 4 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 4, "cost_for_plan": 2.806835937, "estimated_join_cardinality": 4 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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) 2183 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": [ 2083 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 2183 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": "select NULL AS `NULL` from 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", "field": "a", "equals": "t1.a", "null_rejecting": true }, { "table": "t1", "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": [], "table": "t0", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ { "plan_prefix": ["t0"], "table": "t1", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "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 } }, "rows_for_plan": 3, "cost_for_plan": 4.948514767, "estimated_join_cardinality": 3 } ] }, { "plan_prefix": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ { "plan_prefix": ["t1"], "table": "t0", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "not better than ref estimates", "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 } }, "rows_for_plan": 6, "cost_for_plan": 5.55027215, "pruned_by_cost": true } ] } ] }, { "best_join_order": ["t0", "t1"] }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = t0.a and t0.a < 3", "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": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1" } ] } }, { "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "derived": { "table": "q", "select_id": 2, "algorithm": "materialized", "cause": "Random function in the select" } }, { "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": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "estimated_join_cardinality": 3 } ] }, { "best_join_order": ["t1"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": [], "table": "", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 3.6, "estimated_join_cardinality": 3 } ] }, { "best_join_order": [""] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "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": [], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "estimated_join_cardinality": 9 } ] }, { "plan_prefix": [], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "pruned_by_heuristic": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1", "t_inner_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "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" } ], "estimated_join_cardinality": 3 } ] }, { "plan_prefix": ["t1"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "best_access_path": { "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 } }, "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", ""] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "semijoin", "converted_to_semi_join": 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": [], "table": "t_outer_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "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"], "table": "t_outer_2", "best_access_path": { "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 } }, "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" ], "table": "t_inner_4", "best_access_path": { "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 } }, "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" ], "table": "t_inner_3", "best_access_path": { "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 } }, "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" } ], "estimated_join_cardinality": 27 } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_3", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "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" ], "table": "t_outer_2", "best_access_path": { "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 } }, "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" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 207.0615234, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, "read_time": 320.4615234 }, { "chosen_strategy": "DuplicateWeedout" } ], "estimated_join_cardinality": 27 } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "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_outer_2", "best_access_path": { "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 } }, "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"], "table": "t_inner_2", "best_access_path": { "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 } }, "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" ], "table": "t_inner_4", "best_access_path": { "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 } }, "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" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 451.8615234, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, "read_time": 565.2615234 }, { "chosen_strategy": "DuplicateWeedout" } ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_2" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 243, "cost_for_plan": 336.4563965, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_4", "best_access_path": { "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 } }, "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" ], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_4" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_3" ], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 611.8461426, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 81, "read_time": 2232.809033 }, { "chosen_strategy": "FirstMatch" } ], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_outer_2", "t_inner_3" ], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 6561, "cost_for_plan": 1486.656396, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_4", "best_access_path": { "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 } }, "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"], "table": "t_outer_2", "best_access_path": { "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 } }, "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" ], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_4", "t_outer_2" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_inner_2", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], "table": "t_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_3", "t_outer_2" ], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 611.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_3", "t_outer_2" ], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 6561, "cost_for_plan": 1486.656396, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 243, "cost_for_plan": 75.23076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_3", "t_inner_4" ], "table": "t_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_3", "t_inner_4" ], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_outer_2", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_3", "best_access_path": { "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 } }, "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" ] }, { "attaching_conditions_to_tables": { "original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a", "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 eq_ref distinct_key distinct_key 4 func 1 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 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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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": "semijoin", "converted_to_semi_join": 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": [], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "estimated_join_cardinality": 27 } ] }, { "plan_prefix": [], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ { "plan_prefix": ["t_inner_4"], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "estimated_join_cardinality": 27 } ] }, { "plan_prefix": [], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "t_outer_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "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"], "table": "t_outer_2", "best_access_path": { "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 } }, "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" ], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 33.74614258, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2", "t_inner_4" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 729, "cost_for_plan": 181.5615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, "read_time": 394.925293 }, { "strategy": "SJ-Materialization", "records": 27, "read_time": 22.26152344 }, { "strategy": "DuplicateWeedout", "records": 27, "read_time": 294.9615234 }, { "chosen_strategy": "SJ-Materialization" } ], "estimated_join_cardinality": 27 } ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_outer_2" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 243, "cost_for_plan": 66.15639648, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_4", "best_access_path": { "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 } }, "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" ], "table": "t_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": [ "t_outer_1", "t_inner_1", "t_inner_2", "t_inner_4" ], "table": "t_inner_3", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "table": "t_inner_3", "best_access_path": { "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 } }, "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_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "pruned_by_cost": true }, { "plan_prefix": ["t_outer_1", "t_inner_1"], "table": "t_inner_4", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { "plan_prefix": ["t_outer_1"], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1"], "table": "t_outer_2", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "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", "best_access_path": { "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 } }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "t_inner_1", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_outer_2", "best_access_path": { "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 } }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_4", "best_access_path": { "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 } }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "t_inner_3", "best_access_path": { "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 } }, "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", "" ] }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t_outer_1", "attached": null }, { "table": "t_inner_1", "attached": null }, { "table": "t_inner_2", "attached": null }, { "table": "", "attached": null }, { "table": "t_outer_2", "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, "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": [ ], "table": "A", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 5.9375, "cost": 2.829589844, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 5.9375, "cost": 2.829589844, "uses_join_buffering": false } }, "rows_for_plan": 5.9375, "cost_for_plan": 4.017089844, "rest_of_plan": [ { "plan_prefix": [ "A" ], "table": "B", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 804.6875, "cost": 256.8548584, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 804.6875, "cost": 256.8548584, "uses_join_buffering": false } }, "rows_for_plan": 4777.832031, "cost_for_plan": 1216.438354, "estimated_join_cardinality": 4777.832031 } ] }, { "plan_prefix": [ ], "table": "B", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 804.6875, "cost": 43.25976562, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 804.6875, "cost": 43.25976562, "uses_join_buffering": false } }, "rows_for_plan": 804.6875, "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": [ ], "table": "A", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 4.017089844, "rest_of_plan": [ { "plan_prefix": [ "A" ], "table": "B", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "b", "used_range_estimates": false, "cause": "not available", "rows": 1, "cost": 20.00585794, "chosen": true }, { "access_type": "scan", "resulting_rows": 804.6875, "cost": 43.25976562, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 20.00585794, "uses_join_buffering": false } }, "rows_for_plan": 10, "cost_for_plan": 26.02294779, "selectivity": 0.8046875, "estimated_join_cardinality": 8.046875 } ] }, { "plan_prefix": [ ], "table": "B", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 804.6875, "cost": 43.25976562, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 804.6875, "cost": 43.25976562, "uses_join_buffering": false } }, "rows_for_plan": 804.6875, "cost_for_plan": 204.1972656, "pruned_by_cost": true } ] ] 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": [ ], "table": "t1", "best_access_path": { "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 } }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "rest_of_plan": [ { "plan_prefix": [ "t1" ], "table": "t2", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "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 } }, "rows_for_plan": 10, "cost_for_plan": 26.0278306, "cost_for_sorting": 10, "estimated_join_cardinality": 10 } ] }, { "plan_prefix": [ ], "table": "t2", "best_access_path": { "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 } }, "rows_for_plan": 100, "cost_for_plan": 22.21972656, "rest_of_plan": [ { "plan_prefix": [ "t2" ], "table": "t1", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "a", "used_range_estimates": false, "cause": "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 } }, "rows_for_plan": 1000, "cost_for_plan": 224.2416992, "pruned_by_cost": true } ] } ] ] 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.22 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.046875 }, { "column_name": "b", "ranges": [ "NULL < b <= 5" ], "selectivity_from_histogram": 0.046875 } ] ] 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 100.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": 1 } ] ] 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.62 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.15625 } ] ] 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; # # 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": [], "table": "seq_1_to_10000000", "best_access_path": { "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 } }, "rows_for_plan": 10000000, "cost_for_plan": 12000000, "estimated_join_cardinality": 10000000 } ] }, { "best_join_order": ["seq_1_to_10000000"] }, { "attaching_conditions_to_tables": { "original_condition": null, "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": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "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 set optimizer_trace='enabled=off';