From: Jorgen Loland Date: January 5 2011 8:42am Subject: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3243) List-Archive: http://lists.mysql.com/commits/127952 Message-Id: <20110105084219.31FBA374@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8717513574565436086==" --===============8717513574565436086== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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 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": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", - "evaluate_subselect_cond_steps": [ - ] /* evaluate_subselect_cond_steps */, - "after_equality_propagation": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", - "evaluate_subselect_cond_steps": [ - ] /* evaluate_subselect_cond_steps */, - "after_constant_propagation": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", - "evaluate_subselect_cond_steps": [ - ] /* evaluate_subselect_cond_steps */, - "after_trivial_conditions_removal": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`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": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", + "evaluate_subselect_cond_steps": [ + ] /* evaluate_subselect_cond_steps */, + "after_equality_propagation": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", + "evaluate_subselect_cond_steps": [ + ] /* evaluate_subselect_cond_steps */, + "after_constant_propagation": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`test`.`t1`.`topic`)))))", + "evaluate_subselect_cond_steps": [ + ] /* evaluate_subselect_cond_steps */, + "after_trivial_conditions_removal": "((`test`.`t1`.`topic`,(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having ((`test`.`t1`.`topic`) <> (`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, --===============8717513574565436086== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jorgen.loland@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jorgen.loland@stripped\ # wlyy01p9uyrd0z8n # target_branch: file:///export/home/jl208045/mysql/wl4800/mysql-next-\ # mr-opt-backporting-wl4800-range-subselect/ # testament_sha1: 43b3a926ba1cca0ad7e890dcf47154639e17ace3 # timestamp: 2011-01-05 09:42:19 +0100 # base_revision_id: jorgen.loland@stripped\ # v20mgajcaruau5p3 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSsWV6kAJvP/gH11VVVY9/// f///+v////pgIdh3qn0C52uJxHSqNd60FzTSsHu27ubrmMzPo9xna+4V9HtNCKhnvu9NNtLIUgiU rQaMzVVElAOmqUqq6MWNqSULbEUXQSkKnim0TZT1P0JqaaBoAADIaAAAAAAAAkqniaaKn+RKeKGa geKZqbSNoIYRkADCDBNGhoaGBJAkyQRMo2p6noyTIPUBoGnqe1Q0AaAYj0nqHqGgaaCTSRESaJNl PU0D01Bp6jGoaD1DI00eoAaGgAAABEoIRlMJkCnpppkap4ENJqG9RMnqafqgPSb0k09I9QYNE8oF SRBGhGg000BU9M1NGQ1MUabU0AGgGT0nqepoAaaNN1IF5QMAgDh63jRshHXcJ7jvEM68Ie4KjjG4 2R3f1ehYxzem4QLQraeaKb/YfWHiCAon9w0Bzhy9vaTNcNxgv02bt7XLqb+GbY4PLu/u4vu9jHPk cBqd36Xneu5rs2+3apuKxv7modgbZw8wrgjYIM0zLxiJ72SB13oIIQ7TlWRijzrUNEQYqMkkiGCN mKlgEut2SNU/fcKKYEhxCIRVVVkQZDfDEWSsBg++wwDpoFEoynCUwEMiMViIxJCZLkqIUdnV4A6E oLZOnde4e5DfPWBO4wEYQSIIyJEARgkYMiDEFIybGBRKNhMI0XKSo2VSljhrWL5DiAdBIKtCwrBY MgixGIKIwYMmAdARuhWRIIKdD3A5OIYwspawwKbOQ4owhEiVGSLASSDBkYybhyyGoYDxnLyW4NzU hsDlJKCUBglbCKTniTW6lk0bURdZto5pFl3m9T098Zt7UpLT5nML9hxPBZJDk8qn2rvNV+85VHif buv6fgv3PNZreHZZu31vwvoLmRpaB2Zzid9ak3BhUiRcdn0PXRLaeuaF5ABayRtscLCNTINsWOLz M2va16eatuVa1TbreXo0hXHPXp3mxA8iRRFQ0+FBUPtoc9iyqqSa0RIjq+MRF0pJD8qPBG56J9GC wd5jqsOHdCA5GZCFKV/PvF/Ux8yhuH+UCeMzqlQcpGFuUMWEMH2GV/pxCk0rNGSgFrS2w7h2kXyN stMJwHdpEFGTiT+paTNOQVYGRBRYJbJUGIekQ/9SAsYRICEECCxAJBixEYr7B0HzDJJEkYRJA6sZ +8Dkm1bW63EHaTrahA6C4JHntwCGMZ5lQ52H0RTSWc67lhhJWyeifQa8Ds3AkUklUm8wPXmzx2EX GRkPpi1mqNDZpDBYu+LXiMTDYq3pHhS5HJMTRAt4E7YYQCHyNFLsXGLCjRcnJu1LMLP0/Y/KN3pf U+qhoawuOQsY0atGr5+1wPSbRu+Tz+59x3H6BzPWDm0uHAIbPaaHIxHN7/KJBaN/N9CqKfm5W+TC rezkzp9TUmC6MKbhUu7ng8hvENBD2UEu9Dg1V+b7YbHf1meD+SAb+Dt391KYnz10yKWSidZ0IIVH 8PhD7fiz0Se1XhMu7bDkn0zlOPgcqpQpRhOrCjNv/+2ex51O7bJ1/l48DNB2BuKCGNgOz8e1P6R/ Fv8SHiO3rlp58i2Xew1pzh6lJOSkG2kjG7GTlw/GoYKr01plv+J7A/8KIUN7B8HlobGFwfDPMXTP aDxqb+cEb8vUh29Ncp0GKiv0Zzz2NXV+eteH62oerzdJ5IWedJ3AVDelqSfKY3Wtgu0AsymZBJDO QwTU1ethy7HgVkbYcnK3J1RW2cjZ6/7/GoHTw4oFCGpOgbUCG5ZTpI0hIddhcxYsOwQ+QGaJhnoa NIa2IWlCkfimxYMx1zMhY2xYbM4jyP0FsRSlClkSMotQOEOLjA5A81tJmGGE7/aHmRQfVoW0Gtk+ TPqk38rQwLkagEUgwQYClYLIInJ+NA5qgRBKSk0i1IJjZAwwgJY1UZVhJUgqBooEhfWGTAiwRAQZ DmHTAsLMzOqXJEA0WRYbCghUpXAhzqQ2cw0DJx9ITCJzqGEGUMDKZLPJl6QTSWbuMw8RZ3wPRDyn 6oMiSB+lPg2ic7z0G6fBLn1akMgMLUB+7A4YcMLIhEJS5LIhb7EohOEhUpSpUrKS5mKDMUjBKFee /QYwBggIYdh4hgZDBMxWmbHANHOusiScE45oVHvaLI7wszqBkGWEOpgGRoG0DjsOggoeaJKxpoIY WYWAslCRKmBQlCyQ7pgYtrHkrRZFo5rgINsVxTXrCsCQJgYIGgcDQbTILBaknV8nkpKk8qUOsODl kiEorXMVqcTqBWCEogCEwzaUd9kqq7NglDBMUytdNoJm5bCljZEzYa6jkOQJgF7l7WAReyGQSDao HCDLBUiJUPFhINBs2JBkOlksK8yeAkh8eeOKqiqqidCHtCQRPTPXLGAx+nfhz3RoHsntnQQ2du0V JWCI9Aj1jLhZKSyVDtBJkTyB5e6d4+b5OuoHm5+gpZKMv4j2DUplpdZhlpbSpakNh2bzRVS5ENFD RSbZMbYuZbnYVuIr+KSILSRBpHO/IRhsHdAkWVUa2CECQJroo2zhRbjdKDIcMCjIhfasMgJAzRgG RRPVgmZ9bK7EwKarVFOe2ZgYyj6y5sKYJS4ODizQ6hCa+CnxgLSmSw2Gi9PEEQjqVqULC64muccw Mk5ZXETKJEwSUChopYKGBS8wzCTqF0B1epvkK0NwTQxCjzBro1Ti3rZwj6J4T4PrYYJ356/ZtWSO yaB1qGMDmk2jWGmjSamLp7pzeEa8eLR/L+u61Xvh9ToeR8T/0qSO7D/FHCzRfov5eDkL9EMb+yt4 Dpae1fXaAOJLqG64BkUnD4hcKedjdTSMQCSD3rxohnEKn0KdyChVD0/4m/ngDZO8I/EI8bdB1Vr7 1ETzoIPMPpRootyRKTVlklk1/amyjhVr+VW1IONSzhMKFpPmXw3O9dpTljq0vZrL9114MiKCg5wq QxcpO++PQzk2YBzu5t9eKsGmRr08GgBcKMsGsnCClgco4tYXLl744jNLrbkAG59fBxoZYfsCyzGI ZFJozeAyH+ATG7t8KA+YUsYMYOKkaEiAXzHhdNQRGKF4kIwh8gOP0RXsvJQE6ZBYUTF8ZTIqbgxj mpC8Hnbu327WSjgFROt/PjkqGBnZ3XDIzex3c+eQalOH6Xqa9Wvr18muJ5Z9bfk6Hzel9vBcK7iQ 0peWwtSntD5kpwKxqnFht14Kn1OZN3T8+6TIMN+S+aq7F+Dex6t2pdfdi01PSmTY7p680qO4Pcqs IvF7SQtMaHezcXbydbB2pv7m3o7dT3vAPHLy6fR0YscWO+/bzZ8Tlbs1RrekGuei+Hw54IelOsDu iyy9jO8kpBHSKLfUBqbQDSLCJId55TBeNyIeCIebGp9y04lQikwQK7rFDsO8vaSLbAh1sriXN+un kObEKms6jLot3WohvM+Mk6FqLWtFXqKGB6PBs9TZrcPhg879HDzOnq68mp06ibSpKone7XTeRtrq 6Gi+zk7JFSUuteM1+C2Ouk5mN9zFv377bhRGiThraTEPa+i9Ca7ZKHUndMtKggXB3byiSySuEJcE sw47cuPcGJihkuUHth8Z6Fd+s2ZIcQDaweiAyqxkDUDIOlBSmsYGQTh3TJE7+xi3GnZLqVmaEnIk 8hHqZEKqupRTKxi/lJbSNUcES4acoxTtUGEgJyzOMmsNtNmRm4Uxppk+XrX156Pj1cNmezP0w39r uHh04LbstJper9s6HNm6GbawdPe2Nc8VuqTc65HXv0tXbbs24ducaSRkxYuZ7PfljjbXmrkzwcer BybZnNVMvkW0arKU46aNTuw6oVJZvcWXqfK1uxtJB7AgIKjoye8pS6T62jwtwsknElLLbMUBUkaP 1C2Rbhk254K5s3F04YbHVmxWtgyZ7bDW61MO5TTqdNsQZ6jU21mdNn6UmW2SUXM5hZDWMFiSThJH 5awtraFLV2dnM6l7228DHns5m7jns7LL8HxtbVj+xroO14Pgzk49XOt0Vhhbg6L2YtazDdj6Ors1 Tp1b2imHnr6WKnazbmPZvrk2ZMc954NDx9wcdXgqK1iogHDZZejdKGfCtLxDbFxwqBMZSUoMsm7l vbpxnzGkLzvc70Fhiwmqas/BuUJD0fMtPrEhzjFSTkn1IHNkWhJJH0hrHptGEIRY/MEfTTB2cPcO gmgQE9xrQfyC+4j6yfqOD86f+NcLFI/Qngl0v1OSqKGH3j+0SyFBw/Y2QLjUUPsY6hhRDYOhqHAI Fh7R+8dwYm8YDQYMCLZSDsqgm42IJuH/kf4qMOUbHAdFIWdBsGw2g/zD2jyhZUswU/kx4D9yj/N6 XmDim6RuO2YkvLpsaOtN8/YtDRklpHlwn5NWuq3BtKOf/kOtHhCM4cUmaik/9TUlmHVCNqdhROKW Ew1pZDKGSWTCIFxfeVOc1A/c8yCfuGB71IOA5D1pV3TacidplO3CRxTOydKc5FktInqTiWdiCdYY j4nS4HQwZFN5s0KlhI8il3nY0aNBg0eUgYh4h6h6kMWGQ8o1jyiNkI0NpQG4R0HASD0DwU1vIaDo pVO14B949ZiIbXR1MNxvKAwJJ0D4+UXePBDqoIB2E4roDJCQF0cx0OjwJcyPBQQwNzpOpZELoaCA d0EJZI/ZkFkFlXQIMBCilDmZIr4u0K8mi64u4LALqJHgEaYYwxgvkH1A0FtySwRhGB94SQwJ55Ne 8GBNFwYYpFJEgQaD/qJ9OtcACF8KL+VUwawwi7GPywujCRgxVKm2KaIpFsRouSmghmIWACohhe1k dAufmNSmpW7cxzUupUEMwMAwSjkJRKNQQ1yqqqjySk2HMBGB+Mow4J2EDtiOihAhJBMwoZjmE0wE PsQ1iZjGSRgwSRkUMkNamS6C/DTYDcMRCzbLRVFKyU50XRrkjAyuYrGi6MBMPdk84ICB51A88jJ+ 8yha00YGa1EJAjCVZxWieRKgWWRhBYgxZJ1kCGT64eygVFlSCyqkbSKSh+ukJiuIQqQUhCoMKIDC IwILFjEZInhuCzVxbhR/z96mwp9J1gfof3LJ2Md7p4O1i63PZt3pf9JKpKSmt9tOxBp22jaVQqQX kFCNq/8KSfYcUOa2AjV7NtEHUYGc2GWBQ1AmQuLkhrVh3L/uv1ptB+sApBSLO9RDEELgwuohu8E2 qEkIMVgJGINlOwikQ2IRCIaURvBiDsGqvnYCI1xQgapPLWTl3tzQjhJUFlENcmYcEkrKugVmnFXi DbWWDUBh0jVCiSlIYi0GoVSEkjKCPAEOA8EiRIm5NQ5rx+t3oji0j2v3f4NqeipumlQrW9b+973m 9asXg9n6GJi73sgph9+pqSTRm5nMoUKio9rE9KRIpFCO1P4+SyG5qyip3vueHvhMCsXjvqY2bnHe 7N7tcFtJOyThYmvfluCPvk7ERm9G975tQd20t0Kg43B1IPhIw9j1xL9JaZlADcp6aCVE4BvUDBjY MbtBHaI9ly2KDQCOI0GiA0KEgd1KrZESyAlACiJpFhFRiQWMXIjyKiNDBJulXVFKo9yol1796YMA 8N+t17t3bZq7u15ee/S7m3jUcw1OOGDh5OZ5O9uiZwHEXmitVINQYdp2nEV9GwsfgNfIT38NpNqc XdOykhvhUj9GGMIOjpWBvkweLpGBEe5ufROWG0I3ee6TYfJGjcOY1fg9iqEkJAoJdOwiXjEdCh5r 2BxIBaiC2pLQVCwKLJDNkTBFSiVGKhPK0huE6iQGRWEGEWDAYrGlWZVhCUpLPU7sOlxd09bJ4+b3 65400mT2vYs9m3xbHZ8yetNI9HjRyq1yJ2FJHz8YulwUUTwRcupSS8pJSFkzJZFzndTmeW94eEni 50+lTqfMjKfivERnDdIVCoD51TVEff09O/Q1yKdrhTe9TExRUcgmI5Hb0r4r4L6+2Y1V26qINQ66 Gwpr5J8X7KHnbpIjfNCiWNmyTvfK4nllIKhKTDGTUDslkksSHuwihrnqJVYVSSyHmVC8F02xLke2 aWjRFJQViQyUgc8SHZyHbDoHxAXuy0isGhSjLWBWTew1JgMNAjRqWQrJdwYGw7PQwEDvimYAeYVi CldqjTFDMB3LvSqHO96GgiHWRVdSp84YjoY5o9YKt3GPcptkoeO48l8PBz/Jw6jpVEGepm82uOHh A7iIbF/Bmvj5z2D7B2jibRPWHTYM1WPdeRYj2kXuE+J3FoDuk1yTJJ3MIFIgSLPG0VRohBAoIwAe 4qrypLrtzs7H63Wb+eSV0ReKWhaFiOjx3nk98Y/hzKJUnlFhSYxQt3Hmqp8aMEA5XM53fEW3xLby xUUpQVHotSn04/ShVvw4M80sZW6ajRNQO/pkVHjDeTzOkGHfuOvticJGkL9zbjrk8WhNx8uaFUzh diEVTgbw3UjDZklQF6AcFPIh0YnTqFE3KoaCcLqdRueMgSPrhXQsJUYNsEWDaw95CmZoht9fjt2C JmqkqRVBGuo/PeJrVI7e+G5aR8DshzcTAhK9K+kx0BCDZA1jAbwQ9p2d7h418h7Dfz0qh4CpKFIp HtOI+9eSkty0a214+ejXAtPRdeDD1vxu1fr1Gsftx7ZKKkqa9F5MS0S8SoqOk+J2LQ0cvhPfe7vn cShnOs8sYYdMYFmUhKfOifKiVGDV4U+mTCLpYSNkJZLFJSD3KZSRHOWRhkTphsHKYKT21HGE1zGR C8KIbY5rpcnxKe6e+RYTrCiG9r6ZCpSJRObwDzWrfIlSO6OTWnqxfRJeGsyg5BRYSSKrIBHOnAg0 pS98BSeF+BWifnqw2HxmbPrbLEbihRUUkWwouqmCYFoFSKKKYSFUFCWiKUUXoug2pUeBRapcQiBQ GKxCAkUuSpFi8WXuS5JNiekOYhfCRvsRndCiDQGDRBEoqJBJqx8ZndUcAiBSCSlspG3hn8C4Y83s btNcFEqqEdqkbDfJhJZC0gqhshubGE+E/COmD1+I66aIVE2Kc5ND9pIFDIPjBX4eRBwkPjiTLnQa qSCz66WInysUExceuRjBMEOjnhpByyk1KFUsPjtePPjVSSRoX5xbkRPIg6kM0tgdajQUqAL7uEJo Tu0SdPyNk7YRaRrkg74iwO/znntq5lB51yEaAp5sonib8g+VfaAd+CgeHbrSfUnrI9uCUNjuJH3W k9JEyYGrhaK605weqZJ8oQ0TrSKRTcDutqRTbyBIYTchMT5zlwZeDLwn2MZHyujOJuhotqonebRI druekWeMeCDIjIyCQOCkKQ3QlkFNWZ1Kh1Oq0T11JeXlhLSLSLJEs+q8tCoXRYSwmcaSJrjkFEDZ EyyrMsEslJUVHvstJgJvXRKlggRIlVcAK6ld4JQ5xsfKwh0+i65vIIzamNQaohrJYa9RBZUEOnRS 9TyqGXturtUHM3KXUd4chqEOzZSXRKqDaRKFrwsLWVf2nA3Y7kN9oDCh1gYD4iw6RM1hMiFCWTcj CM8QUi1gGFbsN+psJ0AJwQnEygWkKwshyHQpJyTHWBtkMggVtsOkYkYyVDL1HiLBrUcyJrF1jlQa CQKIwebTukHkpl9DTmnXCcKEVFEX+NOGpAIixCiIFl5RComzoUMlGKHKk4lFl3GEeGY9+GMipGCe qtidEjshUekUmvBwhxEtJ7W8csWG1NG3UjhINykUsURoqxEeFESiQEGq2dwrkkhkIwIEjdR1KbkL mOAXuHQVEY57LXZQoNV8eunD5IVxsmyFEtXN4cL1FVVDKURFWhVJIwOcwwctzD0x2m6HowdghAPL /CUfC1EPnBTEoLUWPyNMi6SPxO19nDcdcdZKJYKVFlI6Erh33kxmE6kyUllzktIRqSToOjecFrQK SUFkeKJkMoPONLSTSjCd20FiQz+QjvIc6Ha3RJz8+MdkhFoltrapS9A/FOTWAHDYo9h2KpRTeW4J kjCAZlKHCGDFVVVVUYCRwsBYiIyy1rVJLRaRaRQXoG2LkNHscEb0M0OR0OYO3BA3uC86VHGwI69y OERgRTVcDQR2ROgUg41XcHMGabUkYhv1mkk48M1+NHAHauID30BH+hupz+vccEe/XD5Y22DrBxSK IteXTqkHUuiKR2i+2OuI3fYGMPn7tqdxNC3R0SyJYhudj5pdM4fBksTFt0iYSOizY3SGEkcREPmh k2dEqAL9QQOoDgxcAjFiXFT3hYhmv1FzJRDdI75PgqGC8ieIYm1ybRO5FpYidreGPYkTrdBDJ0NP Jyn7ETOI/UIpI+eCkfmShGRT4BIJAD9KLBPghD/aB8P4/b6hs60XYgmJEURQREoojGMZIgEJ2MSg obyHAWvQKSFU/Xrslm+J7hxyoMi0b2NFxaVssqJHHYZgmxRyHAwTJXEP/xdyRThQkCsWV6k= --===============8717513574565436086==--