From: Jorgen Loland Date: May 27 2011 12:37pm Subject: bzr push into mysql-trunk branch (jorgen.loland:3316 to 3317) WL#4800 List-Archive: http://lists.mysql.com/commits/138328 Message-Id: <20110527123758.385B2EEE@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 3316 Tor Didriksen 2011-05-27 s/ASSERT_EQ(false, /ASSERT_FALSE(/g because it is "the right thing" and because it does not compile with gcc 4.5.1 unittest/gunit/opt_trace-t.cc:338:156: error: converting false to pointer type for argument 1 of char testing::internal::IsNullLiteralHelper(testing::internal::Secret*) modified: unittest/gunit/opt_trace-t.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; No bundle (reason: useless for push emails).