From: Jorgen Loland Date: May 27 2011 12:37pm Subject: bzr commit into mysql-trunk branch (jorgen.loland:3317) WL#4800 List-Archive: http://lists.mysql.com/commits/138327 Message-Id: <20110527123741.E7FE1EEE@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3516312748176819247==" --===============3516312748176819247== 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/ based on revid:tor.didriksen@stripped 3317 Jorgen Loland 2011-05-27 WL#4800: * fix todo item "c3) The optimizer may have second thoughts about which access method to use for a table" by tracing the decision. * cost for ref access now includes "compare" cost Reviewer note: Also take a look at Bug 12580768 @ WL4800_TODO.txt Todo-item C3 removed: fixed by this patch. modified: WL4800_TODO.txt mysql-test/r/group_min_max.result mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result sql/sql_select.cc === modified file 'WL4800_TODO.txt' --- a/WL4800_TODO.txt 2011-05-20 13:15:22 +0000 +++ b/WL4800_TODO.txt 2011-05-27 12:37:37 +0000 @@ -28,15 +28,6 @@ not forget to bump the version when doin changes? any little change? maybe). Guilhem suggests: don't have a version. -C3) Jorgen wrote: The optimizer may have second thoughts about which access method to -use for a table. This should be traced. See example query (1) where -the trace says that ref-access is best but we change our mind and use -range access anyway. -(1)CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)); - INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); - SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; -Jorgen said he'll fix in the wl4800 branch. - C4) Jorgen wrote: Make --opt-trace-protocol dump traces to a separate file so that mtr can run with it without failing all tests. Guilhem asks: good idea, but how much is it needed? === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2011-05-24 12:46:22 +0000 +++ b/mysql-test/r/group_min_max.result 2011-05-27 12:37:37 +0000 @@ -2444,7 +2444,7 @@ a b 3 13 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 3 100.00 Using where; Using index for group-by; Using temporary +1 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15 100.00 Using index; Using temporary Warnings: Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a` drop table t1; === modified file 'mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc' --- a/mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc 2011-05-20 13:15:22 +0000 +++ b/mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc 2011-05-27 12:37:37 +0000 @@ -286,3 +286,16 @@ SELECT * FROM information_schema.OPTIMIZ --echo DROP TABLE t1; + +# Optimizer first decides to use ref, then changes mind to use range instead +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); + +--echo +--echo # Test trace for "access_type_changed 'ref' to 'range'" +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +--echo +SELECT * FROM information_schema.OPTIMIZER_TRACE; + +--echo +drop table t1; === modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result' --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result 2011-05-26 16:24:06 +0000 +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result 2011-05-27 12:37:37 +0000 @@ -1373,7 +1373,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -1405,7 +1405,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -1702,7 +1702,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -4496,7 +4496,7 @@ trace "access_type": "ref", "index": "PRIMARY", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -4624,7 +4624,7 @@ trace "access_type": "ref", "index": "PRIMARY", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -6385,7 +6385,7 @@ select * from t6 where d in (select f1() "access_type": "ref", "index": "d", "records": 1, - "cost": 3, + "cost": 3.2, "chosen": true }, { @@ -6421,7 +6421,7 @@ select * from t6 where d in (select f1() "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -7024,7 +7024,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 3, + "cost": 3.2, "chosen": true }, { @@ -7060,7 +7060,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -8482,7 +8482,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { === modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result' --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result 2011-05-26 16:24:06 +0000 +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result 2011-05-27 12:37:37 +0000 @@ -1353,7 +1353,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -1385,7 +1385,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -1682,7 +1682,7 @@ explain SELECT c FROM t5 where c+1 in (s "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -4476,7 +4476,7 @@ trace "access_type": "ref", "index": "PRIMARY", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -4604,7 +4604,7 @@ trace "access_type": "ref", "index": "PRIMARY", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -6357,7 +6357,7 @@ select * from t6 where d in (select f1() "access_type": "ref", "index": "d", "records": 1, - "cost": 3, + "cost": 3.2, "chosen": true }, { @@ -6393,7 +6393,7 @@ select * from t6 where d in (select f1() "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -6986,7 +6986,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 3, + "cost": 3.2, "chosen": true }, { @@ -7022,7 +7022,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -8458,7 +8458,7 @@ select d into res from t6 where d in (se "access_type": "ref", "index": "d", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { === modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result' --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result 2011-05-26 16:24:06 +0000 +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result 2011-05-27 12:37:37 +0000 @@ -1444,14 +1444,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 "access_type": "ref", "index": "i2_1", "records": 10, - "cost": 10, + "cost": 12, "chosen": true }, { "access_type": "ref", "index": "i2_2", "records": 10, - "cost": 10, + "cost": 12, "chosen": false }, { @@ -2086,7 +2086,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5 "access_type": "ref", "index": "PRIMARY", "records": 10, - "cost": 10.24, + "cost": 12.24, "chosen": true }, { @@ -2954,7 +2954,7 @@ WHERE t1.key1=t2.key1a AND t1.key2 > 102 "access_type": "ref", "index": "PRIMARY", "records": 10, - "cost": 430.08, + "cost": 432.08, "chosen": true }, { @@ -3216,14 +3216,14 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f "access_type": "ref", "index": "cola", "records": 533, - "cost": 533, + "cost": 639.6, "chosen": true }, { "access_type": "ref", "index": "colb", "records": 533, - "cost": 533, + "cost": 639.6, "chosen": false }, { @@ -3406,7 +3406,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f "access_type": "ref", "index": "cola", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -5280,3 +5280,236 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN } 0 0 DROP TABLE t1; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); + +# Test trace for "access_type_changed 'ref' to 'range'" +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by + +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a { + "steps": [ + { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select max(`test`.`t1`.`b`) AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` < 2) and (`test`.`t1`.`a` = 1)) group by `test`.`t1`.`a`" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "((`test`.`t1`.`b` < 2) and (`test`.`t1`.`a` = 1))", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + } + ] /* steps */ + } /* condition_processing */ + }, + { + "ref_optimizer_key_uses": [ + { + "database": "test", + "table": "t1", + "field": "a", + "equals": "1", + "null_rejecting": false + } + ] /* ref_optimizer_key_uses */ + }, + { + "records_estimation": [ + { + "database": "test", + "table": "t1", + "range_analysis": { + "table_scan": { + "records": 4, + "cost": 3.9 + } /* table_scan */, + "potential_range_indices": [ + { + "index": "PRIMARY", + "usable": true, + "key_parts": [ + "a", + "b" + ] /* key_parts */ + }, + { + "index": "b", + "usable": true, + "key_parts": [ + "b" + ] /* key_parts */ + } + ] /* potential_range_indices */, + "best_covering_index_scan": { + "index": "b", + "cost": 1.8044, + "chosen": true + } /* best_covering_index_scan */, + "setup_range_conditions": [ + ] /* setup_range_conditions */, + "group_index_range": { + "potential_group_range_indices": [ + { + "index": "PRIMARY", + "covering": true, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "records": 1, + "cost": 1.2 + }, + { + "index": "b", + "usable": false, + "cause": "not_covering" + } + ] /* potential_group_range_indices */ + } /* group_index_range */, + "best_group_range_summary": { + "type": "index_group", + "index": "PRIMARY", + "group_attribute": "b", + "min_aggregate": false, + "max_aggregate": true, + "distinct_aggregate": false, + "records": 1, + "cost": 1.2, + "key_parts_used_for_access": [ + "a" + ] /* key_parts_used_for_access */, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "chosen": true + } /* best_group_range_summary */, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "PRIMARY", + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "index_only": true, + "records": 1, + "cost": 1.21, + "rowid_ordered": true, + "chosen": false, + "cause": "cost" + }, + { + "index": "b", + "ranges": [ + "b < 2" + ] /* 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 */, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "index_group", + "index": "PRIMARY", + "group_attribute": "b", + "min_aggregate": false, + "max_aggregate": true, + "distinct_aggregate": false, + "records": 1, + "cost": 1.2, + "key_parts_used_for_access": [ + "a" + ] /* key_parts_used_for_access */, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */ + } /* range_access_plan */, + "records_for_plan": 1, + "cost_for_plan": 1.2, + "chosen": true + } /* chosen_range_access_summary */ + } /* range_analysis */ + } + ] /* records_estimation */ + }, + { + "considered_execution_plans": "..." + }, + { + "attaching_conditions_to_tables": { + "original_condition": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2))", + "attached_conditions_computation": [ + { + "access_type_changed": { + "database": "test", + "table": "t1", + "index": "PRIMARY", + "old_type": "ref", + "new_type": "range", + "cause": "uses_more_keyparts" + } /* access_type_changed */ + } + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t1", + "attached": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2))" + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "refine_plan": [ + { + "database": "test", + "table": "t1", + "scan_type": "table" + } + ] /* refine_plan */ + } + ] /* steps */ + } /* join_optimization */ + }, + { + "join_execution": { + "select#": 1, + "steps": [ + ] /* steps */ + } /* join_execution */ + } + ] /* steps */ +} 0 0 + +drop table t1; === modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result' --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result 2011-05-24 12:46:22 +0000 +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result 2011-05-27 12:37:37 +0000 @@ -1444,14 +1444,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 "access_type": "ref", "index": "i2_1", "records": 10, - "cost": 10, + "cost": 12, "chosen": true }, { "access_type": "ref", "index": "i2_2", "records": 10, - "cost": 10, + "cost": 12, "chosen": false }, { @@ -2086,7 +2086,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5 "access_type": "ref", "index": "PRIMARY", "records": 10, - "cost": 10.24, + "cost": 12.24, "chosen": true }, { @@ -2954,7 +2954,7 @@ WHERE t1.key1=t2.key1a AND t1.key2 > 102 "access_type": "ref", "index": "PRIMARY", "records": 10, - "cost": 430.08, + "cost": 432.08, "chosen": true }, { @@ -3216,14 +3216,14 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f "access_type": "ref", "index": "cola", "records": 533, - "cost": 533, + "cost": 639.6, "chosen": true }, { "access_type": "ref", "index": "colb", "records": 533, - "cost": 533, + "cost": 639.6, "chosen": false }, { @@ -3406,7 +3406,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f "access_type": "ref", "index": "cola", "records": 1, - "cost": 1, + "cost": 1.2, "chosen": true }, { @@ -5270,3 +5270,236 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN } 0 0 DROP TABLE t1; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); + +# Test trace for "access_type_changed 'ref' to 'range'" +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by + +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a { + "steps": [ + { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select max(`test`.`t1`.`b`) AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` < 2) and (`test`.`t1`.`a` = 1)) group by `test`.`t1`.`a`" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "((`test`.`t1`.`b` < 2) and (`test`.`t1`.`a` = 1))", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "((`test`.`t1`.`b` < 2) and multiple equal(1, `test`.`t1`.`a`))" + } + ] /* steps */ + } /* condition_processing */ + }, + { + "ref_optimizer_key_uses": [ + { + "database": "test", + "table": "t1", + "field": "a", + "equals": "1", + "null_rejecting": false + } + ] /* ref_optimizer_key_uses */ + }, + { + "records_estimation": [ + { + "database": "test", + "table": "t1", + "range_analysis": { + "table_scan": { + "records": 4, + "cost": 3.9 + } /* table_scan */, + "potential_range_indices": [ + { + "index": "PRIMARY", + "usable": true, + "key_parts": [ + "a", + "b" + ] /* key_parts */ + }, + { + "index": "b", + "usable": true, + "key_parts": [ + "b" + ] /* key_parts */ + } + ] /* potential_range_indices */, + "best_covering_index_scan": { + "index": "b", + "cost": 1.8044, + "chosen": true + } /* best_covering_index_scan */, + "setup_range_conditions": [ + ] /* setup_range_conditions */, + "group_index_range": { + "potential_group_range_indices": [ + { + "index": "PRIMARY", + "covering": true, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "records": 1, + "cost": 1.2 + }, + { + "index": "b", + "usable": false, + "cause": "not_covering" + } + ] /* potential_group_range_indices */ + } /* group_index_range */, + "best_group_range_summary": { + "type": "index_group", + "index": "PRIMARY", + "group_attribute": "b", + "min_aggregate": false, + "max_aggregate": true, + "distinct_aggregate": false, + "records": 1, + "cost": 1.2, + "key_parts_used_for_access": [ + "a" + ] /* key_parts_used_for_access */, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "chosen": true + } /* best_group_range_summary */, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "PRIMARY", + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */, + "index_only": true, + "records": 1, + "cost": 1.21, + "rowid_ordered": true, + "chosen": false, + "cause": "cost" + }, + { + "index": "b", + "ranges": [ + "b < 2" + ] /* 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 */, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "index_group", + "index": "PRIMARY", + "group_attribute": "b", + "min_aggregate": false, + "max_aggregate": true, + "distinct_aggregate": false, + "records": 1, + "cost": 1.2, + "key_parts_used_for_access": [ + "a" + ] /* key_parts_used_for_access */, + "ranges": [ + "1 <= a <= 1 AND b < 2" + ] /* ranges */ + } /* range_access_plan */, + "records_for_plan": 1, + "cost_for_plan": 1.2, + "chosen": true + } /* chosen_range_access_summary */ + } /* range_analysis */ + } + ] /* records_estimation */ + }, + { + "considered_execution_plans": "..." + }, + { + "attaching_conditions_to_tables": { + "original_condition": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2))", + "attached_conditions_computation": [ + { + "access_type_changed": { + "database": "test", + "table": "t1", + "index": "PRIMARY", + "old_type": "ref", + "new_type": "range", + "cause": "uses_more_keyparts" + } /* access_type_changed */ + } + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t1", + "attached": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2))" + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "refine_plan": [ + { + "database": "test", + "table": "t1", + "scan_type": "table" + } + ] /* refine_plan */ + } + ] /* steps */ + } /* join_optimization */ + }, + { + "join_execution": { + "select#": 1, + "steps": [ + ] /* steps */ + } /* join_execution */ + } + ] /* steps */ +} 0 0 + +drop table t1; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-05-24 12:46:22 +0000 +++ b/sql/sql_select.cc 2011-05-27 12:37:37 +0000 @@ -7637,18 +7637,21 @@ best_access_path(JOIN *join, loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ - trace_access_idx.add("records", records).add("cost", tmp); - if (tmp < best_time - records * ROW_EVALUATE_COST || - (quick_matches_more_parts && - quick_records < best_quick_records)) - { - best_quick_records = quick_records; - best_time= tmp + records * ROW_EVALUATE_COST; - best= tmp; - best_records= records; - best_key= start_key; - best_max_key_part= max_key_part; - best_ref_depends_map= found_ref; + + { + const double idx_time= tmp + records * ROW_EVALUATE_COST; + trace_access_idx.add("records", records).add("cost", idx_time); + if (idx_time < best_time || + (quick_matches_more_parts && quick_records < best_quick_records)) + { + best_quick_records = quick_records; + best_time= idx_time; + best= tmp; + best_records= records; + best_key= start_key; + best_max_key_part= max_key_part; + best_ref_depends_map= found_ref; + } } done_with_index: trace_access_idx.add("chosen", best_key == start_key); @@ -10093,7 +10096,20 @@ static bool make_join_select(JOIN *join, (uint) tab->ref.key == tab->quick->index && tab->ref.key_length < tab->quick->max_used_key_length) { - /* Range uses longer key; Use this instead of ref on key */ + /* + Range uses longer key; Use this instead of ref on key + + Todo: This decision should rather be made in + best_access_path() + */ + Opt_trace_object wrapper(trace); + Opt_trace_object (trace, "access_type_changed"). + add_utf8_table(tab->table). + add_utf8("index", tab->table->key_info[tab->ref.key].name). + add_alnum("old_type", "ref"). + add_alnum("new_type", "range"). + add_alnum("cause", "uses_more_keyparts"); + tab->type=JT_ALL; use_quick_range=1; tab->use_quick=QS_RANGE; --===============3516312748176819247== 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\ # 6nplx99u6wlarfqs # target_branch: file:///export/home/jl208045/mysql/wl4800/mysql-next-\ # mr-opt-backporting-wl4800/ # testament_sha1: 1e41e4a1dde91a09d23ff2deeddea01f54469ff6 # timestamp: 2011-05-27 14:37:41 +0200 # source_branch: file:///export/home/jl208045/mysql/wl4800/mysql-next-\ # mr-opt-backporting-wl4800/ # base_revision_id: tor.didriksen@stripped\ # xvi11830h6lljkx3 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfccfDIAE/b/gFV1VUBZ//// f///+v////5gGUvfAFK+e57zuwASE20AFHRQDXQG1prSrQFrDdg07YFFJAAoKlQoAKoFAAkop6aJ qn4qPyU/TUwmm1PQobUHqA2po0AMjIyGgDQyNBKiGjU9SejyJqaGg0GTQGgAAAAADQAAHBo0aBoN AZMQGRoZAAGmmQAADBAAanpJkKaDIaGmgwQaZAZGIyDTQxAYjI0aaAwiiJAaNNTETyU/U9QwBT0a p6n5KGmMkA0B5TRoG1Gmm1AqSIBAgAmmTTSZNGkyYk9JM1GaQ08KfpQeUeUA9I2pgwntnASXMONJ Zx+pcZ3Q2Dk4CQ+EyiJGmeM5SEcvQWdxGhQdpDFZziZKCSAjMoilfzdGpZB5IC0gPEA+TjoUPs4T RbsjwgpsM+jGHuPAfvGB1yNR9OGtmTmNsGVff0fhF/ZTfU3tlKUm5+UoYpP6cEuYmBRdMkweTNkm QYlKUxYFKMF0UpJQyYsGLJKepmhmoUpFJRSimLMpmwZLqLl3SkpRKTIpJRRiMCxZOhcXIwFJGCXT EYM3ik5TlS1d3kz6/UtVGLx7u7vxx774PP8XRO+M+NOSUDdUkl6e8d3B2t7gxjD/QdDBddltTJeV jlhrz6Yar8GuGWnBO9SREn9nR7bk1pIIjMJJLUYoP8p67E/+e6yS9KpmoFnXWHI7zxMgaXpeV6ad HtcnOMDAQf5INRA2WZLozZMF29RcpKUP5+9P5FIUSikkp6D/wPQUqqopQqqpVRFQOH3oJS2Lf0In EKlPJXlqPBzGM82IxQRHJWZ5be/ax71NGFsKrBo1f7Ik3sl25rdnpTWy91CAHgAj2MECBAikBU4j FvtCBhCBiEsJLUCw+hDgaWgNRwtLxdMAgkZD7FrdRCsujSUpXwFK0ImwBEBhaiACCdEAHUHE/Eir NAtCjREitWLMt1E+ijh+pO9q5M/X2toFBUodYXHUWjccoH70omztsdnv7dQ3i/8kcDqTKBlclgdJ 2ec8nHYFY3Nc8kRTBAkBin3w0hTZpqpSGDBToWFH9yEWkQNrI90+7enXxsMsc+hwyxcE6xe9/DPD EY6n6tMrhp+3oxwa7Z3Pr3Qv+DA5aWR5T+M6ozm/xnTm6uzgBj4dTJKbypKsV5P1hIlUeIlHkAkn ONoaawEZQdpJvChQyGQJG57vRuj09/eZyyY7Qzeq1Ii3w9UmFZfh0DLdw31du7iY/Dw07HzKdrgO dKf5PgXbN77HvZruDBkpiwWU1WLGcx3+fI9v637H7TBhE6XMjR71NSXYLNxSXWKUgpT86YrqTeRZ SSyhSkpFkzWJPRkjAposlhZdiYJZiYLsWLBdZTJfZJtwcF1KP2H7VP7j9bnZGRNMi344Jq1WKTFj q1LJnk/FopRNFLrLJsXXXSlm34puYGLVg2ZWYsWTBtgYGhZZY0KP2plDAxUpSlMrMmi92KN5ZquZ S7JRm1TS666WbKZSxomBm/Wl2qZLpssTFMWilyUlLNTRReaZy3IP2/kpudbI+vi5ZJ7wqQB6+QfB qwPCEhAkBvIQ/2sHit/LTB0Z/Al7vOHWEDQXzdDUqYBgkuLS0+udLgrPWWuzf28CiPBSJLE9KkDc j63J9xcP8TklI3tVEWLLKRSUpSmazFiwYLN66YLLJispZkuxUuUYqWYrLGJSyMTFdiUwUssVChMN 7vajKS0d1EKg6TtPOxeHZj9fyRSab+8sCg/+PwnpG/eHOq+zbERl9QJp3mLe0M1pxe0O+YrRYY1V FzqtoeY1KmD89Foz1bMExedOFKrRP4QsObgrmSZqVVVIus46nfiwek9yQb/q9g653ABexp4ET6aF gYX6NrHsVTs7BV4iBaACht4bD6h3/8VU7ezz1D21El6XrCqqLVJO/7ZTFphXlhby0XsofPJ5L+5U MmJYRUblWktldk36HLfi2epumepoyRhFIm7SsmT0WmbHzdn6eTbNTWzmU3GFFje3TcnC9KYSMpaO Zm+VZRyYzK7Sf58x7z2zJ+Sdz2zu4fEsIy3MOthlK/uVfvtbvFomI1il1C48JQdjV9yFt7aYpJp1 OPbaTCisjwuWIX2V9Lu/Ti55mIs4Lfw7uPkrhims7N2/dwOKm/xI4zgYTLDd9eV8LZV52rNnxpOh ni0ZQ6FToRR8/DiulhUfXzde5udLZ9fFi4S0bONHou92LpZpkqdUWwV57nQdrPyalnlMhRC5zOXU WF3xnHVMJYFzqN2Qu0mErl20wXRpVvCBd7jDEoS7aXUyWZTnqCbpPJS7Mpuy7Nl2Smpv5TBc4+Vm 5sRg+x4729tNhob9Z0XrHBgbqyb+c0Xlc19/M5nz6t++qvtJGLaZOpxXEXTlIQNGTiWcRjhv40hK N2VxcdMTQRELUko2mrGd84sp0o3OjgmrlMXDBwU0MBmvmvqxYFdvurRWRj8Wze6Zia9DOasnGV9/ cuuw2Iv0ORGl1Ondn6MFpw4FpN3bn3Ov0XcHM+x2q2/o2dkQ6lWkIFvSOKCKTkRJEvTMglUQix09 j6FISTxRAD0njU823M0m/fheMtzqos3uDwv512ubG9lNaVSqVmWJqxmGDoWlmO6/PklqfD6P7Vpi 1czuZuflvdXKF2rs5NVnWwdDfpj/U6P6Ls5w5C0b+3Pnqt+drXvfGYrucsykzZsVxzKROZWDhua0 rCuOzbOyzHBr0TxOOC4xacHBto7NmPBTxyexswcm5txYulh17cnm+w53Jzm9bhbr5961rLse45TH rcm2TRS2DwYT6nY4TOdcxs2de9vbzlnZlRypfbC6tppbjK5mjl3TbLgcdqqerFtqs4t7MYtd9kIW ywF5oKlpUxmV7Hq0yXnK8gJl1Aul00po6N1N+FucteryurlbmsRllM3UZqZyIfC0gkz5rJisqL9P ylEi+8bOXaL44l6I0ddKURai/bi4PXwYO10drBm5cRz8ro9HZTQ7mrrWZOrPvu9eL4N2jtrtqq5V WHZt0dGanVJj1rrtNlzTLZdd8jwXZezrb2OKWdVmSb9ys8mOKnJWLDuwOJdmpazTq07uizubndo6 HM2aKjpYO15KenR2unbd0tmHMuvjjguZ9LMYM2C3YsvOLvZZ9ezmu3t7U2KlK0YTZ5TfwY04HS4Z tWrqUrTnI7Mawz47nM/JPY4vZMyOybvBBHq6rIHFAI14YugZ1cVNGWSNdnBIE4o10JEwSJWcMSgt pEMTIU3vLciTVw6s+XHovWsSjMCfCataSR2kDN8CEYAy2Za5cVa3RMwxBERBDG3wAgmIhhqFVwnh +gfT6Go0mcR1T+cfZH/05on4xFDM/dRClxrGIISIY9r8D7GxJMUlE+0rgbAPrR6n8xJ8D5zQ8DwJ LBRsZbqifWBeaG4/6tVcJFyDVKUUI/oso7SPOUlAulxNQw5knFJfLqJkb3Yl7Joc5a6zEWEvufls HxFz7wNTpA2OtzGngMIi0fi7znYJoqM4ma0vKRSpqfGQaQP4nzuyg/wM9ye5OJytXqBlvNDtM6aF zOtU3AfEmWx1sECEP3Y5SgS1Te5nxnF3OWLVxnXKjPWbp2q5ppNzeqWZR48vH+ipuZLkwuOILpdB CazLEYM64EgvSWHcksqwS0jRJHv6j/ofxUp/JSyn8k/imDzTJo+7qesmy5DXKzXPGUrgA62hgGga MGGkOynDgYLZBg/Of7j+CUuZC5on6MjQxeShdi2NSxrWdotJUx2Ni47N5k1WMAwZ/8NZnrMDI0MT gbzFqyoLWU2LLMkYNV+FViZsJZNSszebk4FMFHAz0rFm2/VqfqbNxScWc0moamKin+viaY0qsTom swUSm3ClNzIOYu/Wk/ST+9Syk/wXQakPY/rRSiSU/gP3rCwU4qQtKOwA1eZSEgem8I3TcGgbpvF8 3weVjiCN5EPUFT9xhoXP5wT+R9XCq8YEKWewIIF1skPCpDhJmaloAWHuMB5UX3Sly2ExbSJgUnEc IrDoRZWMs6yLDAnoqGfxeJQopSYnY7FKb7DGkqI4ryPlUkj2CkVSK+TVkwTZ18V/3OW4TMimdEY5 k9SK4V5oxt9RLMr9yyzRiWFmDBedI6GqlKIINIea9TThB+bnL8YuMhICeSQedsYXU1lt8Yw17TSu WBZGJJCvYwh4qEAofFlgAxAqyiDZv83bdk+K7oUpaWpVcPi+gfW+eb1VInxUPhXd3Otieb4OpyPF +eInv5/lTr3ux2ebOvNX73g9Hk6m+bmjFwDdIs5PHwm5t3EnMyeteSPDvHIk9Z7XiYdnhb5x4evc jrmVjgdaMlNBfClWSoGq3SDyy3GdIs0UshT/D5JLFGwlUbCCA7iqhkCA3MDoLTNZVgUqlJb4dN8J dSdzzU9T29fi7nvcPN09t/Y3OSPY4ObijZ9zLhz7oLxG0uhcpfSYPJpxIx0jiIyFBcgICkaKuMwq YwwdUROQsgQCxrCgvCggTq4Dmdw5sb4m1Rx9xXBCZzh6jMGw3nQm5i7/s+vNJ3zS3rz88sXNlLY6 3m3swnBl8CixSjJFLJLl1FKKMIrOt9uLJKSiUL8MUGV1h47ezycze09nqdT2sLr+3422stfHuzZP Y+hvb+vtU+wUPa4VGNLdfAO8UdkKGsssTCX9haXqGBYaPX3O99JU9e0ifvs1WstEs7U2it1tmZZ5 XF2g1drm9Tsl26t9myMiOw3yKajXJ4y7JhjH9s+UsVtOBTGz4jrwxRaIqTvyhkOyWSdlyPmT3mvl tX5ttL6WLFKkmRZPm37x7mZNMWWTi8W9TUsMKxswXUwWGKzCbc2ORJ7VH4Tej3JKoQCFeoNSuSkB yd9VOY3Oa9BMY8RXKgb1Df6ppT41J7orofH3PfMHvfBb5l2HL5XbHTzPNqmDb3Ee9u/Is2evmb4d r5+uHzdD946fqFchwBDlOj4mi2gwYPKNWm8Eg5mze9eA6YFOwIFMwPxhBKsmSQWVIEJFIVJBs6T0 dmLufEnJzlOwpU4rjoc49XuOp6re2laPdO10zHZCkcM/jvwR9IqYPkWqjG6xW6epdIvrk49HbPR7 XP7JJGrOV47Lrjxuo+I+VtR6+yOG56umoOc3OjfMcfv7p5Obn/LpDnPlPdaYs0TnGkQ1ZnoVKHef Z6Y5GXnI/NgxiKoc4bjs6b4zrTrpzt6llpPUXOkZDM7Pn9N+qNqBRKgcLN0pv7KpHab+VkHT6MXm 3Dob2RuFM6kPtqbPY82/KeE4u1SlKfvVCh1dfrbmzTq+lYt5Kjk+ivvcfXVef34LsDnKKLQ4TetI +T6d+M7J8J98+gsyOYfT4Os+g8ZHsePljP4apMGk6SeCRTYjRzvDZ5ZzpaJzixJXUCoLJlJOxYUA zud5spyMVdyJxkin6qrIGnFQHM7Xk0CPqNCc7oaSpJLCn3TvWvOPV6ExWrqUlKmC88y1q2eTJaih 3LYV6pmZzDXGE++fldHMTlfQ5T48ax4ww5rnjxTpeOeGblmmyiktdcS5SlLIpLJ3rLxFQqYGBRep KUUXKi65RPDYbhh9uNkytJLEhE0QSgNBAxSE6nLRUsGFSEVrBIvLFn+M5TnZt7p8iPjy2bFJKSne o4BnQqSEqRAmQ8byFXube0NjuwSkhXhN1jXU2ZyTCGovDJT0wxTXpPKDgcfQH06UTU4zlVc1SdaN 1CS0g+3PkYBgnJhpD15GlHoykQREREBpXEwD94ASp0dY4QtUdgfDXEbGxNGs6ElmkJORcd0wHZ34 NMpE+HCajXAl/c1lHJS/rkrzvJHXkeDzMvhiKRrJ+Eol6vv+s/rZw10E16t30cXU4JPYnAjd79ft c71KU26c9idNHIijDl+xRVEZGa/pILm/hGpftBMwng5z1aZI59JoDCQQzM6hz54TlMZYUKKg9iJa RHlinapu1ln85V11hZZZJZZYsiyM092mG8tENlqWL5s4lWUpYwTWXXmst+GSzBSnSS5fWQ6T7IZJ 4fmyhkRv4JFcja580wGD1LMCeFh7eJl6muswhzSJyME6Wp7OMtwsSqG5IqS11ks63J5scuHBLlNV mClLrHMo8SkyY5L4VVrXYsxbK76ViaDJTBY0UlMiw0U0zUO9715oUmlZGq8U1xXUpRQsPQWYc5Oh UZ8GU/51J0Iouh2/AuOa7VKazrdaPKUeGg4ZtHOnzsp8qTcMup0s1OhKdsTuMx2M6BhNXS53siIz 45knQK8BAYp4ZmjAQJCA1WSykJdzUpGaLnE55FpJyJbmd87kskOuZC9OqVFoJaMmOwsdtk4HOGxu UuRU/1EPyzpcOWgtqWWRuGNllRuCm9ksQ/z+jfsjitOKqR0/Bddc9SS+p1F2ixRPRz8sldrTAYEa dY6Dei3WQzC29vXVTsr7r7tR2bzmTudsJc7WPBODYnOSkWUUslpQXSw4D0bi1Tnb5ukcJ0sp2s36 Kkl9E6ymd0jlH5KC6OCTIXSGsOpKG0tDKEC/MaCw1a38QJ6DpR28206Uj8+L+/L6pePaODDtY8on 5UvLEwI8Chbu6x4S0jsRal5j5nmYOxOPZN7wyaOLfbGUOYd07O/7fGYt11SMmU/Q09k4OHd/Ywd8 2Ttjk1Pp+Xf7/gRimdPrazqqI7XpU+ydfDnsRliUUomR2uhdNdD8k2MHb7SyFf4tmcwne99UlK4W scSk83mZQmyjeVLCXp9q7M8Xm63TpiN7h5OpB1ZUN3lu7HM9T/Sngny/Sksf1J/7LKSkn4lH7h9j +l8mmhZNE0aGr8S79yyUp/NLsl2Joi5gzP/4u5IpwoSHuOPhkA== --===============3516312748176819247==--