#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:jorgen.loland@stripped
3243 Jorgen Loland 2011-01-05
WL4800:
* Updated sys_vars with new options for optimizer_trace_features
* Added tests that demonstrate optimizer tracing with subselects
modified:
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/optimizer_trace_range.result
mysql-test/r/optimizer_trace_subquery.result
mysql-test/t/optimizer_trace_range.test
mysql-test/t/optimizer_trace_subquery.test
sql/sys_vars.cc
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2011-01-05 08:42:16 +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 {misc, 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 misc=on,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_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 2010-12-13 14:03:16 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2011-01-05 08:42:16 +0000
@@ -2197,6 +2197,253 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
}
] /* steps */
} 0 0
+
+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 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 STRAIGHT_JOIN * FROM t1, t2
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select straight_join `*` AS `*` from `test`.`t1` join `test`.`t2` where ((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "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 */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t1.key1= `test`.`t2`.`key1a`",
+ "null_rejecting": false
+ },
+ {
+ "condition": "t2.key1a= `test`.`t1`.`key1`",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "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_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 42,
+ "cost": 51.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": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "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 */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i1",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "records": 42,
+ "cost": 51.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "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`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`key2` > 1020)"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "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
DROP TABLE t1,t2;
CREATE TABLE t1 (
cola char(3) not null,
@@ -2756,11 +3003,11 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN
] /* steps */
} 0 0
DROP TABLE t1;
-SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
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
@@ -2909,10 +3156,76 @@ EXPLAIN SELECT 1 FROM
"select#": 2,
"steps": [
{
- "records_estimation_per_record": "..."
- },
- {
- "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 */
+ },
+ {
+ "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 */
@@ -2970,62 +3283,272 @@ EXPLAIN SELECT 1 FROM
] /* 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
+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
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic) {
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where `topic` = all (/* select#2 */ select `topic` from `test`.`t1` group by `topic`)"
+ "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`b` < `c`) group by 1 limit 1) `d2`"
},
{
"join_preparation": {
- "select#": 1,
+ "select#": 2,
"steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "EXISTS (CORRELATED SELECT)",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- }
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
- "select#": 1,
+ "select#": 2,
"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`)))))",
- "evaluate_subselect_cond_steps": [
- ] /* evaluate_subselect_cond_steps */,
- "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`)))))"
+ "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": [
+ ] /* 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": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where `topic` = all (/* select#2 */ select `topic` from `test`.`t1` group by `topic`)"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* 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`)))))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "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 */
},
{
@@ -3187,3 +3710,780 @@ SELECT * from t1 where topic = all (SELE
} 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": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where (`i1` > '2') order by `i1`,`i2`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* 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": [
+ {
+ "expanded_query": "/* select#1 */ select distinct `i1` AS `i1` from `test`.`t1` where (`i1` >= '1') order by `i1` desc"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* 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/r/optimizer_trace_subquery.result'
--- a/mysql-test/r/optimizer_trace_subquery.result 2010-12-13 14:03:16 +0000
+++ b/mysql-test/r/optimizer_trace_subquery.result 2011-01-05 08:42:16 +0000
@@ -308,21 +308,129 @@ SELECT (SELECT a FROM t1 WHERE t1.a=t2.a
DROP TABLE t1,t2;
SET @@optimizer_trace_features="default";
-CREATE TABLE t1 (a int, b int, c int);
-SELECT 50, 3, 3 FROM DUAL
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) ;
+a
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `t1`.`a` AS `a` from `test`.`t1` where ((`t1`.`a` = (/* select#2 */ select `b` from `test`.`t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `a` from `test`.`t2` limit 1)))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_preparation": {
+ "select#": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))",
+ "evaluate_subselect_cond_steps": [
+ {
+ "subselect_exec": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* 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 */
+ }
+ ] /* evaluate_subselect_cond_steps */,
+ "after_equality_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_constant_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+ "evaluate_subselect_cond_steps": [
+ ] /* evaluate_subselect_cond_steps */,
+ "after_trivial_conditions_removal": null
+ } /* condition_processing */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+SELECT 1 FROM DUAL
WHERE NOT EXISTS
-(SELECT * FROM t1 WHERE a = 50 AND b = 3);
-50 3 3
-50 3 3
+(SELECT * FROM t2 WHERE a = 50 AND b = 3);
+1
+1
SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT 50, 3, 3 FROM DUAL
+SELECT 1 FROM DUAL
WHERE NOT EXISTS
-(SELECT * FROM t1 WHERE a = 50 AND b = 3) {
+(SELECT * FROM t2 WHERE a = 50 AND b = 3) {
"steps": [
{
- "expanded_query": "/* select#1 */ select 50 AS `50`,3 AS `3`,3 AS `3` from DUAL where (not(exists(/* select#2 */ select `*` from `test`.`t1` where ((`a` = 50) and (`b` = 3)))))"
+ "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select `*` from `test`.`t2` where ((`a` = 50) and (`b` = 3)))))"
},
{
"join_preparation": {
@@ -345,13 +453,13 @@ WHERE NOT EXISTS
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+ "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
"evaluate_subselect_cond_steps": [
] /* evaluate_subselect_cond_steps */,
- "after_equality_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+ "after_equality_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
"evaluate_subselect_cond_steps": [
] /* evaluate_subselect_cond_steps */,
- "after_constant_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+ "after_constant_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
"evaluate_subselect_cond_steps": [
{
"subselect_exec": {
@@ -364,10 +472,10 @@ WHERE NOT EXISTS
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3))",
- "after_equality_propagation": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))",
- "after_constant_propagation": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))",
- "after_trivial_conditions_removal": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))"
+ "original_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_equality_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_constant_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "after_trivial_conditions_removal": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
} /* condition_processing */
},
{
@@ -378,7 +486,7 @@ WHERE NOT EXISTS
"records_estimation": [
{
"database": "test",
- "table": "t1",
+ "table": "t2",
"records": 1,
"cost": 1,
"table_type": "system"
@@ -418,4 +526,4 @@ WHERE NOT EXISTS
] /* steps */
} 0 0
-DROP TABLE t1;
+DROP TABLE t1,t2;
=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test 2010-12-13 14:03:16 +0000
+++ b/mysql-test/t/optimizer_trace_range.test 2011-01-05 08:42:16 +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 (
@@ -184,11 +191,21 @@ DROP TABLE t1;
# Test that range optimization is not shown for every outer record
# when there is a dynamic range.
-SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
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
@@ -210,4 +227,27 @@ SELECT * from t1 where topic = all (SELE
SELECT * FROM information_schema.OPTIMIZER_TRACE;
--echo
-drop table t1;
\ No newline at end of file
+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 'mysql-test/t/optimizer_trace_subquery.test'
--- a/mysql-test/t/optimizer_trace_subquery.test 2010-12-13 14:03:16 +0000
+++ b/mysql-test/t/optimizer_trace_subquery.test 2011-01-05 08:42:16 +0000
@@ -26,14 +26,24 @@ DROP TABLE t1,t2;
SET @@optimizer_trace_features="default";
-# evaluate_subquery_cond_step (subquery eval during optimize_cond)
-CREATE TABLE t1 (a int, b int, c int);
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
-SELECT 50, 3, 3 FROM DUAL
+# evaluate_subselect_cond_steps for build_equal_item()
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+ t1.a= (SELECT a FROM t2 LIMIT 1) ;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# evaluate_subselect_cond_steps for remove_eq_conds
+SELECT 1 FROM DUAL
WHERE NOT EXISTS
- (SELECT * FROM t1 WHERE a = 50 AND b = 3);
+ (SELECT * FROM t2 WHERE a = 50 AND b = 3);
--echo
SELECT * FROM information_schema.OPTIMIZER_TRACE;
--echo
-DROP TABLE t1;
\ No newline at end of file
+DROP TABLE t1,t2;
=== 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 08:42:16 +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-20110105084216-wlyy01p9uyrd0z8n.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3243) | Jorgen Loland | 5 Jan |
