mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-04 04:46:15 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/opt_trace.result
bsrikanth-mariadb 2263c8a1f7 MDEV-36461: Optimizer trace: remove join_execution node 
In non-EXPLAIN queries with subqueries, the trace was flooded
with empty "join_execution":{} nodes. Now, they are gone.

The "Range checked for each record" optimization still prints
content into trace on join execution. Now, we wrap it into
"range-checked-for-each-record" to delimit the invocations.
This new object has fields "select_id" which corresponds to
the outer query block, and the "loop" which corresponds to
the  inner query block iteration number. Additionally,
the field  "row_estimation" which itself is an object has
"table", and "range_analysis" fields that were moved
from the old "join_execution"'s steps array.
2025-04-28 01:25:05 -04:00

11083 lines
386 KiB
Text
Raw Permalink Blame History

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"
}
]
}
}
]
}
}
]
} 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"
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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"
}
]
}
}
]
}
}
]
} 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": []
}
]
}
}
]
} 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": []
},
{
"prepare_sum_aggregators": {
"function": "min(t1.d)",
"aggregator_type": "simple"
}
}
]
}
}
]
} 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": []
},
{
"prepare_sum_aggregators": {
"function": "min(t1.a)",
"aggregator_type": "simple"
}
},
{
"prepare_sum_aggregators": {
"function": "max(t1.a)",
"aggregator_type": "simple"
}
}
]
}
}
]
} 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": []
}
]
}
}
]
} 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
}
}
}
]
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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))"
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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)
2269
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": [
2169 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 2269 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",
"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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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"
}
]
}
}
]
}
}
]
} 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
}
]
}
}
]
}
}
]
} 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": []
}
}
]
}
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_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
}
]
}
}
]
}
}
]
} 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;
#
# MDEV-29179 Condition pushdown from HAVING into WHERE is not shown in optimizer trace
#
CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w');
SET optimizer_trace= 'enabled=on';
SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;
b a
h 1
n 4
SELECT json_detailed(json_extract(trace, '$**.steps[*].join_optimization.steps[*].condition_pushdown_from_having') ) exp1, JSON_VALID(trace) exp2 FROM information_schema.optimizer_trace;
exp1 exp2
[
{
"conds": "(t1.b <> 'p' or multiple equal(4, t1.a)) and t1.a <= 7",
"having": null
}
] 1
DROP TABLE t1;
#
# 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
}
]
}
}
]
}
}
]
} 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')) as jd
from information_schema.optimizer_trace;
jd
[
{
"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')) as jd
from information_schema.optimizer_trace;
jd
[
{
"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')) as jd
from information_schema.optimizer_trace;
jd
[
{
"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')) as jd
from information_schema.optimizer_trace;
jd
[
{
"item": "t0.a in ('1','2','3','4','5','6')",
"done": false,
"reason": "type mismatch"
}
]
set in_predicate_conversion_threshold=@tmp;
drop table t0;
#
# MDEV-29298: INSERT ... SELECT Does not produce an optimizer trace
#
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5);
set optimizer_trace=1;
insert into t2 select * from t1 where a<= b and a>4;
select QUERY, LENGTH(trace)>1 from information_schema.optimizer_trace;
QUERY LENGTH(trace)>1
insert into t2 select * from t1 where a<= b and a>4 1
drop table t1, t2;
#
# MDEV-34305 Redundant truncation errors/warnings with optimizer_trace enabled
#
SET @@optimizer_trace='enabled=on';
CREATE TABLE t1 (
a CHAR(2) NOT NULL PRIMARY KEY,
b VARCHAR(20) NOT NULL,
KEY (b)
) CHARSET=utf8mb4;
CREATE TABLE t2 (
a CHAR(2) NOT NULL PRIMARY KEY,
b VARCHAR(20) NOT NULL,
KEY (b)
) CHARSET=utf8mb4;
INSERT INTO t1 VALUES
('AB','MySQLAB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');
INSERT IGNORE INTO t2 VALUES
('AB','Sweden'),
('JA','USA'),
('MS','United States'),
('IB','North America'),
('GO','South America');
UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';
DROP TABLE t1, t2;
#
# 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",
"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
#
# MDEV-36461: Remove join_execution nodes and add range_check_for_each_record when appropriate
#
create table t1 (a int, b int);
insert into t1 values (1, 999),(999, 1),(987,987);
create table t2 (a int, b int, index(a),index(b));
insert into t2 select seq, seq from seq_1_to_1000;
create table t3 (a int);
insert into t3 select seq from seq_1_to_2;
set optimizer_trace='enabled=on';
analyze
select
(
select count(*)
from t1, t2
where t2.a<t1.a and t2.b<t1.b and t2.b+t1.a>t3.a
) as SUBQ, a
from t3;
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 2.00 100.00 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00
2 DEPENDENT SUBQUERY t2 ALL a,b NULL NULL NULL 1000 333.33 100.00 98.60 Range checked for each record (index map: 0x3)
# The trace must contain 6 objects with select_id 2 and
# loop varying from 1..2 for each of the 3 ranges from t1:
set @trace=(select trace from information_schema.optimizer_trace);
set @trace=(select json_extract(@trace, '$**.range-checked-for-each-record'));
set @trace=json_replace(@trace, '$[0].rows_estimation[0].range_analysis','REPLACED');
set @trace=json_replace(@trace, '$[1].rows_estimation[0].range_analysis','REPLACED');
set @trace=json_replace(@trace, '$[2].rows_estimation[0].range_analysis','REPLACED');
set @trace=json_replace(@trace, '$[3].rows_estimation[0].range_analysis','REPLACED');
set @trace=json_replace(@trace, '$[4].rows_estimation[0].range_analysis','REPLACED');
set @trace=json_replace(@trace, '$[5].rows_estimation[0].range_analysis','REPLACED');
select json_detailed(@trace) as TRACE;
TRACE
[
{
"select_id": 2,
"loop": 1,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
},
{
"select_id": 2,
"loop": 1,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
},
{
"select_id": 2,
"loop": 1,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
},
{
"select_id": 2,
"loop": 2,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
},
{
"select_id": 2,
"loop": 2,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
},
{
"select_id": 2,
"loop": 2,
"rows_estimation":
[
{
"table": "t2",
"range_analysis": "REPLACED"
}
]
}
]
# The trace must be empty:
select json_detailed(json_extract(trace, '$**.join_execution'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.join_execution'))
NULL
select
(
select count(*)
from t1, t2
where t2.a+1<t1.a and t2.b+1<t1.b and t2.b+t1.a>t3.a
) as SUBQ, a
from t3;
SUBQ a
985 1
985 2
# The trace must be empty:
select json_detailed(json_extract(trace, '$**.range-checked-for-each-record'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.range-checked-for-each-record'))
NULL
# The trace must be empty:
select json_detailed(json_extract(trace, '$**.join_execution'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.join_execution'))
NULL
set optimizer_trace='enabled=off';
drop table t1,t2,t3;
# End of 10.11 tests
Reference in a new issue View git blame Copy permalink