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