#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:nirbhay.choubey@stripped
3534 Ole John Aske 2011-01-06
Fix for bug#59326: Greedy optimizer produce stupid query execution plans.
The 'greedy' query plan optimizer fails to consider the size of
intermediate query result when calculating the accumulated
'cost' of a query. This may result in slowly executing queries
when there are much faster QEP's available.
When there are multiple JOIN orders available, it would normally be
optimal to join the tables producing the smallest intermediate results
first. This means that any EQ_REF should be joined before REF's, which
in turn should be joined prior to full table scans (ALL).
This fix add the 'CPU-cost' of processing 'current_record_count' records at
each level to 'current_read_time' *before* it is used as 'accumulated cost'
argument to recursive best_extension_by_limited_search() calls. This ensures
that the cost of a huge join-fanout early in the QEP is correctly reflected
in the cost of the final QEP.
Several new MTR tests has been added to greedy_optimizer.test to ensure that
QEP plans joins the tables in the order EQ_REF, REF, ALL whenever possible.
Also updated result files for several test where the QEP or 'query_cost'
changes due to this fix. The changed query plans has been verified to be an
improvement over the original QEPs.
@ mysql-test/r/greedy_optimizer.result
Several QEP changed (improved) due to changes in greedy optimizer.
Generally EQ_REF and REF's are now executed prior to table scans(ALL)
whenever possible.
Added result for new testcases
@ mysql-test/r/select.result
Changed order of result rows due to changed QEP
@ mysql-test/r/subselect.result
Changed (improved) QEP.
modified:
mysql-test/r/greedy_optimizer.result
mysql-test/r/join.result
mysql-test/r/select.result
mysql-test/r/status.result
mysql-test/r/subselect.result
mysql-test/t/greedy_optimizer.test
sql/sql_select.cc
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result 2009-01-16 10:45:17 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2011-01-06 12:21:00 +0000
@@ -200,28 +200,28 @@ select @@optimizer_search_depth;
0
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL 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 t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1371.437037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL 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 t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1371.437037
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 t2 ALL NULL NULL NULL NULL 6
@@ -229,11 +229,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -241,11 +241,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -253,11 +253,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -265,11 +265,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -352,28 +352,28 @@ select @@optimizer_search_depth;
62
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL 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 t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1371.437037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL 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 t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1371.437037
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 t2 ALL NULL NULL NULL NULL 6
@@ -381,11 +381,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -393,11 +393,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -405,11 +405,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
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 t2 ALL NULL NULL NULL NULL 6
@@ -417,11 +417,11 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.418727
+Last_query_cost 362.618727
set optimizer_prune_level=1;
select @@optimizer_prune_level;
@@optimizer_prune_level
@@ -441,7 +441,7 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1693.637037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
@@ -453,55 +453,55 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1693.637037
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 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
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 844.037037
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 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
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 844.037037
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 t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 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
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
-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 844.037037
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 t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 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
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
-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 844.037037
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -593,7 +593,7 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1693.637037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
@@ -605,55 +605,55 @@ id select_type table type possible_keys
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.837037
+Last_query_cost 1693.637037
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 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
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 844.037037
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 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
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 794.837037
+Last_query_cost 844.037037
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 t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 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
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
-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 844.037037
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 t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 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
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
-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 844.037037
drop table t1,t2,t3,t4,t5,t6,t7;
CREATE TABLE t1 (a int, b int, d int, i int);
INSERT INTO t1 VALUES (1,1,1,1);
@@ -729,6 +729,261 @@ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_
;
1
1
-SET optimizer_search_depth = DEFAULT;
DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
-End of 5.0 tests
+#
+# Bug #59326: Greedy optimizer produce stupid query execution plans.
+#
+CREATE TABLE t10(
+K INT NOT NULL AUTO_INCREMENT,
+I INT,
+PRIMARY KEY(K)
+);
+INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+CREATE TABLE t100 LIKE t10;
+INSERT INTO t100(I)
+SELECT X.I FROM t10 AS X,t10 AS Y;
+CREATE TABLE t10000 LIKE t10;
+INSERT INTO t10000(I)
+SELECT X.I FROM t100 AS X, t100 AS Y;
+EXPLAIN SELECT * FROM t10,t100,t10000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t10,t10000,t100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t100,t10,t10000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t100,t10000,t10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t10000,t10,t100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t10000,t100,t10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using join buffer
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer
+EXPLAIN SELECT * FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT * FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT * FROM t100,t10,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT * FROM t100,t10000,t10
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT * FROM t10000,t10,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT * FROM t10000,t100,t10
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y index NULL PRIMARY 4 NULL 10000 Using index; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X index NULL PRIMARY 4 NULL 10000 Using index; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=t10.I
+AND Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=Y.K
+AND Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 10
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X ALL NULL NULL NULL NULL 10000 Using where; Using join buffer
+CREATE INDEX IX ON t10(I);
+CREATE INDEX IX ON t100(I);
+CREATE INDEX IX ON t10000(I);
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y index NULL PRIMARY 4 NULL 10000 Using index; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X index NULL PRIMARY 4 NULL 10000 Using index; Using join buffer
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=t10.I
+AND Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X ref IX IX 5 test.t10.I 1000 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=Y.K
+AND Y.K=t10.I;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 index IX IX 5 NULL 10 Using index
+1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE X ref IX IX 5 test.Y.K 1000 Using where; Using index
+DROP TABLE t10,t100,t10000;
+SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
+END OF 5.0 TESTS
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2010-11-22 21:34:22 +0000
+++ b/mysql-test/r/join.result 2011-01-06 12:21:00 +0000
@@ -894,7 +894,7 @@ id select_type table type possible_keys
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1
show status like '%cost%';
Variable_name Value
-Last_query_cost 24.016090
+Last_query_cost 28.016090
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
Z
^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2010-06-24 08:00:48 +0000
+++ b/mysql-test/r/select.result 2011-01-06 12:21:00 +0000
@@ -2204,10 +2204,10 @@ a a
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
2 1
-3 1
2 2
-3 2
2 3
+3 1
+3 2
3 3
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
a
=== modified file 'mysql-test/r/status.result'
--- a/mysql-test/r/status.result 2009-06-10 08:58:36 +0000
+++ b/mysql-test/r/status.result 2011-01-06 12:21:00 +0000
@@ -149,7 +149,7 @@ a a
1 1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
-Last_query_cost 4.805836
+Last_query_cost 5.205836
DROP TABLE t1;
show status like 'com_show_status';
Variable_name Value
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2010-11-08 10:55:43 +0000
+++ b/mysql-test/r/subselect.result 2011-01-06 12:21:00 +0000
@@ -1353,10 +1353,10 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
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 100.00 Using index
+2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1167 100.01 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
=== modified file 'mysql-test/t/greedy_optimizer.test'
--- a/mysql-test/t/greedy_optimizer.test 2009-01-16 10:45:17 +0000
+++ b/mysql-test/t/greedy_optimizer.test 2011-01-06 12:21:00 +0000
@@ -380,7 +380,145 @@ LEFT JOIN (
) ON t4.d = t1.d
;
-SET optimizer_search_depth = DEFAULT;
DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
---echo End of 5.0 tests
+
+--echo #
+--echo # Bug #59326: Greedy optimizer produce stupid query execution plans.
+--echo #
+
+CREATE TABLE t10(
+ K INT NOT NULL AUTO_INCREMENT,
+ I INT,
+ PRIMARY KEY(K)
+);
+INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+
+CREATE TABLE t100 LIKE t10;
+INSERT INTO t100(I)
+SELECT X.I FROM t10 AS X,t10 AS Y;
+
+CREATE TABLE t10000 LIKE t10;
+INSERT INTO t10000(I)
+SELECT X.I FROM t100 AS X, t100 AS Y;
+
+## All crossproducts should be executed in order t10,t100,t10000
+EXPLAIN SELECT * FROM t10,t100,t10000;
+EXPLAIN SELECT * FROM t10,t10000,t100;
+EXPLAIN SELECT * FROM t100,t10,t10000;
+EXPLAIN SELECT * FROM t100,t10000,t10;
+EXPLAIN SELECT * FROM t10000,t10,t100;
+EXPLAIN SELECT * FROM t10000,t100,t10;
+
+
+## Ordering between T100,T10000 EQ-joined T10 should
+## be with smallest table joined first
+EXPLAIN SELECT * FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+EXPLAIN SELECT * FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+EXPLAIN SELECT * FROM t100,t10,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+EXPLAIN SELECT * FROM t100,t10000,t10
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+EXPLAIN SELECT * FROM t10000,t10,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+EXPLAIN SELECT * FROM t10000,t100,t10
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+
+## EQ_REF Should be executed before table scan(ALL)
+## - Independent of #records in table being EQ_REF-joined
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+
+## EQ_REF & Cross join two instances of t10000 with t10:
+## Always EQ_REF join first before producing cross product
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE Y.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=t10.I
+ AND Y.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=Y.K
+ AND Y.K=t10.I;
+
+## Create indexes to test REF access
+CREATE INDEX IX ON t10(I);
+CREATE INDEX IX ON t100(I);
+CREATE INDEX IX ON t10000(I);
+
+## EQ_REF Should be executed before 'REF'
+## - Independent of #records in table being EQ_REF-joined
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+
+## EQ_REF & Cross join two instances of t10000 with t10:
+## Always EQ_REF join first before producing cross product
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE Y.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=t10.I
+ AND Y.K=t10.I;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.I=Y.K
+ AND Y.K=t10.I;
+
+DROP TABLE t10,t100,t10000;
+
+SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
+
+--ECHO END OF 5.0 TESTS
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-12-28 23:47:05 +0000
+++ b/sql/sql_select.cc 2011-01-06 12:21:00 +0000
@@ -5377,6 +5377,8 @@ best_extension_by_limited_search(JOIN
}
}
+ current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
+
if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) )
{ /* Recursively expand the current partial plan */
swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
@@ -5395,7 +5397,6 @@ best_extension_by_limited_search(JOIN
'join' is either the best partial QEP with 'search_depth' relations,
or the best complete QEP so far, whichever is smaller.
*/
- current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
if (join->sort_by_table &&
join->sort_by_table !=
join->positions[join->const_tables].table->table)
Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110106122100-bi9qlhmxsmvrt3yg.bundle
