3442 lines
145 KiB
Plaintext
3442 lines
145 KiB
Plaintext
SET optimizer_trace_max_mem_size=1048576;
|
|
SET end_markers_in_json=on;
|
|
SET optimizer_trace="enabled=on,one_line=off";
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (2);
|
|
INSERT INTO t2 VALUES (1,7),(2,7);
|
|
# Subselect execute is traced every time it is executed
|
|
SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
|
|
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
|
|
(SELECT a FROM t1 WHERE t1.a=t2.a) a
|
|
NULL 1
|
|
2 2
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5011
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": "..."
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` = `t2`.`a`)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`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_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "('2' = `t2`.`a`)",
|
|
"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": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
# Subselect execute is traced only the first time it is executed
|
|
SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
|
|
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
|
|
(SELECT a FROM t1 WHERE t1.a=t2.a) a
|
|
NULL 1
|
|
2 2
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5011
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": "..."
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` = `t2`.`a`)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`a` = `t2`.`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_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "('2' = `t2`.`a`)",
|
|
"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": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
DROP TABLE t1,t2;
|
|
SET @@optimizer_trace_features="default";
|
|
CREATE TABLE t1 (a FLOAT(5,4) zerofill);
|
|
Warnings:
|
|
Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
|
|
Warnings:
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
|
SELECT t1.a
|
|
FROM t1
|
|
WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
|
|
t1.a= (SELECT a FROM t2 LIMIT 1) ;
|
|
a
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT t1.a
|
|
FROM t1
|
|
WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
|
|
t1.a= (SELECT a FROM t2 LIMIT 1) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t2`.`b` from `t2` limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select `t2`.`a` from `t2` limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a` from `t1` where ((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "((NULL <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `t2` limit 1), `t1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "((NULL <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `t2` limit 1), `t1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "Impossible WHERE"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
SELECT 1 FROM DUAL
|
|
WHERE NOT EXISTS
|
|
(SELECT * FROM t2 WHERE a = 50 AND b = 3);
|
|
1
|
|
1
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT 1 FROM DUAL
|
|
WHERE NOT EXISTS
|
|
(SELECT * FROM t2 WHERE a = 50 AND b = 3) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "EXISTS (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "Impossible WHERE"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b);
|
|
1
|
|
1
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"transformations_to_subquery": [
|
|
"removed_ordering",
|
|
"removed_distinct",
|
|
"removed_grouping"
|
|
] /* transformations_to_subquery */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t2`.`a` from `t2`"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "EXISTS (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
|
|
# DISTINCT/MIN/JOIN/SUBQ QUERY
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INTEGER,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM;
|
|
CREATE TABLE t2 (
|
|
pk INTEGER,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM;
|
|
CREATE TABLE t3 (
|
|
pk INTEGER,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_time_key TIME,
|
|
col_datetime_nokey DATETIME,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_time_key),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM;
|
|
CREATE TABLE t4 (
|
|
pk INTEGER,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_date_key DATE,
|
|
col_date_nokey DATE,
|
|
col_time_key TIME,
|
|
col_time_nokey TIME,
|
|
col_datetime_key DATETIME,
|
|
col_datetime_nokey DATETIME,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM;
|
|
INSERT IGNORE INTO t4 (
|
|
col_int_key,col_int_nokey,
|
|
col_date_key,col_date_nokey,
|
|
col_time_key,col_time_nokey,
|
|
col_datetime_key,col_datetime_nokey,
|
|
col_varchar_key,col_varchar_nokey
|
|
) VALUES
|
|
(8,7,'2008-10-02','2008-10-02','04:07:22.028954','04:07:22.028954','2001-10-08 00:00:00','2001-10-08 00:00:00','g','g');
|
|
Warnings:
|
|
Warning 1364 Field 'pk' doesn't have a default value
|
|
CREATE TABLE t5 (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_date_key DATE,
|
|
col_date_nokey DATE,
|
|
col_time_key TIME,
|
|
col_time_nokey TIME,
|
|
col_datetime_key DATETIME,
|
|
col_datetime_nokey DATETIME,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_int_key),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM;
|
|
INSERT INTO t5 (
|
|
col_int_key,col_int_nokey,
|
|
col_date_key,col_date_nokey,
|
|
col_time_key,col_time_nokey,
|
|
col_datetime_key,col_datetime_nokey,
|
|
col_varchar_key,col_varchar_nokey
|
|
) VALUES
|
|
(8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
|
|
(7,8,'2008-05-03','2008-05-03','10:19:31.050677','10:19:31.050677','2007-10-06 17:56:40.056051','2007-10-06 17:56:40.056051','d','d'),
|
|
(8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
select distinct
|
|
alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
|
|
field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
|
|
as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
|
|
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
|
|
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
|
|
exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
|
|
t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
|
|
c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
|
|
,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
|
|
field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
|
|
field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
|
|
as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
|
|
(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
|
|
( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
|
|
inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
|
|
(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
|
|
(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
|
|
sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
|
|
sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
|
|
alias1.`col_int_key` not in (214) group by field1,field2,field3,
|
|
field4,field5,field6;
|
|
field1 field2 field3 field4 field5 field6
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select distinct
|
|
alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
|
|
field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
|
|
as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
|
|
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
|
|
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
|
|
exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
|
|
t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
|
|
c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
|
|
,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
|
|
field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
|
|
field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
|
|
as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
|
|
(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
|
|
( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
|
|
inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
|
|
(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
|
|
(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
|
|
sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
|
|
sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
|
|
alias1.`col_int_key` not in (214) group by field1,field2,field3,
|
|
field4,field5,field6 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 5,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#5 */ select `sq3_alias2`.`pk` AS `pk`,`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`sq3_alias2`.`col_int_key` AS `col_int_key`,`sq3_alias2`.`col_date_key` AS `col_date_key`,`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`sq3_alias2`.`col_time_key` AS `col_time_key`,`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`t5` `sq3_alias1` join `t4` `sq3_alias2`)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": "``.`` `alias2`",
|
|
"select#": 5,
|
|
"merged": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"transformations_to_subquery": [
|
|
"removed_distinct"
|
|
] /* transformations_to_subquery */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 3,
|
|
"from": "EXISTS (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 4,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 6,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`))"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 6,
|
|
"from": "IN (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* transformation */
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 6,
|
|
"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 `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from ((`t4` `alias3` left join ((`t5` `sq3_alias1` join `t4` `sq3_alias2`)) on((`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))) left join `t5` `alias1` on((`alias3`.`col_int_key` = `sq3_alias2`.`pk`))) where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`)))) and (`alias1`.`col_int_key` <> 214)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"outer_join_to_inner_join",
|
|
"JOIN_condition_to_WHERE",
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))"
|
|
} /* transformations_to_nested_joins */
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
|
|
} /* transformations_to_nested_joins */
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"outer_join_to_inner_join",
|
|
"JOIN_condition_to_WHERE",
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))"
|
|
} /* transformations_to_nested_joins */
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"outer_join_to_inner_join",
|
|
"JOIN_condition_to_WHERE",
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#1 */ select `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from `t4` `alias3` join `t5` `sq3_alias1` join `t4` `sq3_alias2` join `t5` `alias1` where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
|
|
} /* transformations_to_nested_joins */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t5` `alias1`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t5` `sq3_alias1`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t4` `sq3_alias2`",
|
|
"row_may_be_null": true,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t4` `alias3`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 3,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t4` `sq3_alias2`",
|
|
"field": "pk",
|
|
"equals": "`alias3`.`col_int_key`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t4` `sq3_alias2`",
|
|
"field": "col_varchar_key",
|
|
"equals": "`alias3`.`col_varchar_key`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t4` `alias3`",
|
|
"field": "col_varchar_key",
|
|
"equals": "`sq3_alias2`.`col_varchar_key`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t4` `alias3`",
|
|
"field": "col_int_key",
|
|
"equals": "`sq3_alias2`.`pk`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t5` `alias1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9085
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "col_int_key",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"col_int_key"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "col_varchar_key",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"impossible_range": true
|
|
} /* range_analysis */,
|
|
"rows": 0,
|
|
"cause": "impossible_where_condition"
|
|
},
|
|
{
|
|
"table": "`t5` `sq3_alias1`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5085
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t4` `sq3_alias2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
},
|
|
{
|
|
"table": "`t4` `alias3`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t4` `sq3_alias2`",
|
|
"`t4` `alias3`",
|
|
"`t5` `alias1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t5` `sq3_alias1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 3,
|
|
"access_type": "scan",
|
|
"resulting_rows": 3,
|
|
"cost": 0.8085,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 3,
|
|
"cost_for_plan": 0.8085,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set optimizer_switch=@old_opt_switch;
|
|
drop table t1,t2,t3,t4,t5;
|
|
#
|
|
# BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON
|
|
# SELECT/SUBQ/SUM QUERY
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_date_key DATE,
|
|
col_date_nokey DATE,
|
|
col_time_key TIME,
|
|
col_time_nokey TIME,
|
|
col_datetime_key DATETIME,
|
|
col_datetime_nokey DATETIME,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key,col_int_key)
|
|
) ENGINE=MYISAM, CHARSET utf8mb4;
|
|
INSERT INTO t1 (
|
|
col_int_key,col_int_nokey,
|
|
col_date_key,col_date_nokey,
|
|
col_time_key,col_time_nokey,
|
|
col_datetime_key,col_datetime_nokey,
|
|
col_varchar_key,col_varchar_nokey
|
|
) VALUES
|
|
(8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
|
|
(8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
|
|
CREATE TABLE t2 (I INTEGER);
|
|
select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
|
|
t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
|
|
on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
|
|
table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
|
|
(192,18) order by field1 desc;
|
|
field1
|
|
select * from information_schema.optimizer_trace;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
|
|
t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
|
|
on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
|
|
table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
|
|
(192,18) order by field1 desc {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select 5 from `t2`"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 3,
|
|
"from": "IN (SELECT)",
|
|
"to": "EXISTS (CORRELATED SELECT)",
|
|
"chosen": true,
|
|
"evaluating_constant_where_conditions": [
|
|
] /* evaluating_constant_where_conditions */
|
|
} /* transformation */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"IN_uses_bisection": true
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from (`t1` `table1` straight_join `t1` `table2` on((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))) where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18))) order by `field1` desc"
|
|
},
|
|
{
|
|
"transformations_to_nested_joins": {
|
|
"transformations": [
|
|
"JOIN_condition_to_WHERE",
|
|
"parenthesis_removal"
|
|
] /* transformations */,
|
|
"expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from `t1` `table1` straight_join `t1` `table2` where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`)) order by `field1` desc"
|
|
} /* transformations_to_nested_joins */
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"field": "col_varchar_key",
|
|
"equals": "`table2`.`col_varchar_key`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"field": "col_varchar_key",
|
|
"equals": "`table1`.`col_varchar_key`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8056
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"pk"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "col_varchar_key",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* 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 */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"18 <= pk <= 18",
|
|
"192 <= pk <= 192"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 1.21,
|
|
"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": 2,
|
|
"ranges": [
|
|
"18 <= pk <= 18",
|
|
"192 <= pk <= 192"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.21,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5056
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_varchar_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"resulting_rows": 2,
|
|
"cost": 1.41,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.41,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_varchar_key",
|
|
"rows": 2,
|
|
"cost": 1.4,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"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.9057,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 2.3157,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)))",
|
|
"attached_conditions_computation": [
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8056
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "col_varchar_key",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"col_varchar_key",
|
|
"col_int_key"
|
|
] /* 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 */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "col_varchar_key",
|
|
"chosen": false,
|
|
"cause": "depends_on_unread_values"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */
|
|
} /* range_analysis */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"attached": "(`table1`.`pk` in (192,18))"
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"attached": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`field1` desc",
|
|
"items": [
|
|
{
|
|
"item": "(/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`)",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1` `subquery1_t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1` `subquery1_t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5056
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `subquery1_t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7056,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7056,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1` `subquery1_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` `subquery1_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 */,
|
|
"uses_only_constant_tables": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": ""
|
|
} /* simplifying_order_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"original_table_condition": "(`table1`.`pk` in (192,18))",
|
|
"final_table_condition ": "(`table1`.`pk` in (192,18))"
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"original_table_condition": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))",
|
|
"final_table_condition ": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"pushed_index_condition": "(`table1`.`pk` in (192,18))",
|
|
"table_condition_attached": null
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"unknown_key_1": {
|
|
"constant_condition_in_bnl": "<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true))))"
|
|
}
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"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": "`t2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 3,
|
|
"from": "IN (SELECT)",
|
|
"to": "materialization",
|
|
"possible": false,
|
|
"cause": "not an IN predicate"
|
|
} /* transformation */
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 3,
|
|
"from": "IN (SELECT)",
|
|
"to": "EXISTS (CORRELATED SELECT)",
|
|
"put_1_in_SELECT_list": true
|
|
} /* transformation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Tracing of semijoin loosescan
|
|
#
|
|
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, filler char(100), key(a,b)) charset utf8mb4;
|
|
insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
|
|
create table t2 as select * from t1;
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch="firstmatch=off,materialization=off,duplicateweedout=off";
|
|
set @old_opt_prune_level=@@optimizer_prune_level;
|
|
set optimizer_prune_level=0;
|
|
explain select * from t2 where a in (select b from t1 where a=3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 10.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 3))
|
|
# Equality-propagation involving inner field => 1st sj equality is bound
|
|
explain select * from t2 where (b+0,a+0) in (select a,b from t1 where a=3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = 3) and ((`test`.`t2`.`b` + 0) = 3) and ((`test`.`t2`.`a` + 0) = `test`.`t1`.`b`))
|
|
# Equality-propagation involving outer field => 3rd sj equality is bound.
|
|
explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1) and filler='abc';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using index; LooseScan
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = 'abc') and (`test`.`t2`.`filler` = (`test`.`t1`.`a` * 3)))
|
|
SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_1": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"covering_scan": {
|
|
"cost": 1.0484,
|
|
"chosen": true
|
|
} /* covering_scan */
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 10,
|
|
"cost": 112.04,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
}
|
|
# Remove the condition on 'filler' => 3rd sj equality is not bound.
|
|
explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where
|
|
1 SIMPLE t1 NULL ref a a 10 test.t2.b,test.t2.a 1 100.00 Using index; Start temporary; End temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`b`) and (`test`.`t2`.`filler` = (`test`.`t2`.`b` * 3)))
|
|
SELECT show_json_object('"searching_loose_scan_index": {', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"searching_loose_scan_index": {', TRACE)
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"index_handles_needed_semijoin_equalities": false
|
|
}
|
|
] /* indexes */
|
|
}
|
|
# Equality-propagation involving outer field => 3rd sj equality is bound.
|
|
explain select * from t2 as t3, t2
|
|
where t2.filler=t3.filler and
|
|
(t2.b,t2.a,t2.filler) in (select a,b,a*3 from t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL
|
|
1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = `test`.`t3`.`filler`) and (`test`.`t3`.`filler` = (`test`.`t1`.`a` * 3)))
|
|
SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 1299.6,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_1": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"covering_scan": {
|
|
"cost": 1.0484,
|
|
"chosen": true
|
|
} /* covering_scan */
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 16,
|
|
"resulting_rows": 100,
|
|
"cost": 100051,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
}
|
|
# In plan t3-t1-t2, 3rd outer expression is dependent only on
|
|
# previous tables => 3rd sj equality is bound.
|
|
# If t1 is before t3, 3rd sj equality is not bound.
|
|
explain select * from t2 as t3 left join t2 on t2.filler+10=t3.filler+20
|
|
where (t2.b,t2.a,t3.filler+2) in (select a,b,a*3 from t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL
|
|
1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and ((`test`.`t3`.`filler` + 2) = (`test`.`t1`.`a` * 3)) and ((`test`.`t2`.`filler` + 10) = (`test`.`t3`.`filler` + 20)))
|
|
SELECT show_json_object('"considered_execution_plans": [', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"considered_execution_plans": [', TRACE)
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 100,
|
|
"cost_for_plan": 12.996,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2` `t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 1016.5,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2` `t3`",
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3500,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 32,
|
|
"resulting_rows": 100,
|
|
"cost": 100097,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 4516.5,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 6517.5,
|
|
"rows": 10000,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t2` `t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"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": 1003.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 1016.5,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2` `t3`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 16,
|
|
"resulting_rows": 100,
|
|
"cost": 100051,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 1,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 101067,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 1299.6,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_1": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"covering_scan": {
|
|
"cost": 1.0484,
|
|
"chosen": true
|
|
} /* covering_scan */
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 16,
|
|
"resulting_rows": 100,
|
|
"cost": 100051,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 101065,
|
|
"rows": 100,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 102078,
|
|
"rows": 100,
|
|
"duplicate_tables_left": false,
|
|
"chosen": false
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"chosen": true,
|
|
"cause": "previous_plan_used_disabled_strategy"
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 100,
|
|
"cost_for_plan": 12.996,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 1016.5,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`",
|
|
"`t2` `t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 3500,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 32,
|
|
"resulting_rows": 100,
|
|
"cost": 100097,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 4516.5,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 6517.5,
|
|
"rows": 10000,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"chosen": false,
|
|
"cause": "plan_uses_disabled_strategy"
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 35,
|
|
"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": 1003.5,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 100,
|
|
"cost_for_plan": 47.996,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t2`",
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 1051.5,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"cost": 35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 1299.6,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_2": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"index_handles_needed_semijoin_equalities": false
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 3052.5,
|
|
"rows": 10000,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"chosen": false,
|
|
"cause": "plan_uses_disabled_strategy"
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 100,
|
|
"cost_for_plan": 12.996,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 1016,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t2` `t3`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 16,
|
|
"resulting_rows": 100,
|
|
"cost": 100051,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 1,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 101067,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_3": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"index_handles_needed_semijoin_equalities": false
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 102078,
|
|
"rows": 100,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 1,
|
|
"rows_for_plan": 100,
|
|
"cost_for_plan": 1016,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`",
|
|
"`t2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2` `t3`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"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": 1003.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 10000,
|
|
"cost_for_plan": 2019.5,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 12.996,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_4": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"index_handles_needed_semijoin_equalities": false
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 3030.5,
|
|
"rows": 100,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"chosen": false,
|
|
"cause": "plan_uses_disabled_strategy"
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"final_semijoin_strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 100,
|
|
"cost": 1299.6,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_5": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "a",
|
|
"covering_scan": {
|
|
"cost": 1.0484,
|
|
"chosen": true
|
|
} /* covering_scan */
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 100,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 32,
|
|
"resulting_rows": 100,
|
|
"cost": 100097,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */
|
|
}
|
|
]
|
|
drop table t0,t1,t2;
|
|
#
|
|
# Discover bound equality thanks to equality propagation
|
|
# specific of ON clause.
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int(11) DEFAULT NULL,
|
|
b varchar(100) DEFAULT NULL,
|
|
c int(11) DEFAULT NULL,
|
|
KEY b_c_a (b,c,a)
|
|
) ENGINE=InnoDB, CHARSET utf8mb4;
|
|
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.
|
|
explain select *
|
|
from t1 left join t1 as t2
|
|
on (t2.a= t1.a and (t2.a,t2.b) in (select a,b from t1 as t3))
|
|
where t1.a < 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL b_c_a 413 NULL 1 100.00 Using where; Using index
|
|
1 SIMPLE t3 NULL index b_c_a b_c_a 413 NULL 1 100.00 Using where; Using index; LooseScan
|
|
1 SIMPLE t2 NULL ref b_c_a b_c_a 403 test.t3.b 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t1` `t2` semi join (`test`.`t1` `t3`)) on(((`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) where (`test`.`t1`.`a` < 5)
|
|
drop table t1;
|
|
#
|
|
# Show that loosescan planning is not dependent on order of
|
|
# creation of indexes anymore.
|
|
#
|
|
create table it(a int, b int, index a_b (a,b), index a (a))
|
|
engine=InnoDB;
|
|
insert into it values(1,1),(2,3),(4,3);
|
|
select * from it as ot
|
|
where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
|
|
a b
|
|
2 3
|
|
4 3
|
|
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
from information_schema.optimizer_trace;
|
|
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
0
|
|
drop table it;
|
|
create table it(a int, b int, index a (a),index a_b (a,b))
|
|
engine=InnoDB;
|
|
insert into it values(1,1),(2,3),(4,3);
|
|
select * from it as ot
|
|
where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
|
|
a b
|
|
2 3
|
|
4 3
|
|
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
from information_schema.optimizer_trace;
|
|
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
0
|
|
drop table it;
|
|
#
|
|
# Show that we reject LooseScan if no handled key parts
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=INNODB;
|
|
CREATE TABLE t2 (a INT, b INT) ENGINE=INNODB;
|
|
EXPLAIN SELECT * FROM t2 AS t3, t2
|
|
WHERE t2.b=t3.b AND
|
|
(t2.b) IN (SELECT b*3 FROM t1 WHERE a=10);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
1 SIMPLE t1 NULL ref a a 5 const 1 100.00 Using where; Start temporary; End temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = 10) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` = (`test`.`t1`.`b` * 3)))
|
|
SELECT TRACE LIKE '%"some_index_part_used": false%'
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
TRACE LIKE '%"some_index_part_used": false%'
|
|
1
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Show that we detect a hole in sequence of key parts
|
|
#
|
|
CREATE TABLE ot1 (a INTEGER);
|
|
INSERT INTO ot1 VALUES (0),(1),(3),(7);
|
|
CREATE TABLE it1 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
|
|
INSERT INTO it1 VALUES ('a',7), ('b',7);
|
|
CREATE TABLE it2 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
|
|
INSERT INTO it2 VALUES ('a',7), ('b',7);
|
|
explain SELECT * FROM ot1
|
|
WHERE a IN (
|
|
SELECT it1.b
|
|
FROM it1 JOIN it2
|
|
ON it1.a = it2.a
|
|
);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE it1 NULL index a a 12 NULL 2 100.00 Using index; Start temporary
|
|
1 SIMPLE ot1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop)
|
|
1 SIMPLE it2 NULL index a a 12 NULL 2 50.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a` from `test`.`ot1` semi join (`test`.`it1` join `test`.`it2`) where ((`test`.`it2`.`a` = `test`.`it1`.`a`) and (`test`.`ot1`.`a` = `test`.`it1`.`b`))
|
|
SELECT TRACE LIKE '%"index_can_remove_duplicates": false%'
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
TRACE LIKE '%"index_can_remove_duplicates": false%'
|
|
1
|
|
DROP TABLE ot1, it1, it2;
|
|
#
|
|
# Show that handled keyparts cannot be on prefix
|
|
#
|
|
create table t1 (a int, b varchar(100), key a_b (a,b)) charset utf8mb4;
|
|
insert into t1 values(25,'111111'),(25,'1111112');
|
|
explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref a_b a_b 5 const 1 100.00 Using index; LooseScan
|
|
1 SIMPLE t2 NULL index NULL a_b 408 NULL 2 50.00 Using where; Using index; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
|
|
select * from t1 as t2 where t2.b in (select b from t1 where a=25);
|
|
a b
|
|
25 111111
|
|
25 1111112
|
|
alter table t1 drop key a_b, add key a_b_prefix (a,b(2));
|
|
explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref a_b_prefix a_b_prefix 5 const 1 100.00 Start temporary
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; End temporary; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
|
|
select * from t1 as t2 where t2.b in (select b from t1 where a=25);
|
|
a b
|
|
25 111111
|
|
25 1111112
|
|
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
from information_schema.optimizer_trace;
|
|
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
|
|
1
|
|
drop table t1;
|
|
set optimizer_switch=@old_opt_switch;
|
|
set optimizer_prune_level=@old_opt_prune_level;
|
|
# Tracing of CTEs using the same tmp table: observe
|
|
# "reusing_tmp_table" and only one "creating_tmp_table".
|
|
with q(a) as (select 1 union all select 2)
|
|
select * from q, q q1 where q.a=1 and q1.a=2;
|
|
a a
|
|
1 2
|
|
select TRACE from information_schema.optimizer_trace;
|
|
TRACE
|
|
{
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 1 AS `1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select 2 AS `2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": "`q`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 4,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#4 */ select 1 AS `1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 5,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#5 */ select 2 AS `2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": "`q` `q1`",
|
|
"select#": 4,
|
|
"materialized": true,
|
|
"reusing_tmp_table": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `q`.`a` AS `a`,`q1`.`a` AS `a` from `q` join `q` `q1` where ((`q`.`a` = 1) and (`q1`.`a` = 2))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 4,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 5,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`q`.`a` = 1) and (`q1`.`a` = 2))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`q`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`q`",
|
|
"field": "a",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`q`",
|
|
"field": "a",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"field": "a",
|
|
"equals": "2",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"field": "a",
|
|
"equals": "2",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`q`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.525
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.525
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`q`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key0>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key1>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 2.725,
|
|
"rows": 2,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`q`"
|
|
] /* plan_prefix */,
|
|
"table": "`q` `q1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key0>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key1>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 2.725,
|
|
"rows": 2,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.7,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`q` `q1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key0>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "<auto_key1>",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"cost": 2.725,
|
|
"rows": 2,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"pruned_by_heuristic": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`q1`.`a` = 2) and (`q`.`a` = 1))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`q`",
|
|
"attached": "(`q`.`a` = 1)"
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"attached": "(`q1`.`a` = 2)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`q`",
|
|
"original_table_condition": "(`q`.`a` = 1)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`q` `q1`",
|
|
"original_table_condition": "(`q1`.`a` = 2)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`q`"
|
|
},
|
|
{
|
|
"table": "`q` `q1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": "`q`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 1,
|
|
"row_length": 9,
|
|
"key_length": 8,
|
|
"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 */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
}
|
|
# Tracing of repetitions of the recursive member
|
|
# can be silenced with repeated_subselect=off
|
|
with recursive qn(n) as
|
|
(select 1 union all select n+1 from qn where n<4)
|
|
select count(*) from qn;
|
|
count(*)
|
|
4
|
|
SELECT show_json_object('"join_execution": {', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"join_execution": {', TRACE)
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": "`qn`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 1,
|
|
"row_length": 9,
|
|
"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 */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
}
|
|
SET @@optimizer_trace_features="repeated_subselect=off";
|
|
with recursive qn(n) as
|
|
(select 1 union all select n+1 from qn where n<4)
|
|
select count(*) from qn;
|
|
count(*)
|
|
4
|
|
SELECT show_json_object('"join_execution": {', TRACE)
|
|
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
show_json_object('"join_execution": {', TRACE)
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": "`qn`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 1,
|
|
"row_length": 9,
|
|
"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 */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
}
|
|
SET @@optimizer_trace_features=default;
|
|
drop function show_json_object;
|