888 lines
29 KiB
Plaintext
888 lines
29 KiB
Plaintext
set @@session.end_markers_in_json=on;
|
||
set @@session.optimizer_trace="enabled=on";
|
||
create table t1(a int);
|
||
insert into t1 values(1);
|
||
set names latin1;
|
||
explain select 'ÁÂÃÄÅ', _latin1'ÁÂÃÄÅ' from t1 limit 1;
|
||
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
||
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
||
Warnings:
|
||
Note 1003 /* select#1 */ select 'ÁÂÃÄÅ' AS `ÁÂÃÄÅ`,_latin1'\xC1\xC2\xC3\xC4\xC5' AS `ÁÂÃÄÅ` from `test`.`t1` limit 1
|
||
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
|
||
(@query:=QUERY)+NULL (@trace:=TRACE)+NULL
|
||
NULL NULL
|
||
Warnings:
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
select @query, @trace;
|
||
@query @trace
|
||
explain select 'ÁÂÃÄÅ', _latin1'ÁÂÃÄÅ' from t1 limit 1 {
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"expanded_query": "/* select#1 */ select 'ÁÂÃÄÅ' AS `ÁÂÃÄÅ`,_latin1'\\xC1\\xC2\\xC3\\xC4\\xC5' AS `ÁÂÃÄÅ` from `t1` limit 1"
|
||
}
|
||
] /* 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": 1,
|
||
"cost": 0.25
|
||
} /* table_scan */
|
||
}
|
||
] /* rows_estimation */
|
||
},
|
||
{
|
||
"considered_execution_plans": [
|
||
{
|
||
"plan_prefix": [
|
||
] /* plan_prefix */,
|
||
"table": "`t1`",
|
||
"best_access_path": {
|
||
"considered_access_paths": [
|
||
{
|
||
"rows_to_scan": 1,
|
||
"access_type": "scan",
|
||
"resulting_rows": 1,
|
||
"cost": 0.35,
|
||
"chosen": true
|
||
}
|
||
] /* considered_access_paths */
|
||
} /* best_access_path */,
|
||
"condition_filtering_pct": 100,
|
||
"rows_for_plan": 1,
|
||
"cost_for_plan": 0.35,
|
||
"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_explain": {
|
||
"select#": 1,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_explain */
|
||
}
|
||
] /* steps */
|
||
}
|
||
set names utf8;
|
||
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.
|
||
select @query, @trace;
|
||
@query @trace
|
||
explain select 'Ã<>ÂÃÄÅ', _latin1'Ã<>ÂÃÄÅ' from t1 limit 1 {
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"expanded_query": "/* select#1 */ select 'Ã<>ÂÃÄÅ' AS `Ã<>ÂÃÄÅ`,_latin1'\\xC1\\xC2\\xC3\\xC4\\xC5' AS `Ã<>ÂÃÄÅ` from `t1` limit 1"
|
||
}
|
||
] /* 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": 1,
|
||
"cost": 0.25
|
||
} /* table_scan */
|
||
}
|
||
] /* rows_estimation */
|
||
},
|
||
{
|
||
"considered_execution_plans": [
|
||
{
|
||
"plan_prefix": [
|
||
] /* plan_prefix */,
|
||
"table": "`t1`",
|
||
"best_access_path": {
|
||
"considered_access_paths": [
|
||
{
|
||
"rows_to_scan": 1,
|
||
"access_type": "scan",
|
||
"resulting_rows": 1,
|
||
"cost": 0.35,
|
||
"chosen": true
|
||
}
|
||
] /* considered_access_paths */
|
||
} /* best_access_path */,
|
||
"condition_filtering_pct": 100,
|
||
"rows_for_plan": 1,
|
||
"cost_for_plan": 0.35,
|
||
"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_explain": {
|
||
"select#": 1,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_explain */
|
||
}
|
||
] /* steps */
|
||
}
|
||
set names latin1;
|
||
create view v1 as select 'ÁÂÃÄÅ' as col from t1 limit 1;
|
||
select * from v1 where v1.col = 'ÁÂÃÄÅ';
|
||
col
|
||
ÁÂÃÄÅ
|
||
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
|
||
(@query:=QUERY)+NULL (@trace:=TRACE)+NULL
|
||
NULL NULL
|
||
Warnings:
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
set names utf8;
|
||
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.
|
||
select @query, @trace;
|
||
@query @trace
|
||
select * from v1 where v1.col = 'Ã<>ÂÃÄÅ' {
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 2,
|
||
"steps": [
|
||
{
|
||
"expanded_query": "/* select#2 */ select 'Ã<>ÂÃÄÅ' AS `col` from `t1` limit 1"
|
||
}
|
||
] /* steps */
|
||
} /* join_preparation */
|
||
},
|
||
{
|
||
"view": {
|
||
"table": "`v1`",
|
||
"select#": 2,
|
||
"materialized": true
|
||
} /* view */
|
||
},
|
||
{
|
||
"expanded_query": "/* select#1 */ select `v1`.`col` AS `col` from `v1` where (`v1`.`col` = 'Ã<>ÂÃÄÅ')"
|
||
}
|
||
] /* steps */
|
||
} /* join_preparation */
|
||
},
|
||
{
|
||
"join_optimization": {
|
||
"select#": 1,
|
||
"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": 1,
|
||
"cost": 0.25
|
||
} /* table_scan */
|
||
}
|
||
] /* rows_estimation */
|
||
},
|
||
{
|
||
"considered_execution_plans": [
|
||
{
|
||
"plan_prefix": [
|
||
] /* plan_prefix */,
|
||
"table": "`t1`",
|
||
"best_access_path": {
|
||
"considered_access_paths": [
|
||
{
|
||
"rows_to_scan": 1,
|
||
"access_type": "scan",
|
||
"resulting_rows": 1,
|
||
"cost": 0.35,
|
||
"chosen": true
|
||
}
|
||
] /* considered_access_paths */
|
||
} /* best_access_path */,
|
||
"condition_filtering_pct": 100,
|
||
"rows_for_plan": 1,
|
||
"cost_for_plan": 0.35,
|
||
"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 */
|
||
},
|
||
{
|
||
"creating_tmp_table": {
|
||
"tmp_table_info": {
|
||
"table": "`v1`",
|
||
"columns": 1,
|
||
"row_length": 7,
|
||
"key_length": 0,
|
||
"unique_constraint": false,
|
||
"makes_grouped_rows": false,
|
||
"cannot_insert_duplicates": false,
|
||
"location": "TempTable"
|
||
} /* tmp_table_info */
|
||
} /* creating_tmp_table */
|
||
},
|
||
{
|
||
"join_execution": {
|
||
"select#": 2,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_execution */
|
||
},
|
||
{
|
||
"condition_processing": {
|
||
"condition": "WHERE",
|
||
"original_condition": "(`v1`.`col` = 'Ã<>ÂÃÄÅ')",
|
||
"steps": [
|
||
{
|
||
"transformation": "equality_propagation",
|
||
"resulting_condition": "multiple equal('Ã<>ÂÃÄÅ', `v1`.`col`)"
|
||
},
|
||
{
|
||
"transformation": "constant_propagation",
|
||
"resulting_condition": "multiple equal('Ã<>ÂÃÄÅ', `v1`.`col`)"
|
||
},
|
||
{
|
||
"transformation": "trivial_condition_removal",
|
||
"resulting_condition": "multiple equal('Ã<>ÂÃÄÅ', `v1`.`col`)"
|
||
}
|
||
] /* steps */
|
||
} /* condition_processing */
|
||
},
|
||
{
|
||
"substitute_generated_columns": {
|
||
} /* substitute_generated_columns */
|
||
},
|
||
{
|
||
"table_dependencies": [
|
||
{
|
||
"table": "`v1`",
|
||
"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": "`v1`",
|
||
"rows": 1,
|
||
"cost": 1,
|
||
"table_type": "system",
|
||
"empty": false
|
||
}
|
||
] /* rows_estimation */
|
||
},
|
||
{
|
||
"condition_on_constant_tables": "true",
|
||
"condition_value": true
|
||
},
|
||
{
|
||
"attaching_conditions_to_tables": {
|
||
"original_condition": "true",
|
||
"attached_conditions_computation": [
|
||
] /* attached_conditions_computation */,
|
||
"attached_conditions_summary": [
|
||
] /* attached_conditions_summary */
|
||
} /* attaching_conditions_to_tables */
|
||
},
|
||
{
|
||
"refine_plan": [
|
||
] /* refine_plan */
|
||
}
|
||
] /* steps */
|
||
} /* join_optimization */
|
||
},
|
||
{
|
||
"join_execution": {
|
||
"select#": 1,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_execution */
|
||
}
|
||
] /* steps */
|
||
}
|
||
drop table t1;
|
||
drop view v1;
|
||
set names latin1;
|
||
create table t1(c char(4) primary key) charset latin1;
|
||
insert into t1 values ('aaa'), ('ÁÂÃÄ');
|
||
select * from t1 where c < 'ÁÂÃÄÅ';
|
||
c
|
||
aaa
|
||
ÁÂÃÄ
|
||
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
|
||
(@query:=QUERY)+NULL (@trace:=TRACE)+NULL
|
||
NULL NULL
|
||
Warnings:
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
||
set names utf8;
|
||
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.
|
||
select @query, @trace;
|
||
@query @trace
|
||
select * from t1 where c < 'Ã<>ÂÃÄÅ' {
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"expanded_query": "/* select#1 */ select `t1`.`c` AS `c` from `t1` where (`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
}
|
||
] /* steps */
|
||
} /* join_preparation */
|
||
},
|
||
{
|
||
"join_optimization": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"condition_processing": {
|
||
"condition": "WHERE",
|
||
"original_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')",
|
||
"steps": [
|
||
{
|
||
"transformation": "equality_propagation",
|
||
"resulting_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
},
|
||
{
|
||
"transformation": "constant_propagation",
|
||
"resulting_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
},
|
||
{
|
||
"transformation": "trivial_condition_removal",
|
||
"resulting_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
}
|
||
] /* 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.55
|
||
} /* table_scan */,
|
||
"potential_range_indexes": [
|
||
{
|
||
"index": "PRIMARY",
|
||
"usable": true,
|
||
"key_parts": [
|
||
"c"
|
||
] /* key_parts */
|
||
}
|
||
] /* potential_range_indexes */,
|
||
"best_covering_index_scan": {
|
||
"index": "PRIMARY",
|
||
"cost": 0.4502,
|
||
"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": "PRIMARY",
|
||
"usable": false,
|
||
"cause": "prefix_not_const_equality"
|
||
}
|
||
] /* potential_skip_scan_indexes */
|
||
} /* skip_scan_range */,
|
||
"analyzing_range_alternatives": {
|
||
"range_scan_alternatives": [
|
||
{
|
||
"index": "PRIMARY",
|
||
"ranges": [
|
||
"c <= Ã<>ÂÃÄ"
|
||
] /* ranges */,
|
||
"index_dives_for_eq_ranges": true,
|
||
"rowid_ordered": true,
|
||
"using_mrr": false,
|
||
"index_only": true,
|
||
"rows": 2,
|
||
"cost": 0.71,
|
||
"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.45,
|
||
"chosen": true
|
||
}
|
||
] /* considered_access_paths */
|
||
} /* best_access_path */,
|
||
"condition_filtering_pct": 100,
|
||
"rows_for_plan": 2,
|
||
"cost_for_plan": 0.45,
|
||
"chosen": true
|
||
}
|
||
] /* considered_execution_plans */
|
||
},
|
||
{
|
||
"attaching_conditions_to_tables": {
|
||
"original_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')",
|
||
"attached_conditions_computation": [
|
||
] /* attached_conditions_computation */,
|
||
"attached_conditions_summary": [
|
||
{
|
||
"table": "`t1`",
|
||
"attached": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
}
|
||
] /* attached_conditions_summary */
|
||
} /* attaching_conditions_to_tables */
|
||
},
|
||
{
|
||
"finalizing_table_conditions": [
|
||
{
|
||
"table": "`t1`",
|
||
"original_table_condition": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')",
|
||
"final_table_condition ": "(`t1`.`c` < 'Ã<>ÂÃÄÅ')"
|
||
}
|
||
] /* finalizing_table_conditions */
|
||
},
|
||
{
|
||
"refine_plan": [
|
||
{
|
||
"table": "`t1`"
|
||
}
|
||
] /* refine_plan */
|
||
}
|
||
] /* steps */
|
||
} /* join_optimization */
|
||
},
|
||
{
|
||
"join_execution": {
|
||
"select#": 1,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_execution */
|
||
}
|
||
] /* steps */
|
||
}
|
||
drop table t1;
|
||
create table `t
|
||
1 a`(`col
|
||
1 a` int, index `index
|
||
1 a` (`col
|
||
1 a`));
|
||
insert into `t
|
||
1 a` values(2),(3);
|
||
create view `v
|
||
1 a` as select * from `t
|
||
1 a`;
|
||
select `col
|
||
1 a` from `v
|
||
1 a` where `col
|
||
1 a` < 6;
|
||
col
|
||
1 a
|
||
2
|
||
3
|
||
select query, trace from information_schema.OPTIMIZER_TRACE;
|
||
query trace
|
||
select `col
|
||
1 a` from `v
|
||
1 a` where `col
|
||
1 a` < 6 {
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"join_preparation": {
|
||
"select#": 2,
|
||
"steps": [
|
||
{
|
||
"expanded_query": "/* select#2 */ select `t\n1\ta`.`col\n1\ta` AS `col\n1\ta` from `t\n1\ta`"
|
||
}
|
||
] /* steps */
|
||
} /* join_preparation */
|
||
},
|
||
{
|
||
"view": {
|
||
"table": "`v\n1\ta`",
|
||
"select#": 2,
|
||
"merged": true
|
||
} /* view */
|
||
},
|
||
{
|
||
"expanded_query": "/* select#1 */ select `t\n1\ta`.`col\n1\ta` AS `col\n1\ta` from (`t\n1\ta`) where (`t\n1\ta`.`col\n1\ta` < 6)"
|
||
},
|
||
{
|
||
"transformations_to_nested_joins": {
|
||
"transformations": [
|
||
"parenthesis_removal"
|
||
] /* transformations */,
|
||
"expanded_query": "/* select#1 */ select `t\n1\ta`.`col\n1\ta` AS `col\n1\ta` from `t\n1\ta` where (`t\n1\ta`.`col\n1\ta` < 6)"
|
||
} /* transformations_to_nested_joins */
|
||
}
|
||
] /* steps */
|
||
} /* join_preparation */
|
||
},
|
||
{
|
||
"join_optimization": {
|
||
"select#": 1,
|
||
"steps": [
|
||
{
|
||
"condition_processing": {
|
||
"condition": "WHERE",
|
||
"original_condition": "(`t\n1\ta`.`col\n1\ta` < 6)",
|
||
"steps": [
|
||
{
|
||
"transformation": "equality_propagation",
|
||
"resulting_condition": "(`t\n1\ta`.`col\n1\ta` < 6)"
|
||
},
|
||
{
|
||
"transformation": "constant_propagation",
|
||
"resulting_condition": "(`t\n1\ta`.`col\n1\ta` < 6)"
|
||
},
|
||
{
|
||
"transformation": "trivial_condition_removal",
|
||
"resulting_condition": "(`t\n1\ta`.`col\n1\ta` < 6)"
|
||
}
|
||
] /* steps */
|
||
} /* condition_processing */
|
||
},
|
||
{
|
||
"substitute_generated_columns": {
|
||
} /* substitute_generated_columns */
|
||
},
|
||
{
|
||
"table_dependencies": [
|
||
{
|
||
"table": "`t\n1\ta`",
|
||
"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": "`t\n1\ta`",
|
||
"range_analysis": {
|
||
"table_scan": {
|
||
"rows": 2,
|
||
"cost": 2.55
|
||
} /* table_scan */,
|
||
"potential_range_indexes": [
|
||
{
|
||
"index": "index\n1\ta",
|
||
"usable": true,
|
||
"key_parts": [
|
||
"col\n1\ta"
|
||
] /* key_parts */
|
||
}
|
||
] /* potential_range_indexes */,
|
||
"best_covering_index_scan": {
|
||
"index": "index\n1\ta",
|
||
"cost": 0.4503,
|
||
"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": "index\n1\ta",
|
||
"usable": false,
|
||
"cause": "prefix_not_const_equality"
|
||
}
|
||
] /* potential_skip_scan_indexes */
|
||
} /* skip_scan_range */,
|
||
"analyzing_range_alternatives": {
|
||
"range_scan_alternatives": [
|
||
{
|
||
"index": "index\n1\ta",
|
||
"ranges": [
|
||
"NULL < col\n1\ta < 6"
|
||
] /* ranges */,
|
||
"index_dives_for_eq_ranges": true,
|
||
"rowid_ordered": false,
|
||
"using_mrr": false,
|
||
"index_only": true,
|
||
"rows": 2,
|
||
"cost": 0.4603,
|
||
"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": "`t\n1\ta`",
|
||
"best_access_path": {
|
||
"considered_access_paths": [
|
||
{
|
||
"rows_to_scan": 2,
|
||
"access_type": "scan",
|
||
"resulting_rows": 2,
|
||
"cost": 0.45,
|
||
"chosen": true
|
||
}
|
||
] /* considered_access_paths */
|
||
} /* best_access_path */,
|
||
"condition_filtering_pct": 100,
|
||
"rows_for_plan": 2,
|
||
"cost_for_plan": 0.45,
|
||
"chosen": true
|
||
}
|
||
] /* considered_execution_plans */
|
||
},
|
||
{
|
||
"attaching_conditions_to_tables": {
|
||
"original_condition": "(`t\n1\ta`.`col\n1\ta` < 6)",
|
||
"attached_conditions_computation": [
|
||
] /* attached_conditions_computation */,
|
||
"attached_conditions_summary": [
|
||
{
|
||
"table": "`t\n1\ta`",
|
||
"attached": "(`t\n1\ta`.`col\n1\ta` < 6)"
|
||
}
|
||
] /* attached_conditions_summary */
|
||
} /* attaching_conditions_to_tables */
|
||
},
|
||
{
|
||
"finalizing_table_conditions": [
|
||
{
|
||
"table": "`t\n1\ta`",
|
||
"original_table_condition": "(`t\n1\ta`.`col\n1\ta` < 6)",
|
||
"final_table_condition ": "(`t\n1\ta`.`col\n1\ta` < 6)"
|
||
}
|
||
] /* finalizing_table_conditions */
|
||
},
|
||
{
|
||
"refine_plan": [
|
||
{
|
||
"table": "`t\n1\ta`"
|
||
}
|
||
] /* refine_plan */
|
||
}
|
||
] /* steps */
|
||
} /* join_optimization */
|
||
},
|
||
{
|
||
"join_execution": {
|
||
"select#": 1,
|
||
"steps": [
|
||
] /* steps */
|
||
} /* join_execution */
|
||
}
|
||
] /* steps */
|
||
}
|
||
drop table `t
|
||
1 a`;
|
||
drop view `v
|
||
1 a`;
|
||
set optimizer_trace=default;
|