5716 lines
199 KiB
Plaintext
5716 lines
199 KiB
Plaintext
set optimizer_trace_max_mem_size=1048576;
|
|
set end_markers_in_json=on;
|
|
set optimizer_trace="enabled=on";
|
|
# check that if a sub-statement should not be traced,
|
|
# it is not traced even if inside a traced top statement
|
|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
create function f1(arg char(1)) returns int
|
|
begin
|
|
declare res int;
|
|
declare dummy varchar(1);
|
|
select 1 into res from dual;
|
|
select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
|
|
select 2 into res from dual;
|
|
return 3;
|
|
end|
|
|
select f1("c")|
|
|
f1("c")
|
|
3
|
|
|
|
select * from information_schema.OPTIMIZER_TRACE|
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select f1("c") {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
select f1("c") {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set res@1 NULL {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set dummy@2 NULL {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
select 1 into res from dual {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
select 2 into res from dual {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select 2 AS `2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
freturn 3 3 {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
|
|
drop function f1;
|
|
# check that if a tracing gets disabled in a routine's body,
|
|
# substatements are not traced
|
|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
create function f1(arg char(1)) returns int
|
|
begin
|
|
declare res int;
|
|
declare dummy varchar(1);
|
|
set optimizer_trace="enabled=off";
|
|
select 1 into res from dual;
|
|
select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
|
|
select 2 into res from dual;
|
|
return 3;
|
|
end|
|
|
select f1("c")|
|
|
f1("c")
|
|
3
|
|
|
|
select * from information_schema.OPTIMIZER_TRACE|
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select f1("c") {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
select f1("c") {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set res@1 NULL {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set dummy@2 NULL {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
|
|
select @@optimizer_trace;
|
|
@@optimizer_trace
|
|
enabled=off,one_line=off
|
|
set optimizer_trace="enabled=on";
|
|
drop function f1;
|
|
|
|
# Check that if a sub-statement reads OPTIMIZER_TRACE,
|
|
# thus reading the unfinished trace of its caller statement,
|
|
# there is no crash.
|
|
|
|
create temporary table optt
|
|
(id int primary key auto_increment,
|
|
QUERY varchar(200),
|
|
TRACE text);
|
|
create table t1 (a int, key(a));
|
|
insert into t1 values(2);
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
create function f1(arg char(1)) returns int
|
|
begin
|
|
declare res int;
|
|
insert into optt select NULL, QUERY, TRACE from information_schema.OPTIMIZER_TRACE;
|
|
return 3;
|
|
end|
|
|
select * from t1 where a in (select f1("c") from t1)|
|
|
a
|
|
|
|
set optimizer_trace="enabled=off";
|
|
this should find unfinished traces
|
|
select count(*) from optt where TRACE NOT LIKE "%] /* steps */\n}";
|
|
count(*)
|
|
1
|
|
select count(*)<>0 from optt;
|
|
count(*)<>0
|
|
1
|
|
this should not
|
|
select count(*) from information_schema.OPTIMIZER_TRACE where TRACE NOT LIKE "%] /* steps */\n}";
|
|
count(*)
|
|
0
|
|
select count(*)<>0 from information_schema.OPTIMIZER_TRACE;
|
|
count(*)<>0
|
|
1
|
|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
|
|
drop temporary table optt;
|
|
drop function f1;
|
|
drop table t1;
|
|
set optimizer_trace="enabled=on";
|
|
|
|
# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
|
|
|
|
create table t1(a int, b int);
|
|
create view v1 as select a from t1;
|
|
select VIEW_DEFINITION from information_schema.VIEWS
|
|
where TABLE_SCHEMA="test" and TABLE_NAME="v1";
|
|
VIEW_DEFINITION
|
|
select `test`.`t1`.`a` AS `a` from `test`.`t1`
|
|
drop table t1;
|
|
drop view v1;
|
|
|
|
# check for readable display of BIT values
|
|
|
|
create table t1 (a bit(5), key(a));
|
|
insert into t1 values(b'00000'),(b'01101');
|
|
select cast(a as unsigned) from t1 where a > b'01100';
|
|
cast(a as unsigned)
|
|
13
|
|
select TRACE from information_schema.OPTIMIZER_TRACE;
|
|
TRACE
|
|
{
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select cast(`t1`.`a` as unsigned) AS `cast(a as unsigned)` from `t1` where (`t1`.`a` > 0x0c)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` > 0x0c)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 0x0c)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 0x0c)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`a` > 0x0c)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8009
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"a"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "a",
|
|
"cost": 0.4544,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": false,
|
|
"cause": "prefix_not_const_equality"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "a",
|
|
"ranges": [
|
|
"12 < a"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2,
|
|
"cost": 0.4644,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7009,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7009,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`a` > 0x0c)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`a` > 0x0c)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`a` > 0x0c)",
|
|
"final_table_condition ": "(`t1`.`a` > 0x0c)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
}
|
|
drop table t1;
|
|
|
|
# check that trace lists all pushed down ON conditions
|
|
|
|
create table t1 (i int not null);
|
|
insert into t1 values (0), (2),(3),(4);
|
|
create table t2 (i int not null);
|
|
insert into t2 values (0),(1), (3),(4);
|
|
create table t3 (i int not null);
|
|
insert into t3 values (0),(1),(2), (4);
|
|
select * from
|
|
t1 LEFT JOIN
|
|
( t2 LEFT JOIN
|
|
( t3
|
|
)
|
|
ON t3.i = t2.i
|
|
)
|
|
ON t2.i = t1.i
|
|
WHERE t3.i IS NULL
|
|
;
|
|
i i i
|
|
3 3 NULL
|
|
2 NULL NULL
|
|
select TRACE from information_schema.OPTIMIZER_TRACE;
|
|
TRACE
|
|
{
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`i` AS `i`,`t2`.`i` AS `i`,`t3`.`i` AS `i` from `t1` left join (`t2` left join `t3` on((`t3`.`i` = `t2`.`i`))) on((`t2`.`i` = `t1`.`i`)) where (`t3`.`i` is null)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t3`.`i` is null)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t3`.`i` is null)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t3`.`i` is null)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t3`.`i` is null)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 0.5017
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 0.5017
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 0.5017
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 4,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 4,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 16,
|
|
"cost_for_plan": 3.0034,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.25,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 1,
|
|
"cost": 2.4021,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 16,
|
|
"cost_for_plan": 5.4056,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t3`.`i` is null)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)"
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"attached": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)",
|
|
"final_table_condition ": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)"
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"original_table_condition": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))",
|
|
"final_table_condition ": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
},
|
|
{
|
|
"table": "`t3`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
}
|
|
drop table t1,t2,t3;
|
|
|
|
# test of tracing a query with an HAVING condition, in
|
|
# ps-protocol, does not crash
|
|
|
|
CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
|
|
INSERT INTO t1 VALUES (16,'f');
|
|
INSERT INTO t1 VALUES (16,'f');
|
|
CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
|
|
INSERT INTO t2 VALUES (13,'f');
|
|
INSERT INTO t2 VALUES (20,'f');
|
|
CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
|
|
INSERT INTO t3 VALUES (7,'f');
|
|
EXPLAIN SELECT t1.f2 FROM t1
|
|
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
|
|
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
|
|
ORDER BY f2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (Block Nested Loop)
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = `test`.`t3`.`f2`) having true order by `test`.`t1`.`f2`
|
|
SELECT t1.f2 FROM t1
|
|
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
|
|
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
|
|
ORDER BY f2;
|
|
f2
|
|
f
|
|
f
|
|
f
|
|
f
|
|
select TRACE from information_schema.OPTIMIZER_TRACE;
|
|
TRACE
|
|
{
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "IN (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`f2` AS `f2` from `t1` join `t2` join `t3` where ((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`)) having <in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))) is false) order by `t1`.`f2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal(`t3`.`f2`, `t2`.`f2`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"condition_processing": {
|
|
"condition": "HAVING",
|
|
"original_condition": "<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))) is false)",
|
|
"steps": [
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))) is false)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "HAVING",
|
|
"original_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5024
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7024,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7024,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5024
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5024
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 0.5012
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7024,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7024,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 1,
|
|
"cost": 0.7012,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.4037,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 2,
|
|
"cost": 0.9025,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 2.3062,
|
|
"sort_cost": 4,
|
|
"new_cost_for_plan": 6.3062,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 2,
|
|
"cost": 0.9025,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 1.6049,
|
|
"pruned_by_heuristic": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t2`.`f2` = `t3`.`f2`)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(`t2`.`f2` = `t3`.`f2`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`t1`.`f2`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`f2`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`f2`"
|
|
} /* simplifying_order_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(`t2`.`f2` = `t3`.`f2`)",
|
|
"final_table_condition ": "(`t2`.`f2` = `t3`.`f2`)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
},
|
|
{
|
|
"table": "`t3`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 3,
|
|
"write_method": "write_all_rows"
|
|
},
|
|
{
|
|
"adding_sort_to_table_in_plan_at_position": 3
|
|
} /* filesort */
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"sorting_table_in_plan_at_position": 3,
|
|
"filesort_information": [
|
|
{
|
|
"direction": "asc",
|
|
"field": "f2"
|
|
}
|
|
] /* filesort_information */,
|
|
"filesort_priority_queue_optimization": {
|
|
"usable": false,
|
|
"cause": "not applicable (no LIMIT)"
|
|
} /* filesort_priority_queue_optimization */,
|
|
"filesort_execution": [
|
|
] /* filesort_execution */,
|
|
"filesort_summary": {
|
|
"memory_available": 262144,
|
|
"key_size": "XXX",
|
|
"row_size": "XXX",
|
|
"max_rows_per_buffer": "XXX",
|
|
"num_rows_estimate": 18446744073709551615,
|
|
"num_rows_found": 4,
|
|
"num_initial_chunks_spilled_to_disk": 0,
|
|
"peak_memory_used": "NNN",
|
|
"sort_algorithm": "std::sort",
|
|
"unpacked_addon_fields": "skip_heuristic",
|
|
"sort_mode": "<varlen_sort_key, additional_fields>"
|
|
} /* filesort_summary */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
}
|
|
DROP TABLES t1,t2,t3;
|
|
|
|
# Test that tracing a query with a materialized FROM-clause
|
|
# derived table using a GROUP BY, does not crash
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 values (1,1), (2,null), (3, 4);
|
|
select max(x) from (select sum(a) as x from t1 group by b) as teeone;
|
|
max(x)
|
|
3
|
|
select TRACE from information_schema.OPTIMIZER_TRACE;
|
|
TRACE
|
|
{
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": " `teeone`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select max(`teeone`.`x`) AS `max(x)` from (/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`) `teeone`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5016
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"access_type": "scan",
|
|
"resulting_rows": 3,
|
|
"cost": 0.8016,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 3,
|
|
"cost_for_plan": 0.8016,
|
|
"sort_cost": 3,
|
|
"new_cost_for_plan": 3.8016,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`b`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`b`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`b`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 1,
|
|
"write_method": "continuously_update_group_row"
|
|
}
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": " `teeone`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": " `teeone`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.5375
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": " `teeone`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"access_type": "scan",
|
|
"resulting_rows": 3,
|
|
"cost": 2.8375,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 3,
|
|
"cost_for_plan": 2.8375,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": " `teeone`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": " `teeone`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": " `teeone`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 1,
|
|
"row_length": 16,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"temp_table_aggregate": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 2,
|
|
"row_length": 22,
|
|
"key_length": 5,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
}
|
|
] /* steps */
|
|
} /* temp_table_aggregate */
|
|
}
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
}
|
|
drop table t1;
|
|
|
|
# To have no crash above, we had to restore the ref_array at
|
|
# end of JOIN::exec(). This impacts how the query looks like,
|
|
# but not too much, as seen in the error message below.
|
|
# Comes from func_gconcat.test.
|
|
|
|
CREATE TABLE t1(f1 int);
|
|
INSERT INTO t1 values (0),(0);
|
|
set optimizer_trace="enabled=off";
|
|
SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
|
|
ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
|
|
set optimizer_trace="enabled=on";
|
|
SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
|
|
ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
|
|
DROP TABLE t1;
|
|
|
|
# Check that SQL PREPARE and SQL EXECUTE each produce one trace.
|
|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
prepare stmt from "select 1";
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select 1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
execute stmt;
|
|
1
|
|
1
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select 1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
deallocate prepare stmt;
|
|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
|
|
|
|
# Test of SELECTs in IF in stored routine.
|
|
# Same test for CASE WHEN.
|
|
|
|
create table t1 (a int);
|
|
create procedure p1()
|
|
begin
|
|
if exists(select 1) then
|
|
insert into t1 values(1);
|
|
end if;
|
|
if exists(select 2) then
|
|
insert into t1 values(2);
|
|
end if;
|
|
if (select count(*) from t1) then
|
|
insert into t1 values(3);
|
|
end if;
|
|
set @a=(select count(a) from t1 where a>0);
|
|
case (select count(a) from t1 where a>1)
|
|
when 2 then set @b=2;
|
|
else set @b=3;
|
|
end case;
|
|
end|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
set @old_max=@@optimizer_trace_max_mem_size;
|
|
set optimizer_trace_max_mem_size=40000;
|
|
call p1();
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
set @old_max=@@optimizer_trace_max_mem_size {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_trace_max_mem_size=40000 {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
call p1() {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
call p1() {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
jump_if_not 2(2) exists(select 1) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 1"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "EXISTS (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
insert into t1 values(1) {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
jump_if_not 4(4) exists(select 2) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 2"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "EXISTS (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
insert into t1 values(2) {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
jump_if_not 6(6) (select count(0) from `test`.`t1`) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select count(0) from `t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "Select tables optimized away"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
insert into t1 values(3) {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
SET @a=(select count(a) from t1 where a>0) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 0)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` > 0)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 0)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 0)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`a` > 0)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5013
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.3333,
|
|
"access_type": "scan",
|
|
"resulting_rows": 1,
|
|
"cost": 0.8013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.8013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`a` > 0)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`a` > 0)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`a` > 0)",
|
|
"final_table_condition ": "(`t1`.`a` > 0)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1)) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 1)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` > 1)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 1)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`a` > 1)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`a` > 1)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5013
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.3333,
|
|
"access_type": "scan",
|
|
"resulting_rows": 1,
|
|
"cost": 0.8013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.8013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`a` > 1)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`a` > 1)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`a` > 1)",
|
|
"final_table_condition ": "(`t1`.`a` > 1)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
jump_if_not_case_when 11(15) (case_expr@0 = 2) {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
SET @b=2 {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @a,@b;
|
|
@a @b
|
|
3 2
|
|
set optimizer_trace_max_mem_size=@old_max;
|
|
drop procedure p1;
|
|
drop table t1;
|
|
|
|
# Test of tracing of DO.
|
|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
do (select 42);
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
do (select 42) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 42"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select 42) AS `(select 42)`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
do (select 42) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 42"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select 42) AS `(select 42)`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
# Test of tracing of subquery used in parameter of routine call
|
|
|
|
create table t1(a int);
|
|
insert into t1 values(1),(2);
|
|
create procedure p1(x int)
|
|
begin
|
|
declare b int;
|
|
set b=(select 2+x from dual);
|
|
end|
|
|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
|
|
call p1((select a from t1 limit 1));
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
call p1((select a from t1 limit 1)) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`a` from `t1` limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
call p1((select a from t1 limit 1)) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`a` from `t1` limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5009
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7009,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7009,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set b@1 NULL {
|
|
"steps": [
|
|
] /* steps */
|
|
} 0 0
|
|
set b@1 (select (2 + x@0)) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select (2 + x@0)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop procedure p1;
|
|
drop table t1;
|
|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
|
|
|
|
# Test that printing expanded query does not alter query's
|
|
# results.
|
|
# Comes from ctype_utf8mb4_heap.test
|
|
|
|
create table t1 (f1 varchar(1) not null) default charset utf8mb4;
|
|
insert into t1 values (''), ('');
|
|
select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
|
|
concat(concat(_latin1'->',f1),_latin1'<-')
|
|
-><-
|
|
-><-
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select concat(concat(_latin1'->',f1),_latin1'<-') from t1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select concat(concat('->',`t1`.`f1`),'<-') AS `concat(concat(_latin1'->',f1),_latin1'<-')` from `t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5024
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7024,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7024,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1;
|
|
|
|
# Bug#12546331 - SEGFAULT IN SUBSELECT_INDEXSUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
|
|
|
|
CREATE TABLE t1 (
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key varchar(1),
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
INSERT INTO t1 VALUES
|
|
(NULL,8,'x'),
|
|
(8,7,'d'),
|
|
(1,1,'r'),
|
|
(9,7,'f'),
|
|
(4,9,'y'),
|
|
(3,NULL,'u'),
|
|
(2,1,'m'),
|
|
(NULL,9,NULL),
|
|
(2,2,'o'),
|
|
(NULL,9,'w'),
|
|
(6,2,'m'),
|
|
(7,4,'q'),
|
|
(2,0,NULL),
|
|
(5,4,'d'),
|
|
(7,8,'g'),
|
|
(6,NULL,'x'),
|
|
(6,NULL,'f'),
|
|
(2,0,'p'),
|
|
(9,NULL,'j'),
|
|
(6,8,'c')
|
|
;
|
|
CREATE TABLE t2 (
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key varchar(1),
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
INSERT INTO t2 VALUES
|
|
(2,4,'v'),
|
|
(150,62,'v'),
|
|
(NULL,7,'c'),
|
|
(2,1,NULL),
|
|
(5,0,'x'),
|
|
(3,7,'i'),
|
|
(1,7,'e'),
|
|
(4,1,'p'),
|
|
(NULL,7,'s'),
|
|
(2,1,'j'),
|
|
(6,5,'z'),
|
|
(6,2,'c'),
|
|
(8,0,'a'),
|
|
(2,1,'q'),
|
|
(6,8,'y'),
|
|
(8,1,NULL),
|
|
(3,1,'r'),
|
|
(3,9,'v'),
|
|
(9,1,NULL),
|
|
(6,5,'r')
|
|
;
|
|
SELECT col_int_nokey
|
|
FROM (
|
|
SELECT *
|
|
FROM t2
|
|
WHERE col_varchar_key > 'a'
|
|
OR ( 7 , 5 ) NOT IN (
|
|
SELECT col_int_nokey , col_int_key
|
|
FROM t1 )
|
|
) AS alias1;
|
|
col_int_nokey
|
|
2
|
|
150
|
|
NULL
|
|
2
|
|
5
|
|
3
|
|
1
|
|
4
|
|
NULL
|
|
2
|
|
6
|
|
6
|
|
8
|
|
2
|
|
6
|
|
8
|
|
3
|
|
3
|
|
9
|
|
6
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
|
|
|
|
CREATE TABLE t1 (
|
|
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
|
|
col_int_key INT,
|
|
KEY col_int_key (col_int_key)
|
|
);
|
|
CREATE TABLE t2 (
|
|
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
|
|
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
|
|
col_int_key INT,
|
|
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
|
|
KEY col_int_key (col_int_key)
|
|
);
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
|
|
CREATE TABLE t3 (
|
|
col_int_key INT,
|
|
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
|
|
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
|
|
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
|
|
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
|
|
);
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
INSERT INTO t3 VALUES (0,'s','it');
|
|
INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
|
|
SELECT table2.col_int_key
|
|
FROM t3 AS table1
|
|
LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
|
|
LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
|
|
table3.col_varchar_10_utf8_key
|
|
;
|
|
col_int_key
|
|
NULL
|
|
NULL
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT table2.col_int_key
|
|
FROM t3 AS table1
|
|
LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
|
|
LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
|
|
table3.col_varchar_10_utf8_key {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `table2`.`col_int_key` AS `col_int_key` from `t3` `table1` left join `t1` `table2` on((`table1`.`col_int_key` < `table2`.`col_int_key`)) left join `t2` `table3` on((convert(`table2`.`col_varchar_10_latin1_key` using utf8) >= `table3`.`col_varchar_10_utf8_key`))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": null,
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t3` `table1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2` `table3`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t3` `table1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5027
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
},
|
|
{
|
|
"table": "`t2` `table3`",
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 0.5012
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7027,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7027,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table2`",
|
|
"`t3` `table1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `table3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 1,
|
|
"cost": 0.7012,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.4039,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"condition_on_constant_tables": "true",
|
|
"condition_value": true
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "true",
|
|
"attached_conditions_computation": [
|
|
{
|
|
"table": "`t2` `table3`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 2.7012
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "col_varchar_10_utf8_key",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"col_varchar_10_utf8_key"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "col_varchar_10_utf8_key",
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
{
|
|
"impossible_condition": {
|
|
"cause": "comparison_with_null_always_false"
|
|
} /* impossible_condition */
|
|
}
|
|
] /* setup_range_conditions */,
|
|
"impossible_range": true
|
|
} /* range_analysis */,
|
|
"without_ON_clause": {
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 2.7012
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "col_varchar_10_utf8_key",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"col_varchar_10_utf8_key"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "col_varchar_10_utf8_key",
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */
|
|
} /* range_analysis */
|
|
} /* without_ON_clause */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t3` `table1`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t2` `table3`",
|
|
"attached": "<if>(is_not_null_compl(table3), (convert(NULL using utf8) >= `table3`.`col_varchar_10_utf8_key`), true)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2` `table3`",
|
|
"original_table_condition": "<if>(is_not_null_compl(table3), (convert(NULL using utf8) >= `table3`.`col_varchar_10_utf8_key`), true)",
|
|
"final_table_condition ": "<if>(is_not_null_compl(table3), (<cache>(convert(NULL using utf8)) >= `table3`.`col_varchar_10_utf8_key`), true)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t3` `table1`"
|
|
},
|
|
{
|
|
"table": "`t2` `table3`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
Tests of tracing of the "eq_ref optimization" of plan search
|
|
|
|
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, key(a));
|
|
create table t2 (a int, b int, key(a));
|
|
create table t3 (a int, b int, key(a));
|
|
insert into t1 select a,a from t0;
|
|
insert into t2 select a,a from t0;
|
|
insert into t3 select a,a from t0;
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
explain select *
|
|
from t0 where a in
|
|
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t0 NULL ALL NULL NULL NULL NULL 10 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL index a a 5 NULL 10 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ref a a 5 test.t1.a 1 100.00 Using index
|
|
2 DEPENDENT SUBQUERY t3 NULL ref a a 5 test.t1.a 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))))
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
explain select *
|
|
from t0 where a in
|
|
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "IN (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "IN (SELECT)",
|
|
"to": "EXISTS (CORRELATED SELECT)",
|
|
"chosen": true,
|
|
"evaluating_constant_where_conditions": [
|
|
] /* evaluating_constant_where_conditions */
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t0`.`a` AS `a` from `t0` where <in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`)))) where (<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`))))"
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"outer_join_to_inner_join",
|
|
"JOIN_condition_to_WHERE",
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))"
|
|
} /* transformations_to_nested_joins */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t0`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t0`",
|
|
"table_scan": {
|
|
"rows": 10,
|
|
"cost": 0.5043
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t0`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 10,
|
|
"cost": 1.5043,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 1.5043,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t0`",
|
|
"attached": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t0`",
|
|
"original_table_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
|
|
"final_table_condition ": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t0`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "a",
|
|
"equals": "`t2`.`a`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "a",
|
|
"equals": "`t3`.`a`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "a",
|
|
"equals": "`t1`.`a`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "a",
|
|
"equals": "`t3`.`a`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"field": "a",
|
|
"equals": "`t2`.`a`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"field": "a",
|
|
"equals": "`t1`.`a`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 10,
|
|
"cost": 0.5055
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 10,
|
|
"cost": 0.5055
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"table_scan": {
|
|
"rows": 10,
|
|
"cost": 0.5055
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 10,
|
|
"cost": 1.5055,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 1.5055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 5.0055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 8.5055,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 10,
|
|
"cost": 1.5055,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 1.5055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 5.0055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 8.5055,
|
|
"pruned_by_cost": true
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 8.5055,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 5.0055,
|
|
"pruned_by_heuristic": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 10,
|
|
"cost": 1.5055,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 1.5055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 5.0055,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 8.5055,
|
|
"pruned_by_cost": true
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 8.5055,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 10,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 10.506,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 5.0055,
|
|
"pruned_by_heuristic": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "IN (SELECT)",
|
|
"to": "EXISTS (CORRELATED SELECT)",
|
|
"put_1_in_SELECT_list": true
|
|
} /* transformation */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`) and (<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`)))",
|
|
"attached_conditions_computation": [
|
|
{
|
|
"table": "`t1`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 10,
|
|
"cost": 5.5
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"a"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "a",
|
|
"cost": 1.3023,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */
|
|
} /* range_analysis */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))"
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(`t2`.`a` = `t1`.`a`)"
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"attached": "(`t3`.`a` = `t1`.`a`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))",
|
|
"final_table_condition ": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))"
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(`t2`.`a` = `t1`.`a`)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"original_table_condition": "(`t3`.`a` = `t1`.`a`)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
},
|
|
{
|
|
"table": "`t3`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_explain": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_switch=@old_opt_switch;
|
|
drop table t0,t1,t2,t3;
|
|
CREATE TABLE t1 (
|
|
OBJECTID int(11) NOT NULL default '0',
|
|
SORTORDER int(11) NOT NULL auto_increment,
|
|
KEY t1_SortIndex (SORTORDER),
|
|
KEY t1_IdIndex (OBJECTID)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
ID int(11) default NULL,
|
|
PARID int(11) default NULL,
|
|
UNIQUE KEY t2_ID_IDX (ID),
|
|
KEY t2_PARID_IDX (PARID)
|
|
) engine=MyISAM DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
|
|
CREATE TABLE t3 (
|
|
ID int(11) default NULL,
|
|
DATA decimal(10,2) default NULL,
|
|
UNIQUE KEY t3_ID_IDX (ID)
|
|
) engine=MyISAM DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
|
|
select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
|
|
497 ID NULL
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from `t1` join `t2` join `t3` where ((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`)) group by `t2`.`PARID` order by `DATA` desc"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": " `tmp`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from `t1` join `t2` join `t3` where ((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`)) group by `t2`.`PARID` order by `DATA` desc) `tmp`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "OBJECTID",
|
|
"equals": "`t3`.`ID`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "OBJECTID",
|
|
"equals": "`t2`.`ID`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "ID",
|
|
"equals": "`t1`.`OBJECTID`",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "ID",
|
|
"equals": "`t3`.`ID`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"field": "ID",
|
|
"equals": "`t1`.`OBJECTID`",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"field": "ID",
|
|
"equals": "`t2`.`ID`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"const_keys_added": {
|
|
"keys": [
|
|
"t2_PARID_IDX"
|
|
] /* keys */,
|
|
"cause": "group_by"
|
|
} /* const_keys_added */,
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 9,
|
|
"cost": 3.5049
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "t2_ID_IDX",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "t2_PARID_IDX",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"PARID"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t3`",
|
|
"table_scan": {
|
|
"rows": 8,
|
|
"cost": 0.5049
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "t3_ID_IDX",
|
|
"rows": 1,
|
|
"cost": 0.1,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 1.3049,
|
|
"rows": 8,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.1,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "t2_ID_IDX",
|
|
"rows": 1,
|
|
"cost": 0.1,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 1.4049,
|
|
"rows": 9,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.2,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": " `tmp`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": " `tmp`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.525
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": " `tmp`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 2.725,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 2.725,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": " `tmp`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": " `tmp`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": " `tmp`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 2,
|
|
"row_length": 10,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1,t2,t3;
|
|
CREATE TABLE t1 (
|
|
t1_id bigint(21) NOT NULL auto_increment,
|
|
_field_72 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_95 varchar(32),
|
|
_field_115 tinyint(4) DEFAULT '0' NOT NULL,
|
|
_field_122 tinyint(4) DEFAULT '0' NOT NULL,
|
|
_field_126 tinyint(4),
|
|
_field_134 tinyint(4),
|
|
PRIMARY KEY (t1_id),
|
|
UNIQUE _field_72 (_field_72),
|
|
KEY _field_115 (_field_115),
|
|
KEY _field_122 (_field_122)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
|
|
INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
|
|
INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
|
|
CREATE TABLE t2 (
|
|
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
|
|
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (seq_0_id,seq_1_id)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES (1,1);
|
|
INSERT INTO t2 VALUES (2,1);
|
|
INSERT INTO t2 VALUES (2,2);
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
CREATE TABLE t3 (
|
|
t3_id bigint(21) NOT NULL auto_increment,
|
|
_field_131 varchar(128),
|
|
_field_133 tinyint(4) DEFAULT '0' NOT NULL,
|
|
_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
|
|
_field_137 tinyint(4),
|
|
_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
|
|
_field_140 blob,
|
|
_field_142 tinyint(4) DEFAULT '0' NOT NULL,
|
|
_field_145 tinyint(4) DEFAULT '0' NOT NULL,
|
|
_field_148 tinyint(4) DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (t3_id),
|
|
KEY _field_133 (_field_133),
|
|
KEY _field_135 (_field_135),
|
|
KEY _field_139 (_field_139),
|
|
KEY _field_142 (_field_142),
|
|
KEY _field_145 (_field_145),
|
|
KEY _field_148 (_field_148)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
|
|
INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
|
|
SET sql_mode = default;
|
|
CREATE TABLE t4 (
|
|
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
|
|
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (seq_0_id,seq_1_id)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t4 VALUES (1,1);
|
|
INSERT INTO t4 VALUES (2,1);
|
|
CREATE TABLE t5 (
|
|
t5_id bigint(21) NOT NULL auto_increment,
|
|
_field_149 tinyint(4),
|
|
_field_156 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_157 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_158 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_159 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_160 varchar(128) DEFAULT '' NOT NULL,
|
|
_field_161 varchar(128) DEFAULT '' NOT NULL,
|
|
PRIMARY KEY (t5_id),
|
|
KEY _field_156 (_field_156),
|
|
KEY _field_157 (_field_157),
|
|
KEY _field_158 (_field_158),
|
|
KEY _field_159 (_field_159),
|
|
KEY _field_160 (_field_160),
|
|
KEY _field_161 (_field_161)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
|
|
INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
|
|
CREATE TABLE t6 (
|
|
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
|
|
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (seq_0_id,seq_1_id)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t6 VALUES (1,1);
|
|
INSERT INTO t6 VALUES (1,2);
|
|
INSERT INTO t6 VALUES (2,2);
|
|
CREATE TABLE t7 (
|
|
t7_id bigint(21) NOT NULL auto_increment,
|
|
_field_143 tinyint(4),
|
|
_field_165 varchar(32),
|
|
_field_166 smallint(6) DEFAULT '0' NOT NULL,
|
|
PRIMARY KEY (t7_id),
|
|
KEY _field_166 (_field_166)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t7 VALUES (1,0,'High',1);
|
|
INSERT INTO t7 VALUES (2,0,'Medium',2);
|
|
INSERT INTO t7 VALUES (3,0,'Low',3);
|
|
select
|
|
replace(t3._field_140, "\r","^M"),
|
|
t3_id,
|
|
min(t3._field_131),
|
|
min(t3._field_135),
|
|
min(t3._field_139),
|
|
min(t3._field_137),
|
|
min(link_alias_142._field_165),
|
|
min(link_alias_133._field_72),
|
|
min(t3._field_145),
|
|
min(link_alias_148._field_156),
|
|
replace(min(t3._field_140), "\r","^M"),
|
|
t3.t3_id
|
|
from
|
|
t3 left join t4 on t4.seq_0_id = t3.t3_id
|
|
left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
|
|
left join t6 on t6.seq_0_id = t3.t3_id
|
|
left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
|
|
left join t2 on t2.seq_0_id = t3.t3_id
|
|
left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
|
|
where
|
|
t3.t3_id in (1)
|
|
group by
|
|
t3.t3_id
|
|
order by
|
|
link_alias_142._field_166,
|
|
_field_139,
|
|
link_alias_133._field_72,
|
|
_field_135,
|
|
link_alias_148._field_156
|
|
;
|
|
replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
|
|
test^M
|
|
job^M
|
|
1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
|
|
job^M
|
|
1 1
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select
|
|
replace(t3._field_140, "\r","^M"),
|
|
t3_id,
|
|
min(t3._field_131),
|
|
min(t3._field_135),
|
|
min(t3._field_139),
|
|
min(t3._field_137),
|
|
min(link_alias_142._field_165),
|
|
min(link_alias_133._field_72),
|
|
min(t3._field_145),
|
|
min(link_alias_148._field_156),
|
|
replace(min(t3._field_140), "\r","^M"),
|
|
t3.t3_id
|
|
from
|
|
t3 left join t4 on t4.seq_0_id = t3.t3_id
|
|
left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
|
|
left join t6 on t6.seq_0_id = t3.t3_id
|
|
left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
|
|
left join t2 on t2.seq_0_id = t3.t3_id
|
|
left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
|
|
where
|
|
t3.t3_id in (1)
|
|
group by
|
|
t3.t3_id
|
|
order by
|
|
link_alias_142._field_166,
|
|
_field_139,
|
|
link_alias_133._field_72,
|
|
_field_135,
|
|
link_alias_148._field_156 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select replace(`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id`,min(`t3`.`_field_131`) AS `min(t3._field_131)`,min(`t3`.`_field_135`) AS `min(t3._field_135)`,min(`t3`.`_field_139`) AS `min(t3._field_139)`,min(`t3`.`_field_137`) AS `min(t3._field_137)`,min(`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`t3`.`_field_145`) AS `min(t3._field_145)`,min(`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id` from `t3` left join `t4` on((`t4`.`seq_0_id` = `t3`.`t3_id`)) left join `t7` `link_alias_142` on((`t4`.`seq_1_id` = `link_alias_142`.`t7_id`)) left join `t6` on((`t6`.`seq_0_id` = `t3`.`t3_id`)) left join `t1` `link_alias_133` on((`t6`.`seq_1_id` = `link_alias_133`.`t1_id`)) left join `t2` on((`t2`.`seq_0_id` = `t3`.`t3_id`)) left join `t5` `link_alias_148` on((`t2`.`seq_1_id` = `link_alias_148`.`t5_id`)) where (`t3`.`t3_id` = 1) group by `t3`.`t3_id` order by `link_alias_142`.`_field_166`,`t3`.`_field_139`,`link_alias_133`.`_field_72`,`t3`.`_field_135`,`link_alias_148`.`_field_156`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t3`.`t3_id` = 1)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t3`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t4`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t6`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 3,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1,
|
|
2
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 4,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1,
|
|
2,
|
|
3
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 5,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1,
|
|
2,
|
|
3,
|
|
4
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 6,
|
|
"depends_on_map_bits": [
|
|
0,
|
|
1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t3`",
|
|
"field": "t3_id",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t4`",
|
|
"field": "seq_0_id",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`",
|
|
"field": "t7_id",
|
|
"equals": "`t4`.`seq_1_id`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t6`",
|
|
"field": "seq_0_id",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`",
|
|
"field": "t1_id",
|
|
"equals": "`t6`.`seq_1_id`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "seq_0_id",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`",
|
|
"field": "t5_id",
|
|
"equals": "`t2`.`seq_1_id`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t3`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "const",
|
|
"empty": false
|
|
},
|
|
{
|
|
"table": "`t4`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8021
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"seq_0_id",
|
|
"seq_1_id"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "PRIMARY",
|
|
"cost": 0.4609,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.36,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5039
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t6`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9031
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"seq_0_id",
|
|
"seq_1_id"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "PRIMARY",
|
|
"cost": 0.5717,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.36,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5103
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9031
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"seq_0_id",
|
|
"seq_1_id"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "PRIMARY",
|
|
"cost": 0.5717,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= seq_0_id <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.36,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5034
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t4`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t4`"
|
|
] /* plan_prefix */,
|
|
"table": "`t7` `link_alias_142`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 0.8039,
|
|
"rows": 3,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.7,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t4`",
|
|
"`t7` `link_alias_142`"
|
|
] /* plan_prefix */,
|
|
"table": "`t6`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 1.05,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t4`",
|
|
"`t7` `link_alias_142`",
|
|
"`t6`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `link_alias_133`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 0.8103,
|
|
"rows": 3,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 1.4,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t4`",
|
|
"`t7` `link_alias_142`",
|
|
"`t6`",
|
|
"`t1` `link_alias_133`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 1.75,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t3`",
|
|
"`t4`",
|
|
"`t7` `link_alias_142`",
|
|
"`t6`",
|
|
"`t1` `link_alias_133`",
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t5` `link_alias_148`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 0.7034,
|
|
"rows": 2,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"added_to_eq_ref_extension": true,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 2.1,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"condition_on_constant_tables": "true",
|
|
"condition_value": true
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "true",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t4`",
|
|
"attached": "<if>(is_not_null_compl(t4), (`t4`.`seq_0_id` = 1), true)"
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`",
|
|
"attached": "<if>(is_not_null_compl(link_alias_142), (`link_alias_142`.`t7_id` = `t4`.`seq_1_id`), true)"
|
|
},
|
|
{
|
|
"table": "`t6`",
|
|
"attached": "<if>(is_not_null_compl(t6), (`t6`.`seq_0_id` = 1), true)"
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`",
|
|
"attached": "<if>(is_not_null_compl(link_alias_133), (`link_alias_133`.`t1_id` = `t6`.`seq_1_id`), true)"
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "<if>(is_not_null_compl(t2), (`t2`.`seq_0_id` = 1), true)"
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`",
|
|
"attached": "<if>(is_not_null_compl(link_alias_148), (`link_alias_148`.`t5_id` = `t2`.`seq_1_id`), true)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`link_alias_142`.`_field_166`,'1999-02-25 22:43:32',`link_alias_133`.`_field_72`,'0000-00-00 00:00:00',`link_alias_148`.`_field_156`",
|
|
"items": [
|
|
{
|
|
"item": "`link_alias_142`.`_field_166`"
|
|
},
|
|
{
|
|
"item": "'1999-02-25 22:43:32'",
|
|
"uses_only_constant_tables": true
|
|
},
|
|
{
|
|
"item": "`link_alias_133`.`_field_72`"
|
|
},
|
|
{
|
|
"item": "'0000-00-00 00:00:00'",
|
|
"uses_only_constant_tables": true
|
|
},
|
|
{
|
|
"item": "`link_alias_148`.`_field_156`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": false,
|
|
"resulting_clause": "`link_alias_142`.`_field_166`,`link_alias_133`.`_field_72`,`link_alias_148`.`_field_156`"
|
|
} /* simplifying_order_by */,
|
|
"simplifying_group_by": {
|
|
"original_clause": "'1'",
|
|
"items": [
|
|
{
|
|
"item": "'1'",
|
|
"uses_only_constant_tables": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": ""
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t4`",
|
|
"original_table_condition": "<if>(is_not_null_compl(t4), (`t4`.`seq_0_id` = 1), true)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`",
|
|
"original_table_condition": "<if>(is_not_null_compl(link_alias_142), (`link_alias_142`.`t7_id` = `t4`.`seq_1_id`), true)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t6`",
|
|
"original_table_condition": "<if>(is_not_null_compl(t6), (`t6`.`seq_0_id` = 1), true)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`",
|
|
"original_table_condition": "<if>(is_not_null_compl(link_alias_133), (`link_alias_133`.`t1_id` = `t6`.`seq_1_id`), true)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "<if>(is_not_null_compl(t2), (`t2`.`seq_0_id` = 1), true)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`",
|
|
"original_table_condition": "<if>(is_not_null_compl(link_alias_148), (`link_alias_148`.`t5_id` = `t2`.`seq_1_id`), true)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t4`"
|
|
},
|
|
{
|
|
"table": "`t7` `link_alias_142`"
|
|
},
|
|
{
|
|
"table": "`t6`"
|
|
},
|
|
{
|
|
"table": "`t1` `link_alias_133`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
},
|
|
{
|
|
"table": "`t5` `link_alias_148`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1,t2,t3,t4,t5,t6,t7;
|
|
#
|
|
# Tracing of ORDER BY & GROUP BY simplification.
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT, col_int_key INT,
|
|
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1)
|
|
);
|
|
INSERT INTO t1 VALUES
|
|
(10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
|
|
(15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
|
|
(20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
|
|
(25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
|
|
CREATE TABLE t2 (
|
|
pk INT, col_int_key INT,
|
|
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t2 VALUES
|
|
(1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
|
|
(6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
|
|
(11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
|
|
(16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
|
|
(21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
|
|
(26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
|
|
(31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
|
|
(36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
|
|
(41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
|
|
(46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
|
|
(51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
|
|
(56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
|
|
(61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
|
|
(66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
|
|
(71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
|
|
(76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
|
|
(81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
|
|
(86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
|
|
(91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
|
|
(96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');
|
|
SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
|
|
STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
|
|
GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ;
|
|
SUM(alias2.col_varchar_nokey) field2
|
|
0 1
|
|
0 2
|
|
0 3
|
|
0 4
|
|
0 5
|
|
0 6
|
|
0 7
|
|
0 9
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'f'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'e'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'f'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'y'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'd'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'f'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'e'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'p'
|
|
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
|
|
STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
|
|
GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `SUM(alias2.col_varchar_nokey)`,`alias2`.`pk` AS `field2` from `t1` `alias1` straight_join `t2` `alias2` where ((0 <> `alias1`.`pk`) and (`alias2`.`pk` = `alias1`.`col_int_key`)) group by `field2` order by `alias1`.`col_int_key`,`alias2`.`pk`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((0 <> `alias1`.`pk`) and (`alias2`.`pk` = `alias1`.`col_int_key`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1` `alias1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2` `alias2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t2` `alias2`",
|
|
"field": "pk",
|
|
"equals": "`alias1`.`col_int_key`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1` `alias1`",
|
|
"table_scan": {
|
|
"rows": 20,
|
|
"cost": 0.5244
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t2` `alias2`",
|
|
"const_keys_added": {
|
|
"keys": [
|
|
"PRIMARY"
|
|
] /* keys */,
|
|
"cause": "group_by"
|
|
} /* const_keys_added */,
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 100,
|
|
"cost": 12.722
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"pk"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `alias1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 20,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.9,
|
|
"access_type": "scan",
|
|
"resulting_rows": 18,
|
|
"cost": 2.5244,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 18,
|
|
"cost_for_plan": 2.5244,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `alias1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `alias2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "eq_ref",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"cost": 6.3,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 100,
|
|
"cost": 180.62,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 18,
|
|
"cost_for_plan": 8.8244,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and (0 <> `alias1`.`pk`))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1` `alias1`",
|
|
"attached": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"
|
|
},
|
|
{
|
|
"table": "`t2` `alias2`",
|
|
"attached": "(`alias2`.`pk` = `alias1`.`col_int_key`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`",
|
|
"items": [
|
|
{
|
|
"item": "`alias1`.`col_int_key`"
|
|
},
|
|
{
|
|
"item": "`alias2`.`pk`",
|
|
"eq_ref_to_preceding_items": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`alias1`.`col_int_key`"
|
|
} /* simplifying_order_by */,
|
|
"simplifying_group_by": {
|
|
"original_clause": "`field2`",
|
|
"items": [
|
|
{
|
|
"item": "`alias2`.`pk`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": false,
|
|
"resulting_clause": "`field2`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1` `alias1`",
|
|
"original_table_condition": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))",
|
|
"final_table_condition ": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"
|
|
},
|
|
{
|
|
"table": "`t2` `alias2`",
|
|
"original_table_condition": "(`alias2`.`pk` = `alias1`.`col_int_key`)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1` `alias1`"
|
|
},
|
|
{
|
|
"table": "`t2` `alias2`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 2,
|
|
"write_method": "continuously_update_group_row"
|
|
},
|
|
{
|
|
"adding_sort_to_table_in_plan_at_position": 2
|
|
} /* filesort */
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"temp_table_aggregate": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 2,
|
|
"columns": 3,
|
|
"row_length": 18,
|
|
"key_length": 4,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
}
|
|
] /* steps */
|
|
} /* temp_table_aggregate */
|
|
},
|
|
{
|
|
"sorting_table_in_plan_at_position": 2,
|
|
"filesort_information": [
|
|
{
|
|
"direction": "asc",
|
|
"field": "col_int_key"
|
|
}
|
|
] /* filesort_information */,
|
|
"filesort_priority_queue_optimization": {
|
|
"usable": false,
|
|
"cause": "not applicable (no LIMIT)"
|
|
} /* filesort_priority_queue_optimization */,
|
|
"filesort_execution": [
|
|
] /* filesort_execution */,
|
|
"filesort_summary": {
|
|
"memory_available": 262144,
|
|
"key_size": "XXX",
|
|
"row_size": "XXX",
|
|
"max_rows_per_buffer": 18,
|
|
"num_rows_estimate": 18,
|
|
"num_rows_found": 8,
|
|
"num_initial_chunks_spilled_to_disk": 0,
|
|
"peak_memory_used": "NNN",
|
|
"sort_algorithm": "std::sort",
|
|
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
|
|
} /* filesort_summary */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Trace of "condition on constant tables"
|
|
#
|
|
create table t1(a int) engine=myisam;
|
|
insert into t1 values(26);
|
|
create table t2(b int primary key, c int) engine=myisam;
|
|
insert into t2 values(1,100),(2,200),(3,300);
|
|
select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2;
|
|
a b c
|
|
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where (((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t2`",
|
|
"field": "b",
|
|
"equals": "2",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "const",
|
|
"empty": false
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"condition_on_constant_tables": "(('26' + '200') = cos(10))",
|
|
"condition_value": false
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "Impossible WHERE noticed after reading const tables"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1,t2;
|
|
#
|
|
# Trace of non-default db
|
|
#
|
|
create table t1(a int);
|
|
insert into t1 values(1),(2),(3);
|
|
create database mysqltest2;
|
|
create table mysqltest2.t2(a int);
|
|
insert into mysqltest2.t2 values(1),(2);
|
|
select * from t1,mysqltest2.t2;
|
|
a a
|
|
1 1
|
|
1 2
|
|
2 1
|
|
2 2
|
|
3 1
|
|
3 2
|
|
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select * from t1,mysqltest2.t2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`mysqltest2`.`t2`.`a` AS `a` from `t1` join `mysqltest2`.`t2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`mysqltest2`.`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5013
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`mysqltest2`.`t2`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5009
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`mysqltest2`.`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7009,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7009,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`mysqltest2`.`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 3,
|
|
"cost": 1.1013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 6,
|
|
"cost_for_plan": 1.8022,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"access_type": "scan",
|
|
"resulting_rows": 3,
|
|
"cost": 0.8013,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 3,
|
|
"cost_for_plan": 0.8013,
|
|
"pruned_by_heuristic": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`mysqltest2`.`t2`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`mysqltest2`.`t2`"
|
|
},
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1;
|
|
drop database mysqltest2;
|