List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:August 10 2007 1:27am
Subject:bk commit into 5.1 tree (sergefp:1.2562) BUG#28554
View as plain text  
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#28554Sergey Petrunia10 Aug