Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-08-10 05:27:32+04:00, sergefp@stripped +6 -0
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
Make order in full/quick table scan calculation part of best_access_path():
- Do not have costs of condition evaluation added twice for quick
select-based scans.
- Do take join buffering into account for quick select scans
- Correctly use the found_constraint heuristic.
[this is a preliminary commit]
mysql-test/r/distinct.result@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +2 -2
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
- Update test results
mysql-test/r/greedy_optimizer.result@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +36 -36
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
- Update test results
mysql-test/r/order_by.result@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +4 -4
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
- Update test results
mysql-test/r/subselect.result@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +1 -1
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
- Update test results
mysql-test/r/union.result@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +1 -1
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
- Update test results
sql/sql_select.cc@stripped, 2007-08-10 05:27:22+04:00, sergefp@stripped +75 -70
BUG#28554: Optimizer wrongly prefers index for (key1=val1) over (PK<=val2)
Make order in full/quick table scan calculation part of best_access_path():
- Do not have costs of condition evaluation added twice for quick
select-based scans.
- Do take join buffering into account for quick select scans
- Correctly use the found_constraint heuristic.
diff -Nrup a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
--- a/mysql-test/r/distinct.result 2007-08-02 23:45:48 +04:00
+++ b/mysql-test/r/distinct.result 2007-08-10 05:27:22 +04:00
@@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3);
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary
+1 SIMPLE t2 index a a 4 NULL 5 Using index; Using temporary
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index
-1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
a
1
diff -Nrup a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result
--- a/mysql-test/r/greedy_optimizer.result 2007-05-29 16:57:20 +04:00
+++ b/mysql-test/r/greedy_optimizer.result 2007-08-10 05:27:22 +04:00
@@ -144,8 +144,8 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -153,11 +153,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -165,11 +165,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -177,11 +177,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -189,7 +189,7 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
set optimizer_prune_level=0;
select @@optimizer_prune_level;
@@optimizer_prune_level
@@ -456,8 +456,8 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -465,11 +465,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -477,11 +477,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -489,11 +489,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -501,7 +501,7 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -608,8 +608,8 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -617,11 +617,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
@@ -629,11 +629,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -641,11 +641,11 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
@@ -653,5 +653,5 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 598.187037
drop table t1,t2,t3,t4,t5,t6,t7;
diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
--- a/mysql-test/r/order_by.result 2007-08-05 04:15:24 +04:00
+++ b/mysql-test/r/order_by.result 2007-08-10 05:27:22 +04:00
@@ -495,8 +495,8 @@ gid sid uid
103853 5 250
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
+1 SIMPLE t3 index PRIMARY PRIMARY 2 NULL 6 Using index; Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
@@ -504,9 +504,9 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t3 index PRIMARY PRIMARY 2 NULL 6 Using index; Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result 2007-08-02 23:45:49 +04:00
+++ b/mysql-test/r/subselect.result 2007-08-10 05:27:22 +04:00
@@ -1355,7 +1355,7 @@ explain extended select * from t2 where
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
diff -Nrup a/mysql-test/r/union.result b/mysql-test/r/union.result
--- a/mysql-test/r/union.result 2007-05-29 16:57:47 +04:00
+++ b/mysql-test/r/union.result 2007-08-10 05:27:22 +04:00
@@ -500,7 +500,7 @@ explain (select * from t1 where a=1 and
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index
-2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer
+2 UNION t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id select_type table type possible_keys key key_len ref rows Extra
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-08-05 09:11:25 +04:00
+++ b/sql/sql_select.cc 2007-08-10 05:27:22 +04:00
@@ -3929,12 +3929,12 @@ best_access_path(JOIN *join,
{
/*
- if 1. expression doesn't refer to forward tables
- 2. we won't get two ref-or-null's
+ if 1. expression refers only to table in constructed join prefix
+ 2. we won't get ".. OR IS NULL" for two keyparts
*/
- if (!(remaining_tables & keyuse->used_tables) &&
+ if (!(remaining_tables & keyuse->used_tables) && // (1)
!(ref_or_null_part && (keyuse->optimize &
- KEY_OPTIMIZE_REF_OR_NULL)))
+ KEY_OPTIMIZE_REF_OR_NULL))) //(2)
{
found_part|= keyuse->keypart_map;
if (!(keyuse->used_tables & ~join->const_table_map))
@@ -3985,7 +3985,9 @@ best_access_path(JOIN *join,
}
else
{
- found_constraint= 1;
+ /* TRUE <=> found "t.keyXpartY= non_const_expr" */
+ found_constraint= test(found_ref & ~join->const_table_map);
+
/*
Check if we found full key
*/
@@ -4289,93 +4291,96 @@ best_access_path(JOIN *join,
! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
!(s->table->force_index && best_key && !s->quick)) // (4)
{ // Check full join
- ha_rows rnd_records= s->found_records;
/*
- If there is a filtering condition on the table (i.e. ref analyzer found
- at least one "table.keyXpartY= exprZ", where exprZ refers only to tables
- preceding this table in the join order we're now considering), then
- assume that 25% of the rows will be filtered out by this condition.
+ rnd_records is the table's fanout. The most precise fanout estimate we
+ have is quick_condition_rows ( = E(#rows that match table condition).
- This heuristic is supposed to force tables used in exprZ to be before
- this table in join order.
+ Additionally, if found_constraint==TRUE, this means that the ref
+ optimizer has detected one or more "table.keyXpartY = non_const_expr"
+ equalities, and we assume that they will filter out 25% of records.
*/
+ double rnd_records= s->table->quick_condition_rows;
if (found_constraint)
- rnd_records-= rnd_records/4;
+ rnd_records-= rnd_records / 4;
/*
- If applicable, get a more accurate estimate. Don't use the two
- heuristics at once.
+ We need 2 cost values:
+ - 'tmp' will have only table access costs
+ - compared_cost will also include cost of evaluating the condition
+ attached to this table. It is used to compare with ref access.
+
+ The costs of comparisons (1/TIME_FOR_COMPARE each) are
+ unrealistically high, but when we use them for compares they serve
+ a purpose of giving additional penalty to plans that produce more
+ rows.
*/
- if (s->table->quick_condition_rows != s->found_records)
- rnd_records= s->table->quick_condition_rows;
+ double compared_cost;
+
- /*
- Range optimizer never proposes a RANGE if it isn't better
- than FULL: so if RANGE is present, it's always preferred to FULL.
- Here we estimate its cost.
- */
if (s->quick)
{
- /*
- For each record we:
- - read record range through 'quick'
- - skip rows which does not satisfy WHERE constraints
- TODO:
- We take into account possible use of join cache for ALL/index
- access (see first else-branch below), but we don't take it into
- account here for range/index_merge access. Find out why this is so.
+ /* quick select cost includes compares cost, remove it */
+ tmp= s->quick->read_time - s->quick->records/TIME_FOR_COMPARE;
+ }
+ else
+ tmp= s->table->file->scan_time();
+
+ if (s->table->map & join->outer_join)
+ {
+ /*
+ We're not using join buffering. For each row combination, we will
+ - do a quick select scan or a full table scan
+ - evaluate part of the WHERE clause for each
+ (join_prefix, s->table->row) combination.
*/
- tmp= record_count *
- (s->quick->read_time +
- (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+ tmp *= record_count;
+ compared_cost= tmp + (record_count * s->found_records /
+ (double) TIME_FOR_COMPARE);
}
else
{
- /* Estimate cost of reading table. */
- tmp= s->table->file->scan_time();
- if (s->table->map & join->outer_join) // Can't use join cache
- {
- /*
- For each record we have to:
- - read the whole table record
- - skip rows which does not satisfy join condition
- */
- tmp= record_count *
- (tmp +
- (s->records - rnd_records)/(double) TIME_FOR_COMPARE);
- }
- else
- {
- /* We read the table as many times as join buffer becomes full. */
- tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
- record_count /
- (double) thd->variables.join_buff_size));
- /*
- We don't make full cartesian product between rows in the scanned
- table and existing records because we skip all rows from the
- scanned table, which does not satisfy join condition when
- we read the table (see flush_cached_records for details). Here we
- take into account cost to read and skip these records.
- */
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
- }
+ /*
+ We'll use join buffering. The table will be read as many times as
+ join buffer becomes full.
+ */
+ double n_times= (1.0 + floor((double) cache_record_length(join,idx) *
+ record_count /
+ (double) thd->variables.join_buff_size));
+ tmp *= n_times;
+ compared_cost= tmp + s->found_records * record_count / TIME_FOR_COMPARE;
+ /*
+ With join buffering, the condition is split into two parts:
+ - condition that depends on this table only. Checked every time we
+ read a record
+ - condition that depends on both this table and the preceding
+ tables. This part is checked when we join this table record with
+ contents of the join buffer.
+
+ The 1st part does some kind of pre-filtering, so the 2nd part of the
+ condition will be invoked fewer # of times. Assuming the evaluation
+ costs of the parts are equal, we should have used something like:
+
+ // the first part:
+ (n_times * s->found_records/(double)(TIME_FOR_COMPARE*2)) +
+ // the second part:
+ (record_count * s->table->quick_condition_rows /
+ (double)(TIME_FOR_COMPARE*2))
+
+ But when I put this in I get odd plan choices (like full 12-row table
+ scan chosen over eq_ref), so for the moment we use the formula that
+ overestimates #of compares.
+ */
}
- /*
- We estimate the cost of evaluating WHERE clause for found records
- as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
- tmp give us total cost of using TABLE SCAN
- */
- if (best == DBL_MAX ||
- (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
- best + record_count/(double) TIME_FOR_COMPARE*records))
+ if (best == DBL_MAX ||
+ (compared_cost < best + record_count/(double)TIME_FOR_COMPARE*records))
{
/*
If the table has a range (s->quick is set) make_join_select()
will ensure that this will be used
*/
best= tmp;
- records= rows2double(rnd_records);
+ records= rnd_records;
best_key= 0;
/* range/index_merge/ALL/index access method are "independent", so: */
best_ref_depends_map= 0;
| Thread |
|---|
| • bk commit into 5.1 tree (sergefp:1.2562) BUG#28554 | Sergey Petrunia | 10 Aug |
