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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3316 to 3317) WL#4800 | Jorgen Loland | 27 May |
