#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:guilhem@stripped
3247 Jorgen Loland 2011-01-05
WL#4800: Optimizer trace/debugger
Fix tracepoints so that all mtr test cases can be traced without
JSON syntax errors.
Added DYNAMIC_RANGE and REPEATED_SUBSELECT tracing features to
avoid repeated tracing of range analysis and execution,
respectively, of subselects.
@ sql/item_subselect.cc
Added Item_subselect::executed_before used to avoid tracing of
subquery execution when this happens repeatedly for each record
in outer select.
@ sql/item_subselect.h
Added Item_subselect::executed_before used to avoid tracing of
subquery execution when this happens repeatedly for each record
in outer select.
@ sql/opt_range.cc
Add tracepoints to range optimizer
@ sql/sql_select.cc
Add tracepoints to range optimizer
modified:
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/optimizer_trace2.result
mysql-test/r/optimizer_trace_charset.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/r/optimizer_trace_range.result
mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result
mysql-test/t/optimizer_trace_range.test
sql/item_subselect.cc
sql/item_subselect.h
sql/opt_range.cc
sql/opt_trace.cc
sql/opt_trace.h
sql/sql_select.cc
sql/sys_vars.cc
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2010-12-19 14:24:03 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2011-01-05 13:48:15 +0000
@@ -418,8 +418,9 @@ The following options may be given as th
Enables/disables tracing of selected features of the
Optimizer:
optimizer_trace_features=option=val[,option=val...],
- where option is one of {greedy_search, range_optimizer}
- and val is one of {on, off, default}
+ where option is one of {greedy_search, range_optimizer,
+ dynamic_range, repeated_subselect} and val is one of {on,
+ off, default}
--optimizer-trace-limit=#
Maximum number of shown optimizer traces
--optimizer-trace-max-mem-size=#
@@ -871,7 +872,7 @@ optimizer-prune-level 1
optimizer-search-depth 62
optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
optimizer-trace
-optimizer-trace-features greedy_search=on,range_optimizer=on
+optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on
optimizer-trace-limit 1
optimizer-trace-max-mem-size 16384
optimizer-trace-offset -1
=== modified file 'mysql-test/r/optimizer_trace2.result'
--- a/mysql-test/r/optimizer_trace2.result 2011-01-03 20:36:18 +0000
+++ b/mysql-test/r/optimizer_trace2.result 2011-01-05 13:48:15 +0000
@@ -92,6 +92,8 @@ TRACE
"cost": 1.4175,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -165,6 +167,13 @@ TRACE
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
=== modified file 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result 2011-01-03 20:36:18 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result 2011-01-05 13:48:15 +0000
@@ -52,6 +52,13 @@ explain extended select '��� from t1
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -98,6 +105,13 @@ explain extended select 'ÁÂÃÄÅ' fro
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -163,6 +177,13 @@ select * from v1 where v1.col = 'ÁÂÃ� } /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_preparation": {
"select#": 1,
"steps": [
@@ -213,6 +234,13 @@ select * from v1 where v1.col = 'ÁÂÃ� }
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -284,6 +312,8 @@ select * from t1 where c < 'ÁÂÃÄÅ'
"cost": 1.4213,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -357,6 +387,13 @@ select * from t1 where c < 'ÁÂÃÄÅ'
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -446,6 +483,8 @@ select `col
"cost": 1.4233,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -519,6 +558,13 @@ select `col
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2011-01-03 20:36:18 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2011-01-05 13:48:15 +0000
@@ -65,69 +65,94 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
}
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`test`.`t6`.`d` = NULL)",
- "after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)",
- "after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)",
- "after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"
- }
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "NULL",
- "null_rejecting": true
- }
- ]
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- },
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ]
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t6`.`d` = NULL)",
+ "after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)",
+ "after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"
+ }
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "NULL",
+ "null_rejecting": true
+ }
+ ]
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ },
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ]
+ }
+ ],
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ },
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ }
+ }
+ ],
+ "impossible_range": true
+ },
+ "records": 0,
+ "cause": "impossible_where_condition"
+ }
+ ]
+ }
+ ],
+ "empty_result": {
+ "cause": "no matching row in const table"
}
- ],
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- },
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- },
- "impossible_range": true
+ }
},
- "records": 0,
- "cause": "impossible_where_condition"
- }
- ]
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ]
+ }
+ }
+ ]
+ }
}
- ],
- "empty_result": {
- "cause": "no matching row in const table"
- }
+ ]
}
}
]
@@ -135,18 +160,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-49.6480
+60.7089
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": [{"expanded_query": "/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = `test`.`t5`.`c`)"}]}},{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = `test`.`t5`.`c`)) AS `RESULT` from `test`.`t5`"}]}},{"join_optimization": {"select#": 1,"steps": [{"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref_optimizer_key_uses": [{"database": "test","table": "t6","field": "d","equals": "NULL","null_rejecting": true}]},{"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": [{"expanded_query": "/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = `test`.`t5`.`c`)"}]}},{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = `test`.`t5`.`c`)) AS `RESULT` from `test`.`t5`"}]}},{"join_optimization": {"select#": 1,"steps": [{"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_execution": {"select#": 1,"steps": [{"subselect_exec": {"select#": 2,"steps": [{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref_optimizer_key_uses": [{"database": "test","table": "t6","field": "d","equals": "NULL","null_rejecting": true}]},{"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"setup_range_conditions": [{"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"}}],"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}},{"join_execution": {"select#": 2,"steps": []}}]}}]}}]} 0 0
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.2615
+6.2291
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -206,70 +231,88 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "ifnull(NULL,NULL)",
- "null_rejecting": false
- }
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- } /* table_scan */,
- "potential_range_indices": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
{
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "ifnull(NULL,NULL)",
+ "null_rejecting": false
}
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- } /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
- "impossible_range": true
- } /* range_analysis */,
- "records": 0,
- "cause": "impossible_where_condition"
- }
- ] /* records_estimation */
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE noticed after reading const tables"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
- ] /* steps */,
- "empty_result": {
- "cause": "Impossible WHERE noticed after reading const tables"
- } /* empty_result */
- } /* join_optimization */
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -330,70 +373,95 @@ SELECT /* should be last */ (SELECT 1 FR
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "ifnull(NULL,NULL)",
- "null_rejecting": false
- }
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- } /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
- "impossible_range": true
- } /* range_analysis */,
- "records": 0,
- "cause": "impossible_where_condition"
- }
- ] /* records_estimation */
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "ifnull(NULL,NULL)",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
+ }
+ ] /* records_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_exec */
}
- ] /* steps */,
- "empty_result": {
- "cause": "no matching row in const table"
- } /* empty_result */
- } /* join_optimization */
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -472,208 +540,251 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "ifnull(NULL,NULL)",
- "null_rejecting": false
- }
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- } /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
- "impossible_range": true
- } /* range_analysis */,
- "records": 0,
- "cause": "impossible_where_condition"
- }
- ] /* records_estimation */
- }
- ] /* steps */,
- "empty_result": {
- "cause": "no matching row in const table"
- } /* empty_result */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 3,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "ifnull(NULL,NULL)",
- "null_rejecting": false
- }
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- } /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
- "impossible_range": true
- } /* range_analysis */,
- "records": 0,
- "cause": "impossible_where_condition"
- }
- ] /* records_estimation */
- }
- ] /* steps */,
- "empty_result": {
- "cause": "no matching row in const table"
- } /* empty_result */
- } /* join_optimization */
- },
- {
- "join_preparation": {
- "select#": "fake",
- "steps": [
- {
- "expanded_query": "/* select#fake */ select `1` from dual"
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": "fake",
- "steps": [
- {
- "records_estimation": [
- {
- "database": "",
- "table": "",
- "table_scan": {
- "records": 0,
- "cost": 10
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "",
- "table": "",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 0,
- "cost": 10,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 10,
- "records_for_plan": 0,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
{
- "database": "",
- "table": "",
- "attached": null
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "ifnull(NULL,NULL)",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_optimization": {
+ "select#": 3,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "ifnull(NULL,NULL)",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": "fake",
+ "steps": [
+ {
+ "expanded_query": "/* select#fake */ select `1` from dual"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": "fake",
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "",
+ "table_scan": {
+ "records": 0,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "",
+ "table": "",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 0,
+ "cost": 10,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10,
+ "records_for_plan": 0,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "",
+ "table": "",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "",
- "table": "",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ ] /* steps */
+ } /* subselect_exec */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -769,8 +880,12 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
"after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
} /* condition_processing */
},
@@ -790,136 +905,171 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
] /* records_estimation */
},
{
- "join_optimization": {
+ "subselect_exec": {
"select#": 2,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 1))",
- "after_trivial_conditions_removal": null
- } /* condition_processing */
- }
- ] /* steps */,
- "empty_result": {
- "cause": "Impossible WHERE"
- } /* empty_result */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 3,
- "steps": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 1))",
+ "after_trivial_conditions_removal": null
+ } /* condition_processing */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE"
+ } /* empty_result */
+ } /* join_optimization */
+ },
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 2))",
- "after_trivial_conditions_removal": null
- } /* condition_processing */
- }
- ] /* steps */,
- "empty_result": {
- "cause": "Impossible WHERE"
- } /* empty_result */
- } /* join_optimization */
- },
- {
- "join_preparation": {
- "select#": "fake",
- "steps": [
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
{
- "expanded_query": "/* select#fake */ select `Not_used` from dual"
+ "join_optimization": {
+ "select#": 3,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 2))",
+ "after_trivial_conditions_removal": null
+ } /* condition_processing */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE"
+ } /* empty_result */
+ } /* join_optimization */
},
{
- "transformation": {
- "select#": "fake",
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": false
- } /* transformation */
+ "join_execution": {
+ "select#": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
},
{
- "transformation": {
+ "join_preparation": {
"select#": "fake",
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": false
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": "fake",
- "steps": [
- {
- "records_estimation": [
- {
- "database": "",
- "table": "",
- "table_scan": {
- "records": 0,
- "cost": 10
- } /* table_scan */
- }
- ] /* records_estimation */
+ "steps": [
+ {
+ "expanded_query": "/* select#fake */ select `Not_used` from dual"
+ },
+ {
+ "transformation": {
+ "select#": "fake",
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": "fake",
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
},
{
- "considered_execution_plans": [
- {
- "database": "",
- "table": "",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": "fake",
+ "steps": [
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 0,
- "cost": 10,
+ "database": "",
+ "table": "",
+ "table_scan": {
+ "records": 0,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "",
+ "table": "",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 0,
+ "cost": 10,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10,
+ "records_for_plan": 0,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 10,
- "records_for_plan": 0,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "",
- "table": "",
- "attached": null
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "",
+ "table": "",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "",
- "table": "",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* subselect_exec */
}
] /* steps */,
"empty_result": {
"cause": "Impossible WHERE noticed after reading const tables"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -928,7 +1078,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-9694
+11607
set optimizer_trace_max_mem_size=9300;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -937,7 +1087,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-517
+2431
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -945,7 +1095,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-9177 1 1
+9176 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@query2) + length(@trace2)
@@ -960,14 +1110,14 @@ select 1;
1
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select 1 390 0
+select 1 521 0
set optimizer_trace_max_mem_size=0;
select 1;
1
1
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
- 398 0
+ 529 0
set optimizer_trace_max_mem_size=1048576;
explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
@@ -1068,6 +1218,8 @@ explain SELECT c FROM t5 where c+1 in (s
"cost": 1.4233,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -1232,7 +1384,14 @@ explain SELECT c FROM t5 where c+1 in (s
] /* refine_plan */
}
] /* steps */
- } /* join_optimization */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1289,8 +1448,12 @@ explain SELECT c FROM t5 where c+1 in (s
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
"after_constant_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
} /* condition_processing */
},
@@ -1332,132 +1495,148 @@ explain SELECT c FROM t5 where c+1 in (s
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "isnull(`test`.`t6`.`d`)",
- "after_equality_propagation": "isnull(`test`.`t6`.`d`)",
- "after_constant_propagation": "isnull(`test`.`t6`.`d`)",
- "after_trivial_conditions_removal": "isnull(`test`.`t6`.`d`)"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- {
- "database": "test",
- "table": "t6",
- "field": "d",
- "equals": "NULL",
- "null_rejecting": false
- }
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 2,
- "cost": 4.5034
- } /* table_scan */,
- "potential_range_indices": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "isnull(`test`.`t6`.`d`)",
+ "after_equality_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_constant_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "isnull(`test`.`t6`.`d`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
{
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
+ "database": "test",
+ "table": "t6",
+ "field": "d",
+ "equals": "NULL",
+ "null_rejecting": false
}
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.4233,
- "chosen": true
- } /* best_covering_index_scan */,
- "group_index_range": {
- "chosen": false,
- "cause": "not_group_by_or_distinct"
- } /* group_index_range */,
- "analyzing_range_alternatives": {
- "range_scan_alternatives": [
- {
- "index": "d",
- "ranges": [
- "NULL <= d <= NULL"
- ] /* ranges */,
- "index_only": true,
- "records": 1,
- "cost": 2.21,
- "rowid_ordered": true,
- "chosen": false,
- "cause": "cost"
- }
- ] /* range_scan_alternatives */,
- "analyzing_roworder_intersect": {
- "usable": false,
- "cause": "too_few_roworder_scans"
- } /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
- } /* range_analysis */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t6",
- "best_access_path": {
- "considered_access_paths": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
{
- "access_type": "ref",
- "index": "d",
- "records": 1,
- "cost": 1,
- "chosen": true
- },
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
{
- "access_type": "scan",
- "cost": 2,
- "records": 2,
- "cause": "cost",
- "chosen": false
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "d",
+ "records": 1,
+ "cost": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1,
- "records_for_plan": 1,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "isnull(`test`.`t6`.`d`)",
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t6",
- "attached": "isnull(`test`.`t6`.`d`)"
+ "attaching_conditions_to_tables": {
+ "original_condition": "isnull(`test`.`t6`.`d`)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": "isnull(`test`.`t6`.`d`)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t6"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1508,81 +1687,23 @@ explain extended select * from t1 where
"select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
- "after_equality_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
- "after_constant_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
- "after_trivial_conditions_removal": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0054,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0054,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
- "attached_conditions": [
- {
- "database": "test",
- "table": "t1",
- "attached": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "after_constant_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
"records_estimation": [
{
"database": "test",
@@ -1618,12 +1739,12 @@ explain extended select * from t1 where
},
{
"attaching_conditions_to_tables": {
- "original_condition": null,
+ "original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
"attached_conditions": [
{
"database": "test",
"table": "t1",
- "attached": null
+ "attached": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -1639,6 +1760,82 @@ explain extended select * from t1 where
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1688,8 +1885,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
"after_constant_propagation": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
} /* condition_processing */
},
@@ -1756,66 +1957,80 @@ explain extended select * from t1 where
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0054,
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0054,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t1",
- "attached": null
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1882,8 +2097,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
"after_constant_propagation": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
} /* condition_processing */
},
@@ -1950,79 +2169,93 @@ explain extended select * from t1 where
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
- "after_equality_propagation": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
- "after_constant_propagation": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
- "after_trivial_conditions_removal": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
+ "after_equality_propagation": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
+ "after_constant_propagation": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
+ "after_trivial_conditions_removal": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0054,
- "chosen": true
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0054,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
- "attached_conditions": [
- {
- "database": "test",
- "table": "t1",
- "attached": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2088,8 +2321,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
"after_constant_propagation": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
} /* condition_processing */
},
@@ -2156,79 +2393,93 @@ explain extended select * from t1 where
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
- "after_equality_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
- "after_constant_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
- "after_trivial_conditions_removal": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_equality_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_constant_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_trivial_conditions_removal": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0054,
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0054,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t1",
- "attached": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
+ "attaching_conditions_to_tables": {
+ "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2392,12 +2643,19 @@ explain select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -2477,6 +2735,13 @@ explain select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2577,8 +2842,12 @@ where a1 in (select b1 from t2_16 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
"after_constant_propagation": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
} /* condition_processing */
},
@@ -2645,79 +2914,93 @@ where a1 in (select b1 from t2_16 where
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
- "after_equality_propagation": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
- "after_constant_propagation": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
- "after_trivial_conditions_removal": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t2_16",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2_16",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
+ "after_equality_propagation": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
+ "after_constant_propagation": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
+ "after_trivial_conditions_removal": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0293,
+ "database": "test",
+ "table": "t2_16",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2_16",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0293,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0293,
+ "records_for_plan": 3,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0293,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t2_16",
- "attached": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2_16",
+ "attached": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2_16",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t2_16",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2793,8 +3076,12 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
"condition_processing": {
"condition": "WHERE",
"original_condition": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and (`test`.`t1`.`c1` = `test`.`t2`.`c2`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))",
"after_constant_propagation": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))"
} /* condition_processing */
},
@@ -2916,79 +3203,100 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`))",
- "after_equality_propagation": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))",
- "after_constant_propagation": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))",
- "after_trivial_conditions_removal": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t2",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0051,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0051,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))",
- "attached_conditions": [
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
{
- "database": "test",
- "table": "t2",
- "attached": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))"
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`))",
+ "after_equality_propagation": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))",
+ "after_constant_propagation": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))",
+ "after_trivial_conditions_removal": "((<cache>(`test`.`t1`.`c1`) = 1) and multiple equal(1, `test`.`t2`.`c2`))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0051,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0051,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t2",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ ] /* steps */
+ } /* subselect_exec */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3095,6 +3403,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_optimization": {
"select#": 2,
"steps": [
@@ -3170,6 +3485,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_preparation": {
"select#": "fake",
"steps": [
@@ -3240,6 +3562,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3335,8 +3664,12 @@ concat(c1,'y') IN
"condition_processing": {
"condition": "WHERE",
"original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
"after_constant_propagation": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
} /* condition_processing */
},
@@ -3360,147 +3693,7 @@ concat(c1,'y') IN
"considered_execution_plans": [
{
"database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0051,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0051,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
- "attached_conditions": [
- {
- "database": "test",
- "table": "t1",
- "attached": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
- },
- {
- "transformation": {
- "select#": 3,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": true
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 3,
- "steps": [
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t2",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0051,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0051,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
- {
- "database": "test",
- "table": "t2",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t2",
- "scan_type": "table"
- }
- ] /* refine_plan */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t2",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
+ "table": "t1",
"best_access_path": {
"considered_access_paths": [
{
@@ -3520,12 +3713,12 @@ concat(c1,'y') IN
},
{
"attaching_conditions_to_tables": {
- "original_condition": null,
+ "original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
"attached_conditions": [
{
"database": "test",
- "table": "t2",
- "attached": null
+ "table": "t1",
+ "attached": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -3534,13 +3727,174 @@ concat(c1,'y') IN
"refine_plan": [
{
"database": "test",
- "table": "t2",
+ "table": "t1",
"scan_type": "table"
}
] /* refine_plan */
+ },
+ {
+ "transformation": {
+ "select#": 3,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": true
+ } /* transformation */
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 3,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0051,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0051,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0051,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0051,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3602,6 +3956,13 @@ select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3795,6 +4156,13 @@ trace
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -3905,6 +4273,13 @@ trace
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4041,6 +4416,13 @@ select * from t1 left join t2 on t2.a=50
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4071,128 +4453,49 @@ select * from t1 where (t1.a,t1.b) not i
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
- "chosen": false
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": false
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "EXISTS (CORRELATED SELECT)",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
- "after_equality_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
- "after_constant_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
- "after_trivial_conditions_removal": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
- } /* condition_processing */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0044,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0044,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
- "attached_conditions": [
+ "chosen": false
+ } /* transformation */
+ },
{
- "database": "test",
- "table": "t1",
- "attached": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true
+ } /* transformation */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_preparation */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
}
] /* steps */
- } /* join_optimization */
+ } /* join_preparation */
},
{
"join_optimization": {
- "select#": 2,
+ "select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
- "after_equality_propagation": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
- "after_constant_propagation": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
- "after_trivial_conditions_removal": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
- } /* condition_processing */
- },
- {
- "condition_processing": {
- "condition": "HAVING",
- "original_condition": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))",
- "after_constant_propagation": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))",
- "after_trivial_conditions_removal": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))"
+ "original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
+ "after_constant_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
} /* condition_processing */
},
{
@@ -4203,7 +4506,7 @@ select * from t1 where (t1.a,t1.b) not i
"records_estimation": [
{
"database": "test",
- "table": "t2",
+ "table": "t1",
"table_scan": {
"records": 2,
"cost": 2
@@ -4215,7 +4518,7 @@ select * from t1 where (t1.a,t1.b) not i
"considered_execution_plans": [
{
"database": "test",
- "table": "t2",
+ "table": "t1",
"best_access_path": {
"considered_access_paths": [
{
@@ -4235,12 +4538,12 @@ select * from t1 where (t1.a,t1.b) not i
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
+ "original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
"attached_conditions": [
{
"database": "test",
- "table": "t2",
- "attached": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
+ "table": "t1",
+ "attached": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4249,13 +4552,117 @@ select * from t1 where (t1.a,t1.b) not i
"refine_plan": [
{
"database": "test",
- "table": "t2",
+ "table": "t1",
"scan_type": "table"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
+ "after_equality_propagation": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
+ "after_constant_propagation": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
+ "after_trivial_conditions_removal": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
+ } /* condition_processing */
+ },
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))",
+ "after_constant_propagation": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))",
+ "after_trivial_conditions_removal": "(trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0044,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0044,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4416,6 +4823,13 @@ trace
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4462,6 +4876,13 @@ trace
"cause": "Impossible HAVING"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4488,6 +4909,8 @@ update t6 set d=5 where d is NULL {
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "no_join"
@@ -4550,6 +4973,8 @@ delete from t6 where d=5 {
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "no_join"
@@ -4649,6 +5074,8 @@ insert into t6 select * from t6 where d>
"cost": 1.6465,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -4722,6 +5149,13 @@ insert into t6 select * from t6 where d>
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4783,6 +5217,8 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -4867,6 +5303,13 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4933,6 +5376,8 @@ delete t6 from t5, t6 where d>7000 {
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -5006,6 +5451,13 @@ delete t6 from t5, t6 where d>7000 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5047,6 +5499,13 @@ select 3 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5068,6 +5527,13 @@ select 4 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5109,6 +5575,13 @@ select 4 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5130,6 +5603,13 @@ select 5 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5233,6 +5713,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5316,6 +5803,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5396,6 +5890,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5493,6 +5994,8 @@ select * from t6 where d in (select f1()
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -5759,6 +6262,13 @@ select * from t6 where d in (select f1()
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5853,6 +6363,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5947,6 +6464,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6041,11 +6565,15 @@ select d+1 into res from t6 where d= NAM
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
"impossible_range": true
} /* range_analysis */,
"records": 0,
@@ -6058,6 +6586,13 @@ select d+1 into res from t6 where d= NAM
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6141,6 +6676,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6224,11 +6766,15 @@ select @trace;
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
"impossible_range": true
} /* range_analysis */,
"records": 0,
@@ -6241,6 +6787,13 @@ select @trace;
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -6357,6 +6910,8 @@ select d into res from t6 where d in (se
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -6588,6 +7143,13 @@ select d into res from t6 where d in (se
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6682,6 +7244,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6776,6 +7345,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6888,6 +7464,13 @@ explain select * from v1 where id="b" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -7038,6 +7621,13 @@ explain select * from v1 where id="b" {
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_preparation": {
"select#": 1,
"steps": [
@@ -7121,6 +7711,20 @@ explain select * from v1 where id="b" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -7218,6 +7822,13 @@ VARIABLE_NAME="optimizer_trace" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -7257,6 +7868,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ },
+ {
"join_optimization": {
"select#": 2,
"steps": [
@@ -7264,6 +7882,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ]
+ }
+ },
+ {
"join_preparation": {
"select#": "fake",
"steps": [
@@ -7334,6 +7959,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
]
}
+ },
+ {
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ]
+ }
}
]
}set optimizer_switch='default,index_merge=on,index_merge=off,default';
@@ -7361,6 +7993,13 @@ select "abc11111111111111111111111111111
"steps": [
]
}
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
}
]
} 0 0
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-03 20:36:18 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-05 13:48:15 +0000
@@ -753,8 +753,12 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
"after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
} /* condition_processing */
},
@@ -1052,6 +1056,8 @@ explain SELECT c FROM t5 where c+1 in (s
"cost": 1.4233,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -1217,6 +1223,13 @@ explain SELECT c FROM t5 where c+1 in (s
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1273,8 +1286,12 @@ explain SELECT c FROM t5 where c+1 in (s
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
"after_constant_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
} /* condition_processing */
},
@@ -1495,8 +1512,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
"after_constant_propagation": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
} /* condition_processing */
},
@@ -1563,66 +1584,80 @@ explain extended select * from t1 where
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 2,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "records_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "records": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 2,
- "cost": 2.0054,
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0054,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t1",
- "attached": null
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1779,12 +1814,12 @@ explain extended select * from t1 where
},
{
"attaching_conditions_to_tables": {
- "original_condition": null,
+ "original_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
"attached_conditions": [
{
"database": "test",
"table": "t1",
- "attached": null
+ "attached": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -1800,6 +1835,82 @@ explain extended select * from t1 where
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1866,8 +1977,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
"after_constant_propagation": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
} /* condition_processing */
},
@@ -2072,8 +2187,12 @@ explain extended select * from t1 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
"after_constant_propagation": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
} /* condition_processing */
},
@@ -2213,6 +2332,95 @@ explain extended select * from t1 where
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_equality_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_constant_propagation": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "after_trivial_conditions_removal": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0054,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0054,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2376,12 +2584,19 @@ explain select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -2461,6 +2676,13 @@ explain select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -2561,8 +2783,12 @@ where a1 in (select b1 from t2_16 where
"condition_processing": {
"condition": "WHERE",
"original_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
"after_constant_propagation": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
} /* condition_processing */
},
@@ -2777,8 +3003,12 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
"condition_processing": {
"condition": "WHERE",
"original_condition": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and (`test`.`t1`.`c1` = `test`.`t2`.`c2`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))",
"after_constant_propagation": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))"
} /* condition_processing */
},
@@ -3079,6 +3309,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_optimization": {
"select#": 2,
"steps": [
@@ -3154,6 +3391,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_preparation": {
"select#": "fake",
"steps": [
@@ -3224,6 +3468,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3319,8 +3570,12 @@ concat(c1,'y') IN
"condition_processing": {
"condition": "WHERE",
"original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
"after_constant_propagation": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
} /* condition_processing */
},
@@ -3403,128 +3658,149 @@ concat(c1,'y') IN
} /* join_optimization */
},
{
- "join_optimization": {
- "select#": 3,
+ "join_execution": {
+ "select#": 1,
"steps": [
{
- "records_estimation": [
- {
- "database": "test",
- "table": "t2",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
+ "join_optimization": {
+ "select#": 3,
+ "steps": [
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0051,
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0051,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0051,
+ "records_for_plan": 3,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0051,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t2",
- "attached": null
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t2",
- "scan_type": "table"
- }
- ] /* refine_plan */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "records_estimation": [
- {
- "database": "test",
- "table": "t2",
- "table_scan": {
- "records": 3,
- "cost": 2
- } /* table_scan */
- }
- ] /* records_estimation */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
+ "join_execution": {
+ "select#": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
{
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0051,
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0051,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0051,
+ "records_for_plan": 3,
"chosen": true
}
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0051,
- "records_for_plan": 3,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
+ ] /* considered_execution_plans */
+ },
{
- "database": "test",
- "table": "t2",
- "attached": null
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
+ ] /* steps */
+ } /* join_optimization */
},
{
- "refine_plan": [
- {
- "database": "test",
- "table": "t2",
- "scan_type": "table"
- }
- ] /* refine_plan */
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
- } /* join_optimization */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3586,6 +3862,13 @@ select * from t1,t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -3779,6 +4062,13 @@ trace
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -3889,6 +4179,13 @@ trace
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4025,6 +4322,13 @@ select * from t1 left join t2 on t2.a=50
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4083,8 +4387,12 @@ select * from t1 where (t1.a,t1.b) not i
"condition_processing": {
"condition": "WHERE",
"original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_equality_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
"after_constant_propagation": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
"after_trivial_conditions_removal": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
} /* condition_processing */
},
@@ -4392,6 +4700,13 @@ trace
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4438,6 +4753,13 @@ trace
"cause": "Impossible HAVING"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -4464,6 +4786,8 @@ update t6 set d=5 where d is NULL {
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "no_join"
@@ -4526,6 +4850,8 @@ delete from t6 where d=5 {
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "no_join"
@@ -4625,6 +4951,8 @@ insert into t6 select * from t6 where d>
"cost": 1.6465,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -4698,6 +5026,13 @@ insert into t6 select * from t6 where d>
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4759,6 +5094,8 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -4843,6 +5180,13 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -4909,6 +5253,8 @@ delete t6 from t5, t6 where d>7000 {
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -4982,6 +5328,13 @@ delete t6 from t5, t6 where d>7000 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5037,6 +5390,13 @@ select 2 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5092,6 +5452,13 @@ select 5 {
"steps": [
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5195,6 +5562,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5278,6 +5652,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5358,6 +5739,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5455,6 +5843,8 @@ select * from t6 where d in (select f1()
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -5721,6 +6111,13 @@ select * from t6 where d in (select f1()
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5815,6 +6212,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5909,6 +6313,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -5993,11 +6404,15 @@ select d+1 into res from t6 where d= NAM
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
"impossible_range": true
} /* range_analysis */,
"records": 0,
@@ -6010,6 +6425,13 @@ select d+1 into res from t6 where d= NAM
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6093,6 +6515,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6176,11 +6605,15 @@ select @trace;
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
- "impossible_condition": {
- "table": "t6",
- "field": "d",
- "cause": "comparison_with_null_always_false"
- } /* impossible_condition */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
"impossible_range": true
} /* range_analysis */,
"records": 0,
@@ -6193,6 +6626,13 @@ select @trace;
"cause": "no matching row in const table"
} /* empty_result */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
}
@@ -6309,6 +6749,8 @@ select d into res from t6 where d in (se
"cost": 1.8698,
"chosen": true
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
@@ -6540,6 +6982,13 @@ select d into res from t6 where d in (se
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6634,6 +7083,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6728,6 +7184,13 @@ select sum(data) into ret from t1 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6840,6 +7303,13 @@ explain select * from v1 where id="b" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -6972,6 +7442,13 @@ explain select * from v1 where id="b" {
} /* join_optimization */
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
"join_preparation": {
"select#": 1,
"steps": [
@@ -7055,6 +7532,20 @@ explain select * from v1 where id="b" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -7152,6 +7643,13 @@ VARIABLE_NAME="optimizer_trace" {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -7191,6 +7689,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ },
+ {
"join_optimization": {
"select#": 2,
"steps": [
@@ -7198,6 +7703,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ]
+ }
+ },
+ {
"join_preparation": {
"select#": "fake",
"steps": [
@@ -7268,6 +7780,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
]
}
+ },
+ {
+ "join_execution": {
+ "select#": "fake",
+ "steps": [
+ ]
+ }
}
]
}set optimizer_switch='default,index_merge=on,index_merge=off,default';
@@ -7295,6 +7814,13 @@ select "abc11111111111111111111111111111
"steps": [
]
}
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
}
]
} 0 0
=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 2011-01-03 20:36:18 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2011-01-05 13:48:15 +0000
@@ -84,7 +84,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
{
"index": "i1",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2",
@@ -96,34 +96,36 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
{
"index": "i3",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i4",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i5",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i6",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i7",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i8",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -210,6 +212,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -261,6 +270,8 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
"cost": 217.15
} /* table_scan */,
"potential_range_indices": "...",
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": "...",
"analyzing_range_alternatives": "...",
"chosen_range_access_summary": {
@@ -325,6 +336,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -393,34 +411,36 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3
{
"index": "i3",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i4",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i5",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i6",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i7",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i8",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -543,6 +563,13 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -590,12 +617,12 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
{
"index": "PRIMARY",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i1b",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_1",
@@ -731,6 +758,13 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -777,12 +811,12 @@ EXPLAIN SELECT DISTINCT key2 FROM t2 {
{
"index": "PRIMARY",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i1b",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_1",
@@ -918,6 +952,13 @@ EXPLAIN SELECT DISTINCT key2 FROM t2 {
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -971,12 +1012,12 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
{
"index": "PRIMARY",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i1b",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_1",
@@ -1001,6 +1042,8 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
"chosen": false,
"cause": "cost"
} /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"potential_group_range_indices": [
{
@@ -1143,6 +1186,13 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1210,12 +1260,12 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
{
"index": "PRIMARY",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i1b",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_1",
@@ -1234,6 +1284,8 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -1342,6 +1394,13 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1395,7 +1454,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
{
"index": "i1",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2",
@@ -1421,29 +1480,33 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
{
"index": "i5",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i6",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i7",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i8",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
- "impossible_condition": {
- "table": "t1",
- "field": "key4",
- "cause": "null_field_in_non_null_column"
- } /* impossible_condition */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t1",
+ "field": "key4",
+ "cause": "null_field_in_non_null_column"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -1594,6 +1657,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1647,12 +1717,12 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 <
{
"index": "PRIMARY",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i1b",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_1",
@@ -1671,6 +1741,8 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 <
] /* key_parts */
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"range_scan_possible": false,
"cause": "condition_always_true",
"group_index_range": {
@@ -1726,6 +1798,13 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 <
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1802,14 +1881,16 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
{
"index": "i2_1",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i2_2",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -1913,6 +1994,13 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
@@ -1980,34 +2068,36 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
{
"index": "i3",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i4",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i5",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i6",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i7",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
},
{
"index": "i8",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -2106,34 +2196,34 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
-DROP TABLE t1,t2;
-CREATE TABLE t1 (
-cola char(3) not null,
-colb char(3) not null,
-filler char(200),
-key(cola),
-key(colb)
-);
-INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
-Inserting records
-EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
+1 SIMPLE t1 range i1,i2 i2 4 NULL 42 Using index condition; Using MRR
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.key1 10
SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020 {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
- "expanded_query": "/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))"
+ "expanded_query": "/* select#1 */ select straight_join `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8`,`test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`key1` = `test`.`t2`.`key1a`) and (`test`.`t1`.`key2` > 1020))"
}
] /* steps */
} /* join_preparation */
@@ -2145,10 +2235,10 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))",
- "after_equality_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
- "after_constant_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
- "after_trivial_conditions_removal": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ "original_condition": "((`test`.`t1`.`key1` = `test`.`t2`.`key1a`) and (`test`.`t1`.`key2` > 1020))",
+ "after_equality_propagation": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))",
+ "after_constant_propagation": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))",
+ "after_trivial_conditions_removal": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))"
} /* condition_processing */
},
{
@@ -2156,15 +2246,15 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
{
"database": "test",
"table": "t1",
- "field": "cola",
- "equals": "'foo'",
+ "field": "key1",
+ "equals": "`test`.`t2`.`key1a`",
"null_rejecting": false
},
{
"database": "test",
- "table": "t1",
- "field": "colb",
- "equals": "'bar'",
+ "table": "t2",
+ "field": "key1a",
+ "equals": "`test`.`t1`.`key1`",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
@@ -2176,113 +2266,100 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
"table": "t1",
"range_analysis": {
"table_scan": {
- "records": 8704,
- "cost": 2184.8
+ "records": 1024,
+ "cost": 217.15
} /* table_scan */,
"potential_range_indices": [
{
- "index": "cola",
- "usable": true,
- "key_parts": [
- "cola"
- ] /* key_parts */
+ "index": "i1",
+ "usable": false,
+ "cause": "not_applicable"
},
{
- "index": "colb",
+ "index": "i2",
"usable": true,
"key_parts": [
- "colb"
+ "key2"
] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
- "cause": "not_group_by_or_distinct"
+ "cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
- "index": "cola",
+ "index": "i2",
"ranges": [
- "foo <= cola <= foo"
+ "1020 < key2"
] /* ranges */,
"index_only": false,
- "records": 533,
- "cost": 640.61,
- "rowid_ordered": true,
+ "records": 42,
+ "cost": 51.41,
+ "rowid_ordered": false,
"chosen": true
- },
- {
- "index": "colb",
- "ranges": [
- "bar <= colb <= bar"
- ] /* ranges */,
- "index_only": false,
- "records": 533,
- "cost": 640.61,
- "rowid_ordered": true,
- "chosen": false,
- "cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
- "intersecting_indices": [
- {
- "index": "cola",
- "usable": true,
- "matching_records_now": 533,
- "cumulated_cost": 320.38,
- "isect_covering_with_this_index": false
- },
- {
- "index": "colb",
- "usable": true,
- "matching_records_now": 32.639,
- "cumulated_cost": 53.359,
- "isect_covering_with_this_index": false
- }
- ] /* intersecting_indices */,
- "clustered_pk": {
- "clustered_pk_added_to_intersect": false,
- "cause": "no_clustered_pk_index"
- } /* clustered_pk */,
- "records": 32,
- "cost": 53.359,
- "covering": false,
- "chosen": true
+ "usable": false,
+ "cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
- "type": "index_roworder_intersect",
- "records": 32,
- "cost": 53.359,
- "covering": false,
- "clustered_pk_scan": false,
- "intersect_of": [
- {
- "type": "range_scan",
- "index": "cola",
- "records": 533,
- "ranges": [
- "foo <= cola <= foo"
- ] /* ranges */
- },
- {
- "type": "range_scan",
- "index": "colb",
- "records": 533,
- "ranges": [
- "bar <= colb <= bar"
- ] /* ranges */
- }
- ] /* intersect_of */
+ "type": "range_scan",
+ "index": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */
} /* range_access_plan */,
- "records_for_plan": 32,
- "cost_for_plan": 53.359,
+ "records_for_plan": 42,
+ "cost_for_plan": 51.41,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 1024,
+ "cost": 8
+ } /* table_scan */
}
] /* records_estimation */
},
@@ -2295,40 +2372,64 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
"considered_access_paths": [
{
"access_type": "ref",
- "index": "cola",
- "records": 533,
- "cost": 533,
- "chosen": true
- },
- {
- "access_type": "ref",
- "index": "colb",
- "records": 533,
- "cost": 533,
+ "index": "i1",
+ "usable": false,
"chosen": false
},
{
"access_type": "range",
- "records": 24,
- "cost": 54.959,
+ "records": 42,
+ "cost": 51.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 54.959,
- "records_for_plan": 24,
- "chosen": true
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "cost_for_plan": 51.41,
+ "records_for_plan": 42
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 10,
+ "cost": 430.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 768,
+ "cost": 59.535,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "cost_for_plan": 481.49,
+ "records_for_plan": 420
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))",
+ "original_condition": "((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))",
"attached_conditions": [
{
"database": "test",
"table": "t1",
- "attached": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))"
+ "attached": "(`test`.`t1`.`key2` > 1020)"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -2339,17 +2440,275 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
"database": "test",
"table": "t1",
"scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
-
-EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
-id select_type table type possible_keys key key_len ref rows Extra
+DROP TABLE t1,t2;
+CREATE TABLE t1 (
+cola char(3) not null,
+colb char(3) not null,
+filler char(200),
+key(cola),
+key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+Inserting records
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))",
+ "after_equality_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
+ "after_constant_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
+ "after_trivial_conditions_removal": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "cola",
+ "equals": "'foo'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "colb",
+ "equals": "'bar'",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8704,
+ "cost": 2184.8
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "key_parts": [
+ "cola"
+ ] /* key_parts */
+ },
+ {
+ "index": "colb",
+ "usable": true,
+ "key_parts": [
+ "colb"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "colb",
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "matching_records_now": 533,
+ "cumulated_cost": 320.38,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "colb",
+ "usable": true,
+ "matching_records_now": 32.639,
+ "cumulated_cost": 53.359,
+ "isect_covering_with_this_index": false
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no_clustered_pk_index"
+ } /* clustered_pk */,
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "cola",
+ "records": 533,
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "colb",
+ "records": 533,
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 32,
+ "cost_for_plan": 53.359,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "cola",
+ "records": 533,
+ "cost": 533,
+ "chosen": true
+ },
+ {
+ "access_type": "ref",
+ "index": "colb",
+ "records": 533,
+ "cost": 533,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "records": 24,
+ "cost": 54.959,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 54.959,
+ "records_for_plan": 24,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
+id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref cola cola 3 const 1 Using index condition
SELECT * FROM information_schema.OPTIMIZER_TRACE;
@@ -2411,9 +2770,11 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
{
"index": "colb",
"usable": false,
- "cause": "not_usable_by_conditions"
+ "cause": "not_applicable"
}
] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
@@ -2503,8 +2864,1657 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
}
] /* steps */
} /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
}
] /* steps */
} 0 0
DROP TABLE t1;
-set optimizer_trace=default;
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b));
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL bkey NULL NULL NULL 32 Using where; Using filesort
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` between 1 and 2) order by `test`.`t1`.`b`,`test`.`t1`.`a`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`b` between 1 and 2)",
+ "after_equality_propagation": "(`test`.`t1`.`b` between 1 and 2)",
+ "after_constant_propagation": "(`test`.`t1`.`b` between 1 and 2)",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`b` between 1 and 2)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 32,
+ "cost": 10.57
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "bkey",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "bkey",
+ "ranges": [
+ "1 <= b <= 2"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 31,
+ "cost": 38.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 31,
+ "cost": 2.2703,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.2703,
+ "records_for_plan": 31,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`b` between 1 and 2)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`b` between 1 and 2)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1;
+CREATE TABLE t1(c INT);
+INSERT INTO t1 VALUES (),();
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (),(),();
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+2 DERIVED t2 ALL b NULL NULL NULL 3 Range checked for each record (index map: 0x1)
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_equality_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_constant_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_trivial_conditions_removal": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "rechecking_index_usage": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 5.6
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "b",
+ "index_only": true,
+ "records": 18446744073709551615,
+ "cost": 0,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* rechecking_index_usage */,
+ "attached": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t2",
+ "field": "b",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ },
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "table": "t2",
+ "field": "b",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ }
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1) `d2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "d2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+2 DERIVED t2 ALL b NULL NULL NULL 3 Range checked for each record (index map: 0x1)
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_equality_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_constant_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "after_trivial_conditions_removal": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "rechecking_index_usage": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 5.6
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "b",
+ "index_only": true,
+ "records": 18446744073709551615,
+ "cost": 0,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* rechecking_index_usage */,
+ "attached": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation_per_record": "..."
+ },
+ {
+ "records_estimation_per_record": "..."
+ }
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1) `d2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "d2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
+CREATE TABLE `t1` (
+`mot` varchar(4) NOT NULL,
+`topic` int NOT NULL,
+PRIMARY KEY (`mot`,`topic`)
+);
+INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
+mot topic
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic`"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`mot` AS `mot`,`test`.`t1`.`topic` AS `topic` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "after_constant_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))",
+ "after_constant_propagation": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))",
+ "after_trivial_conditions_removal": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
+ } /* condition_processing */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5098
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "mot",
+ "topic"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4303,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "covering": true,
+ "usable": false,
+ "cause": "group_attribute_not_prefix_in_index"
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "rechecking_index_usage": {
+ "database": "test",
+ "table": "t1",
+ "no_range_analysis": {
+ "cause": "no_indices_to_analyze"
+ } /* no_range_analysis */
+ } /* rechecking_index_usage */,
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_exec */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+drop table t1;
+CREATE TABLE t1 (
+i1 int,
+i2 int,
+c char(1),
+KEY k1 (i1),
+KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range k1,k2 k2 5 NULL 2 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`i1` > '2') order by `test`.`t1`.`i1`,`test`.`t1`.`i2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`i1` > '2')",
+ "after_equality_propagation": "(`test`.`t1`.`i1` > '2')",
+ "after_constant_propagation": "(`test`.`t1`.`i1` > '2')",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`i1` > '2')"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "k2",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* 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": "k1",
+ "records": 2,
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 3.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`i1` > '2')",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`i1` > '2')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k2",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "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": "k2",
+ "records": 2,
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 3.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index k1,k2 k1 5 NULL 2 Using where; Using index
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i1` >= '1') order by `test`.`t1`.`i1` desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`i1` >= '1')",
+ "after_equality_propagation": "(`test`.`t1`.`i1` >= '1')",
+ "after_constant_propagation": "(`test`.`t1`.`i1` >= '1')",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`i1` >= '1')"
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "k2",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`i1` >= '1')",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`i1` >= '1')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1;
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2010-11-23 10:21:53 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2011-01-05 13:48:15 +0000
@@ -1,45 +1,45 @@
SET @start_global_value = @@global.optimizer_trace_features;
SELECT @start_global_value;
@start_global_value
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
show global variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features greedy_search=on,range_optimizer=on
+optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
show session variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features greedy_search=on,range_optimizer=on
+optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
select * from information_schema.global_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
select * from information_schema.session_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
set global optimizer_trace_features=2;
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=off,range_optimizer=on
+greedy_search=off,range_optimizer=on,dynamic_range=off,repeated_subselect=off
set session optimizer_trace_features=2;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-greedy_search=off,range_optimizer=on
+greedy_search=off,range_optimizer=on,dynamic_range=off,repeated_subselect=off
set global optimizer_trace_features=0;
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off,dynamic_range=off,repeated_subselect=off
set session optimizer_trace_features=0;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off,dynamic_range=off,repeated_subselect=off
set session optimizer_trace_features=default;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off,dynamic_range=off,repeated_subselect=off
set global optimizer_trace_features=1.1;
ERROR 42000: Incorrect argument type to variable 'optimizer_trace_features'
set global optimizer_trace_features=1e1;
@@ -49,4 +49,4 @@ ERROR 42000: Variable 'optimizer_trace_f
SET @@global.optimizer_trace_features = @start_global_value;
SELECT @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test 2010-11-22 09:59:17 +0000
+++ b/mysql-test/t/optimizer_trace_range.test 2011-01-05 13:48:15 +0000
@@ -126,6 +126,13 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
--echo
SELECT * FROM information_schema.OPTIMIZER_TRACE;
+# Range analysis on straight join
+--echo
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+ WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
DROP TABLE t1,t2;
CREATE TABLE t1 (
@@ -168,4 +175,79 @@ SELECT * FROM information_schema.OPTIMIZ
DROP TABLE t1;
-set optimizer_trace=default;
+--echo
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b));
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+
+--echo
+# Make quick select for ordering inside test_if_skip_sort_order
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+# Test that range optimization is not shown for every outer record
+# when there is a dynamic range.
+CREATE TABLE t1(c INT);
+INSERT INTO t1 VALUES (),();
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (),(),();
+
+# First, enable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
+EXPLAIN SELECT 1 FROM
+ (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# Second, disable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
+EXPLAIN SELECT 1 FROM
+ (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
+
+CREATE TABLE `t1` (
+ `mot` varchar(4) NOT NULL,
+ `topic` int NOT NULL,
+ PRIMARY KEY (`mot`,`topic`)
+ );
+INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
+
+# rechecking_index_usage/no_indices_to_analyze
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+drop table t1;
+
+# Range analysis in test_if_skip_sort_order
+# (records_estimation_for_index_ordering)
+CREATE TABLE t1 (
+ i1 int,
+ i2 int,
+ c char(1),
+ KEY k1 (i1),
+ KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1;
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2010-09-27 09:26:39 +0000
+++ b/sql/item_subselect.cc 2011-01-05 13:48:15 +0000
@@ -46,10 +46,10 @@ inline Item * and_items(Item* cond, Item
}
Item_subselect::Item_subselect():
- Item_result_field(), value_assigned(0), thd(0), substitution(0),
- engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
- const_item_cache(1), engine_changed(0), changed(0),
- is_correlated(FALSE)
+ Item_result_field(), value_assigned(0), executed_before(false),
+ thd(0), substitution(0), engine(0), old_engine(0),
+ used_tables_cache(0), have_to_be_excluded(0), const_item_cache(1),
+ engine_changed(0), changed(0), is_correlated(FALSE)
{
with_subselect= 1;
reset();
@@ -309,6 +309,26 @@ bool Item_subselect::exec()
*/
DBUG_EXECUTE_IF("subselect_exec_fail", return 1;);
+ /*
+ Disable tracing of subquery execution if
+ 1) this is not the first time the subselect is executed, and
+ 2) REPEATED_SUBSELECT is disabled
+ */
+ bool disable_trace= false;
+ bool repeated_trace_enabled= thd->opt_trace ?
+ thd->opt_trace->feature_enabled(Opt_trace_context::REPEATED_SUBSELECT) :
+ false;
+ if (executed_before && !repeated_trace_enabled)
+ disable_trace= true;
+ Opt_trace_disable_I_S disable_trace_wrapper(thd->opt_trace, disable_trace);
+ executed_before= true;
+
+ Opt_trace_context * const trace= thd->opt_trace;
+ Opt_trace_object trace_wrapper(trace);
+ Opt_trace_object trace_exec(trace, "subselect_exec");
+ opt_trace_add_select_number(&trace_exec, get_select_lex()->select_number);
+ Opt_trace_array trace_steps(trace, "steps");
+
res= engine->exec();
if (engine_changed)
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2010-10-19 12:56:36 +0000
+++ b/sql/item_subselect.h 2011-01-05 13:48:15 +0000
@@ -45,6 +45,7 @@ typedef Comp_creator* (*chooser_compare_
class Item_subselect :public Item_result_field
{
my_bool value_assigned; /* value already assigned to subselect */
+ bool executed_before;
public:
/* thread handler, will be assigned in fix_fields only */
THD *thd;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-01-03 11:14:55 +0000
+++ b/sql/opt_range.cc 2011-01-05 13:48:15 +0000
@@ -2423,7 +2423,11 @@ int SQL_SELECT::test_quick_select(THD *t
needed_reg.clear_all();
quick_keys.clear_all();
if (keys_to_use.is_clear_all())
+ {
+ Opt_trace_object (thd->opt_trace, "no_range_analysis").
+ add_alnum("cause", "no_indices_to_analyze");
DBUG_RETURN(0);
+ }
records= head->file->stats.records;
if (!records)
records++; /* purecov: inspected */
@@ -2507,7 +2511,7 @@ int SQL_SELECT::test_quick_select(THD *t
if (!keys_to_use.is_set(idx))
{
trace_idx_details.add("usable", false).
- add_alnum("cause", "not_usable_by_conditions");
+ add_alnum("cause", "not_applicable");
continue;
}
if (key_info->flags & HA_FULLTEXT)
@@ -2574,7 +2578,14 @@ int SQL_SELECT::test_quick_select(THD *t
if (cond)
{
- if ((tree= get_mm_tree(¶m,cond)))
+ {
+ //Guilhem: this is needed because we might evaluate a subquery
+ //inside get_mm_tree, but if you have a better suggestion I'm
+ //all ears. I don't like this tracepoint very much.
+ Opt_trace_array trace_setup_cond(trace, "setup_range_conditions");
+ tree= get_mm_tree(¶m,cond);
+ }
+ if (tree)
{
if (tree->type == SEL_TREE::IMPOSSIBLE)
{
@@ -6508,6 +6519,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
end:
if (impossible_cond_cause)
{
+ Opt_trace_object wrapper (param->thd->opt_trace);
Opt_trace_object (param->thd->opt_trace, "impossible_condition",
Opt_trace_context::RANGE_OPTIMIZER).
add_utf8("table", *field->table_name).
=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc 2010-11-24 18:54:26 +0000
+++ b/sql/opt_trace.cc 2011-01-05 13:48:15 +0000
@@ -381,11 +381,13 @@ const char *Opt_trace_context::flag_name
const char *Opt_trace_context::feature_names[]=
{
- "greedy_search", "range_optimizer", "default", NullS
+ "greedy_search", "range_optimizer", "dynamic_range",
+ "repeated_subselect", "default", NullS
};
const Opt_trace_context::feature_value Opt_trace_context::FEATURES_DEFAULT=
Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH |
+ Opt_trace_context::DYNAMIC_RANGE |
Opt_trace_context::RANGE_OPTIMIZER);
Opt_trace_context::Opt_trace_context(void):
=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h 2011-01-03 20:36:18 +0000
+++ b/sql/opt_trace.h 2011-01-05 13:48:15 +0000
@@ -373,13 +373,18 @@ public:
@li "greedy_search" = the greedy search for a plan
@li "range_optimizer" = the cost analysis of accessing data through
ranges in indices
+ @li "dynamic_range" = the range optimization performed for each record
+ when access method is dynamic range (off by default)
+ @li "repeated_subselect" =
@li "default".
*/
static const char *feature_names[];
/** Features' numeric values for @@@@optimizer_trace_features variable */
enum feature_value {
GREEDY_SEARCH= 1,
- RANGE_OPTIMIZER= 2, ///@todo join cache, semijoin...
+ RANGE_OPTIMIZER= 2,
+ DYNAMIC_RANGE= 4,
+ REPEATED_SUBSELECT= 8, ///@todo join cache, semijoin...
/*
if you add here, update feature_value of empty implementation
and FEATURES_DEFAULT!
@@ -453,6 +458,11 @@ public:
*/
bool set_query(const char* query, size_t length, CHARSET_INFO *charset);
+ bool feature_enabled(feature_value arg)
+ {
+ return features & arg;
+ }
+
private:
/**
@@ -1333,6 +1343,8 @@ public:
enum feature_value {
GREEDY_SEARCH= 1,
RANGE_OPTIMIZER= 2,
+ DYNAMIC_RANGE= 4,
+ REPEATED_SUBSELECT= 8,
MISC= 128
};
};
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-03 20:36:18 +0000
+++ b/sql/sql_select.cc 2011-01-05 13:48:15 +0000
@@ -2673,9 +2673,6 @@ JOIN::optimize()
setup_subq_exit:
DBUG_ASSERT(zero_result_cause != NULL);
- trace_steps.end(); // because all steps are done
- Opt_trace_object trace_empty_result(trace, "empty_result");
- trace_empty_result.add_alnum("cause", zero_result_cause);
/*
Even with zero matching rows, subqueries in the HAVING clause may
need to be evaluated if there are aggregate functions in the
@@ -2684,6 +2681,11 @@ setup_subq_exit:
*/
if (setup_subquery_materialization())
DBUG_RETURN(1);
+
+ trace_steps.end(); // because all steps are done
+ Opt_trace_object trace_empty_result(trace, "empty_result");
+ trace_empty_result.add_alnum("cause", zero_result_cause);
+
error= 0;
DBUG_RETURN(0);
}
@@ -2802,6 +2804,12 @@ JOIN::save_join_tab()
void
JOIN::exec()
{
+ Opt_trace_context * const trace= thd->opt_trace;
+ Opt_trace_object trace_wrapper(trace);
+ Opt_trace_object trace_exec(trace, "join_execution");
+ opt_trace_add_select_number(&trace_exec, select_lex->select_number);
+ Opt_trace_array trace_steps(trace, "steps");
+
List<Item> *columns_list= &fields_list;
int tmp_error;
DBUG_ENTER("JOIN::exec");
@@ -4747,7 +4755,6 @@ make_join_statistics(JOIN *join, TABLE_L
join->const_table_map= 0;
{
- Opt_trace_object trace_wrapper(trace);
for (POSITION *p_pos= join->positions, *p_end= p_pos + const_count;
p_pos < p_end ;
p_pos++)
@@ -4931,6 +4938,7 @@ make_join_statistics(JOIN *join, TABLE_L
}
}
+ Opt_trace_object trace_wrapper(trace);
{
/* Calc how many (possible) matched records in each table */
Opt_trace_array trace_records(trace, "records_estimation");
@@ -7629,17 +7637,22 @@ choose_plan(JOIN *join, table_map join_t
jtab_sort_func, (void*)join->emb_sjm_nest);
join->cur_sj_inner_tables= 0;
- if (straight_join)
{
- optimize_straight_join(join, join_tables);
- }
- else
- {
- if (search_depth == 0)
- /* Automatically determine a reasonable value for 'search_depth' */
- search_depth= determine_search_depth(join);
- if (greedy_search(join, join_tables, search_depth, prune_level))
- DBUG_RETURN(TRUE);
+ Opt_trace_object wrapper(join->thd->opt_trace);
+ Opt_trace_array trace_plan(join->thd->opt_trace, "considered_execution_plans",
+ Opt_trace_context::GREEDY_SEARCH);
+ if (straight_join)
+ {
+ optimize_straight_join(join, join_tables);
+ }
+ else
+ {
+ if (search_depth == 0)
+ /* Automatically determine a reasonable value for 'search_depth' */
+ search_depth= determine_search_depth(join);
+ if (greedy_search(join, join_tables, search_depth, prune_level))
+ DBUG_RETURN(TRUE);
+ }
}
/*
@@ -7841,6 +7854,9 @@ optimize_straight_join(JOIN *join, table
for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
+ Opt_trace_object trace_table(join->thd->opt_trace);
+ trace_table.add_utf8_table(s->table);
+
/* Find the best access method from 's' to the current partial plan */
best_access_path(join, s, join_tables, idx, FALSE, record_count,
join->positions + idx, &loose_scan_pos);
@@ -7851,6 +7867,8 @@ optimize_straight_join(JOIN *join, table
advance_sj_state(join, join_tables, s, idx, &record_count, &read_time,
&loose_scan_pos);
+ trace_table.add("cost_for_plan", read_time).
+ add("records_for_plan", record_count);
join_tables&= ~(s->table->map);
++idx;
}
@@ -8032,7 +8050,6 @@ greedy_search(JOIN *join,
POSITION best_pos;
JOIN_TAB *best_table; // the next plan node to be added to the curr QEP
uint n_tables; // ==join->tables or # tables in the sj-mat nest we're optimizing
- Opt_trace_context * const opt_trace= join->thd->opt_trace;
DBUG_ENTER("greedy_search");
/* number of tables that remain to be optimized */
@@ -8041,10 +8058,6 @@ greedy_search(JOIN *join,
join->emb_sjm_nest->sj_inner_tables :
~(table_map)0));
- Opt_trace_object trace_wrapper(opt_trace);
- Opt_trace_array trace_plans(opt_trace, "considered_execution_plans",
- Opt_trace_context::GREEDY_SEARCH);
-
do {
/* Find the extension of the current QEP with the lowest cost */
join->best_read= DBL_MAX;
@@ -9857,6 +9870,10 @@ static bool make_join_select(JOIN *join,
join->best_positions[i].records_read &&
!(join->select_options & OPTION_FOUND_ROWS)))
{
+ Opt_trace_object trace_table(join->thd->opt_trace,
+ "rechecking_index_usage");
+ trace_table.add_utf8_table(tab->table);
+
/* Join with outer join condition */
Item *orig_cond=sel->cond;
sel->cond= and_conds(sel->cond, *tab->on_expr_ref);
@@ -14583,10 +14600,19 @@ optimize_cond(JOIN *join, Item *conds, L
trace_cond.add("original_condition", conds);
if (build_equalities)
{
- conds= build_equal_items(join->thd, conds, NULL, join_list,
- &join->cond_equal);
+ {
+ //Guilhem: Let me know if you have a suggestion for how to
+ //move these three "evaluate_subselect_cond_steps" tracepoints
+ //to one location
+ Opt_trace_disable_I_S disable_trace_wrapper(trace,
+ !conds->with_subselect);
+ Opt_trace_array trace_subselect(trace, "evaluate_subselect_cond_steps");
+ conds= build_equal_items(join->thd, conds, NULL, join_list,
+ &join->cond_equal);
+ }
trace_cond.add("after_equality_propagation", conds);
}
+
/* change field = field to field = const for each found field = const */
propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);
/*
@@ -14595,7 +14621,12 @@ optimize_cond(JOIN *join, Item *conds, L
*/
trace_cond.add("after_constant_propagation", conds);
DBUG_EXECUTE("where",print_where(conds,"after const change", QT_ORDINARY););
- conds= remove_eq_conds(thd, conds, cond_value) ;
+ {
+ Opt_trace_disable_I_S disable_trace_wrapper(trace,
+ !conds->with_subselect);
+ Opt_trace_array trace_subselect(trace, "evaluate_subselect_cond_steps");
+ conds= remove_eq_conds(thd, conds, cond_value) ;
+ }
trace_cond.add("after_trivial_conditions_removal", conds);
}
DBUG_RETURN(conds);
@@ -18390,6 +18421,21 @@ join_read_prev_same(READ_RECORD *info)
static int
join_init_quick_read_record(JOIN_TAB *tab)
{
+ /* This is for QS_DYNAMIC_RANGE, i.e., "Range checked for each
+ record". The trace for the range analysis below this point will
+ be printed with different ranges for every record to the left of
+ this table in the join.
+ */
+ // Guilhem: should we do like for REPEATED_SUBQUERY feature here,
+ // i.e., show the first optimization but not 2nd...Nth? That will
+ // require a new variable similar to executed_before (see
+ // changes in item_subselect.cc
+ Opt_trace_object wrapper(tab->join->thd->opt_trace);
+ Opt_trace_object trace_table(tab->join->thd->opt_trace,
+ "records_estimation_per_record",
+ Opt_trace_context::DYNAMIC_RANGE);
+ trace_table.add_utf8_table(tab->table);
+
if (test_if_quick_select(tab) == -1)
return -1; /* No possible records */
return join_init_read_record(tab);
@@ -20031,6 +20077,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
key_map new_ref_key_map; // Force the creation of quick select
new_ref_key_map.set_bit(new_ref_key); // only for new_ref_key.
+ Opt_trace_context * const trace= tab->join->thd->opt_trace;
+ Opt_trace_object trace_wrapper(trace);
+ Opt_trace_object trace_recest(trace,
+ "records_estimation_for_index_ordering");
+ trace_recest.add_utf8_table(tab->table).
+ add_utf8("index", table->key_info[new_ref_key].name);
if (select->test_quick_select(tab->join->thd, new_ref_key_map, 0,
(tab->join->select_options &
OPTION_FOUND_ROWS) ?
@@ -20080,6 +20132,13 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
bool quick_created= FALSE;
if (table->quick_keys.is_set(best_key) && best_key != ref_key)
{
+ Opt_trace_context * const trace= join->thd->opt_trace;
+ Opt_trace_object trace_wrapper(trace);
+ Opt_trace_object trace_recest(trace,
+ "records_estimation_for_index_ordering");
+ trace_recest.add_utf8_table(tab->table).
+ add_utf8("index", table->key_info[best_key].name);
+
key_map map; // Force the creation of quick select
map.set_bit(best_key); // only best_key.
quick_created=
=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc 2010-11-23 10:21:53 +0000
+++ b/sql/sys_vars.cc 2011-01-05 13:48:15 +0000
@@ -1433,7 +1433,7 @@ static Sys_var_flagset Sys_optimizer_tra
"optimizer_trace_features",
"Enables/disables tracing of selected features of the Optimizer:"
" optimizer_trace_features=option=val[,option=val...], where option is one of"
- " {greedy_search, range_optimizer}"
+ " {greedy_search, range_optimizer, dynamic_range, repeated_subselect}"
" and val is one of {on, off, default}",
SESSION_VAR(optimizer_trace_features), CMD_LINE(REQUIRED_ARG),
Opt_trace_context::feature_names,
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110105134815-mqdymp8b5d0z3i40.bundle
