#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem.bichot@stripped
3313 Jorgen Loland 2011-05-23
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
modified:
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 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2011-05-06 12:58:06 +0000
+++ b/mysql-test/r/group_min_max.result 2011-05-23 08:40:27 +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-23 08:40:27 +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-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result 2011-05-23 08:40:27 +0000
@@ -1369,7 +1369,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
},
{
@@ -1401,7 +1401,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
},
{
@@ -1698,7 +1698,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
},
{
@@ -4484,7 +4484,7 @@ trace
"access_type": "ref",
"index": "PRIMARY",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -4612,7 +4612,7 @@ trace
"access_type": "ref",
"index": "PRIMARY",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -6369,7 +6369,7 @@ select * from t6 where d in (select f1()
"access_type": "ref",
"index": "d",
"records": 1,
- "cost": 3,
+ "cost": 3.2,
"chosen": true
},
{
@@ -6405,7 +6405,7 @@ select * from t6 where d in (select f1()
"access_type": "ref",
"index": "d",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -7008,7 +7008,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
},
{
@@ -7044,7 +7044,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
},
{
@@ -8466,7 +8466,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-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result 2011-05-23 08:40:27 +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
},
{
@@ -4468,7 +4468,7 @@ trace
"access_type": "ref",
"index": "PRIMARY",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -4596,7 +4596,7 @@ trace
"access_type": "ref",
"index": "PRIMARY",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -6349,7 +6349,7 @@ select * from t6 where d in (select f1()
"access_type": "ref",
"index": "d",
"records": 1,
- "cost": 3,
+ "cost": 3.2,
"chosen": true
},
{
@@ -6385,7 +6385,7 @@ select * from t6 where d in (select f1()
"access_type": "ref",
"index": "d",
"records": 1,
- "cost": 1,
+ "cost": 1.2,
"chosen": true
},
{
@@ -6978,7 +6978,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
},
{
@@ -7014,7 +7014,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
},
{
@@ -8450,7 +8450,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-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result 2011-05-23 08:40:27 +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
},
{
@@ -5440,3 +5440,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-20 13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result 2011-05-23 08:40:27 +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
},
{
@@ -5432,3 +5432,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-20 12:38:41 +0000
+++ b/sql/sql_select.cc 2011-05-23 08:40:27 +0000
@@ -7636,18 +7636,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;
+
+ {
+ 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);
@@ -10096,7 +10099,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;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110523084027-e11h0lajj1lksf46.bundle
