#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-trunk/ based on revid:ole.john.aske@stripped
3549 Ole John Aske 2011-01-25
Fix for bug#59326: 'Greedy optimizer produce stupid query execution plans.'
Fixed several defects in the greedy optimization:
1) The greedy optimizer calculate the 'compare-cost' (CPU-cost)
for iterating over the partial plan result at each level in
the query plan as 'record_count / (double) TIME_FOR_COMPARE'
This cost was only used localy for 'best' calculation at each
level, and *not* accumulated into the total cost for the query plan.
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.
To get identical cost for a 'best' optimized query and a straight_join with the
same join order, the same change was also applied to optimize_straight_join()
and get_partial_join_cost()
2) Furthermore to get equal cost for 'best' optimized query and a straight_join
we had to subtract the same '0.001' in optimize_straight_join() as we already
do in best_extension_by_limited_search()
3) When best_extension_by_limited_search() aggregated the 'best' plan a plan
was 'best' by the check :
'if ((search_depth == 1) || (current_read_time < join->best_read))'
The term '(search_depth == 1' incorrectly caused a new best plan to be
collected whenever we reached the specified 'search_depth' - Even if this
partial query plan was more expensive than what we had already found.
... See further comment and pure numbers for this in bugreport.
@ mysql-test/include/check_qep.inc
Compare execution of this query against a known 'best' plan for this query
@ mysql-test/include/expect_qep.inc
Collect expected statistic for query - later used to compare exec. of queries expeted to behave identical
@ mysql-test/r/greedy_optimizer.result
Updated resultfile after greedy optimizer changes. Don't care about increased 'cost' as this
is a relative number.
However, several 'Total_handler_reads' has decreased, and none increased - Which should
be a fairly good indication of this fix improving the existing query plan.
Also added a lots of new tests which will fail wo/ this fix.
@ mysql-test/r/join.result
Accepted udated 'cost'
@ mysql-test/r/join_cache_jcl1.result
Accepted new query plan which looks better as ALL is joined in later. (Decreased fanout)
@ mysql-test/r/join_cache_jcl2.result
Accepted new query plan which looks better as ALL is joined in later. (Decreased fanout)
@ mysql-test/r/join_cache_jcl3.result
Accepted new query plan which looks better as ALL is joined in later. (Decreased fanout)
@ mysql-test/r/join_cache_jcl4.result
Accepted new query plan which looks better as ALL is joined in later. (Decreased fanout)
@ mysql-test/r/select_icp_mrr.result
Same result, another order in resultset (No ORDER BY here)
@ mysql-test/r/select_none.result
Same result, another order in resultset (No ORDER BY here)
@ mysql-test/r/status.result
Accepted new cost
@ mysql-test/r/subquery_sj_none.result
ALL/REF -joining tables with most rows later improved the query plan (Decreased fanout).
Same for doing REF after EQ_REF in the last query (decreased fanout)
@ mysql-test/t/greedy_optimizer.test
Added a lots of new tests which will fail wo/ this fix.
added:
mysql-test/include/check_qep.inc
mysql-test/include/expect_qep.inc
modified:
mysql-test/r/greedy_optimizer.result
mysql-test/r/join.result
mysql-test/r/join_cache_jcl1.result
mysql-test/r/join_cache_jcl2.result
mysql-test/r/join_cache_jcl3.result
mysql-test/r/join_cache_jcl4.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_none.result
mysql-test/r/status.result
mysql-test/r/subquery_sj_none.result
mysql-test/t/greedy_optimizer.test
sql/sql_select.cc
=== added file 'mysql-test/include/check_qep.inc'
--- a/mysql-test/include/check_qep.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/check_qep.inc 2011-01-25 13:18:41 +0000
@@ -0,0 +1,20 @@
+flush status;
+eval EXPLAIN $query;
+eval $query;
+
+let $cost=
+ query_get_value(SHOW STATUS LIKE 'Last_query_cost', Value, 1);
+
+let $reads=
+`select sum(variable_value)
+ from information_schema.session_status
+ where VARIABLE_NAME like 'Handler_read%'`;
+
+#echo Cost: $cost, Handler_reads: $reads;
+
+if ($cost != $best_cost)
+{ echo ### FAILED: Query_cost: $cost, expected: $best_cost ###;
+}
+if ($reads != $best_reads)
+{ echo ### FAILED: Handler_reads: $reads, expected: $best_reads ###;
+}
=== added file 'mysql-test/include/expect_qep.inc'
--- a/mysql-test/include/expect_qep.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/expect_qep.inc 2011-01-25 13:18:41 +0000
@@ -0,0 +1,14 @@
+flush status;
+eval EXPLAIN $query;
+eval $query;
+
+let $best_cost=
+ query_get_value(SHOW STATUS LIKE 'Last_query_cost', Value, 1);
+
+let $best_reads=
+`select sum(variable_value)
+ from information_schema.session_status
+ where VARIABLE_NAME like 'Handler_read%'`;
+
+#echo Expect, cost: $best_cost, Handler_reads: $best_reads;
+
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result 2011-01-25 12:41:38 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2011-01-25 13:18:41 +0000
@@ -124,16 +124,16 @@ 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 (BNL, incremental buffers)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.936459
+Last_query_cost 1372.458334
flush status;
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;
c11
@@ -147,16 +147,16 @@ Total_handler_reads
46
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 (BNL, incremental buffers)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.936459
+Last_query_cost 1372.458334
flush status;
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;
c11
@@ -172,14 +172,14 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.430791
+Last_query_cost 362.673673
flush status;
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;
c11
@@ -190,19 +190,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-1338
+114
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 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.430791
+Last_query_cost 362.673673
flush status;
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;
c11
@@ -213,19 +213,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-1338
+114
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 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.469776
+Last_query_cost 362.776403
flush status;
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;
c11
@@ -238,14 +238,14 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.469776
+Last_query_cost 362.776403
flush status;
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;
c11
@@ -269,7 +269,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 822.001607
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -292,7 +292,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 822.001607
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -315,7 +315,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.014136
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -338,7 +338,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.014136
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -361,7 +361,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.340548
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -381,7 +381,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.340548
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -396,16 +396,16 @@ 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 (BNL, incremental buffers)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.936459
+Last_query_cost 1372.458334
flush status;
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;
c11
@@ -419,16 +419,16 @@ Total_handler_reads
46
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 (BNL, incremental buffers)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 821.936459
+Last_query_cost 1372.458334
flush status;
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;
c11
@@ -444,14 +444,14 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.430791
+Last_query_cost 362.673673
flush status;
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;
c11
@@ -462,19 +462,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-1338
+114
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 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.430791
+Last_query_cost 362.673673
flush status;
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;
c11
@@ -485,19 +485,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-1338
+114
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 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.469776
+Last_query_cost 362.776403
flush status;
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;
c11
@@ -510,14 +510,14 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 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 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 (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 289.469776
+Last_query_cost 362.776403
flush status;
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;
c11
@@ -538,14 +538,14 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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.936459
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -561,14 +561,14 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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.936459
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -583,15 +583,15 @@ Total_handler_reads
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 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 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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-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.968437
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -606,15 +606,15 @@ Total_handler_reads
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 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 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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-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.968437
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -629,15 +629,15 @@ Total_handler_reads
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 Using where
+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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
-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 795.085394
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -645,19 +645,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-24
+6
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 Using where
+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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
-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 795.085394
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -665,7 +665,7 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-24
+6
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -681,7 +681,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 822.001607
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -704,7 +704,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 822.001607
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -727,7 +727,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.014136
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -750,7 +750,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.014136
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -773,7 +773,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.340548
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -793,7 +793,7 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
show status like 'Last_query_cost';
Variable_name Value
-Last_query_cost 795.340548
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -810,14 +810,14 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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.936459
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -833,14 +833,14 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+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 (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
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.936459
+Last_query_cost 1693.801607
flush status;
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;
c11
@@ -855,15 +855,15 @@ Total_handler_reads
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 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 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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-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.968437
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -878,15 +878,15 @@ Total_handler_reads
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 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 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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-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.968437
+Last_query_cost 844.214136
flush status;
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;
c11
@@ -901,15 +901,15 @@ Total_handler_reads
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 Using where
+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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
-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 795.085394
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -917,19 +917,19 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-24
+6
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 Using where
+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 (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
-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 795.085394
+Last_query_cost 844.540548
flush status;
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;
c11
@@ -937,7 +937,7 @@ select sum(variable_value) as Total_hand
from information_schema.session_status
where VARIABLE_NAME like 'Handler_read%';
Total_handler_reads
-24
+6
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);
@@ -1016,3 +1016,562 @@ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_
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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+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 (BNL, incremental buffers)
+1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000 Using join buffer (BNL, incremental buffers)
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=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 Using where
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t100,t10,t10000
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t100,t10000,t10
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t10000,t10,t100
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT COUNT(*) 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 Using where
+1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K 1 Using index
+SELECT COUNT(*) FROM t10000,t100,t10
+WHERE t100.K=t10.I
+AND t10000.K=t10.K;
+COUNT(*)
+9
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+90
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+90
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t10.I;
+COUNT(*)
+90
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+COUNT(*)
+90
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+COUNT(*)
+90
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+AND t10000.K=t100.I;
+COUNT(*)
+90
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 Using where
+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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+flush status;
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 Using where
+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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+CREATE INDEX IX ON t10(I);
+CREATE INDEX IX ON t100(I);
+CREATE INDEX IX ON t10000(I);
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 where; 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
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 where; 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
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 where; 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
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+AND t10000.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 where; 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 (BNL, incremental buffers)
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+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 where; 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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 where; 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 (BNL, incremental buffers)
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I;
+COUNT(*)
+90000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 where; 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
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+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 where; 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
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 where; 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
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+AND Y.I=t10.I;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT STRAIGHT_JOIN 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 where; 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
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+flush status;
+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 where; 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
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+flush status;
+EXPLAIN SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+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 where; 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
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+AND Y.I=X.K;
+COUNT(*)
+9000
+DROP TABLE t10,t100,t10000;
+SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
+END OF 5.6 TESTS
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2010-12-16 17:38:26 +0000
+++ b/mysql-test/r/join.result 2011-01-25 13:18:41 +0000
@@ -895,7 +895,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/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2011-01-18 11:29:05 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2011-01-25 13:18:41 +0000
@@ -1311,11 +1311,11 @@ id select_type table type possible_keys
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, regular buffers)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
+1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid,
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result 2011-01-18 11:29:05 +0000
+++ b/mysql-test/r/join_cache_jcl2.result 2011-01-25 13:18:41 +0000
@@ -1311,11 +1311,11 @@ id select_type table type possible_keys
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
+1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid,
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result 2011-01-18 11:29:05 +0000
+++ b/mysql-test/r/join_cache_jcl3.result 2011-01-25 13:18:41 +0000
@@ -1311,11 +1311,11 @@ id select_type table type possible_keys
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, regular buffers)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
+1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid,
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result 2011-01-18 11:29:05 +0000
+++ b/mysql-test/r/join_cache_jcl4.result 2011-01-25 13:18:41 +0000
@@ -1311,11 +1311,11 @@ id select_type table type possible_keys
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
+1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid,
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_icp_mrr.result 2011-01-25 13:18:41 +0000
@@ -2205,10 +2205,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/select_none.result'
--- a/mysql-test/r/select_none.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_none.result 2011-01-25 13:18:41 +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 2010-05-10 09:37:24 +0000
+++ b/mysql-test/r/status.result 2011-01-25 13:18:41 +0000
@@ -149,7 +149,7 @@ a a
1 1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
-Last_query_cost 4.805989
+Last_query_cost 5.205989
DROP TABLE t1;
show status like 'com_show_status';
Variable_name Value
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-01-25 13:18:41 +0000
@@ -76,10 +76,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 index
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1167 100.00 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
@@ -2881,8 +2881,8 @@ t2.b1 IN (select c1 from t3 where c2 > '
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-3 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (BNL, incremental buffers)
+3 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
+3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (BNL, incremental buffers)
4 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1`,`test`.`t2_16`.`b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and <in_optimizer>(`test`.`t2`.`b1`,<exists>(select 1 from `test`.`t3` where ((`test`.`t3`.`c2` > '0') and (<cache>(`test`.`t2`.`b1`) = `test`.`t3`.`c1`)))) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8)))))
@@ -5221,8 +5221,8 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1
+2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 func 1 Using index
2 DEPENDENT SUBQUERY t3 ref uid uid 5 const 4 Using where
-2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
and t2.uid=t1.fid;
=== modified file 'mysql-test/t/greedy_optimizer.test'
--- a/mysql-test/t/greedy_optimizer.test 2011-01-25 12:41:38 +0000
+++ b/mysql-test/t/greedy_optimizer.test 2011-01-25 13:18:41 +0000
@@ -424,3 +424,336 @@ 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;
+
+let $total_handler_reads=
+select sum(variable_value) from information_schema.session_status
+ where VARIABLE_NAME like 'Handler_read%';
+
+
+## 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 will
+## normally be with smallest EQ-table joined first
+######
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.I;
+--source include/expect_qep.inc
+
+## However, swapping EQ_REF-joined tables gives the same cost
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expect all variants of EQ joining t100 & t10000 with T10
+# to have same cost # handler_reads:
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t100,t10,t10000
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t100,t10000,t10
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10000,t10,t100
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10000,t100,t10
+WHERE t100.K=t10.I
+ AND t10000.K=t10.K;
+--source include/check_qep.inc
+
+
+#####
+## EQ_REF Should be executed before table scan(ALL)
+## - Independent of #records in table being EQ_REF-joined
+#####
+#####
+# Expect: Join EQ_REF(t100) before ALL(t10000)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expect: Join EQ_REF(t10000) before ALL(t100) (star-join)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expect: Join EQ_REF(t10000) before ALL(t100)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.I=t10.I
+ AND t10000.K=t100.I;
+--source include/check_qep.inc
+
+
+#####
+## EQ_REF & ALL join two instances of t10000 with t10:
+## Always EQ_REF join first before producing cross product
+#####
+
+#####
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before 'cross' ALL(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before ALL(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before ALL(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/check_qep.inc
+
+
+
+## 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
+
+####
+# Expected QEP: 'join EQ_REF(t100) on t100.K=t10.I' before REF(t10000)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t100,t10000
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000,t100
+WHERE t100.K=t10.I
+ AND t10000.I=t10.I;
+--source include/check_qep.inc
+
+
+#####
+## EQ_REF & REF join two instances of t10000 with t10:
+#####
+
+#####
+## Expect this QEP, cost & #handler_read
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before 'cross' ALL(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before REF(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+ AND Y.I=t10.I;
+--source include/check_qep.inc
+
+#####
+# Expected QEP: 'join EQ_REF(X) on X.K=t10.I' before REF(Y)
+let $query=
+SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/expect_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/check_qep.inc
+
+let $query=
+SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
+WHERE X.K=t10.I
+ AND Y.I=X.K;
+--source include/check_qep.inc
+
+########
+
+DROP TABLE t10,t100,t10000;
+
+SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
+
+--ECHO END OF 5.6 TESTS
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-24 09:02:03 +0000
+++ b/sql/sql_select.cc 2011-01-25 13:18:41 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -7724,7 +7724,8 @@ optimize_straight_join(JOIN *join, table
/* compute the cost of the new plan extended with 's' */
record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time;
+ read_time+= join->positions[idx].read_time
+ + record_count / (double) TIME_FOR_COMPARE;
advance_sj_state(join, join_tables, s, idx, &record_count, &read_time,
&loose_scan_pos);
@@ -7732,13 +7733,12 @@ optimize_straight_join(JOIN *join, table
++idx;
}
- read_time+= record_count / (double) TIME_FOR_COMPARE;
if (join->sort_by_table &&
join->sort_by_table != join->positions[join->const_tables].table->table)
read_time+= record_count; // We have to make a temp table
memcpy((uchar*) join->best_positions, (uchar*) join->positions,
sizeof(POSITION)*idx);
- join->best_read= read_time;
+ join->best_read= read_time - 0.001; // Similar in best_extension_by_li...
}
@@ -7974,7 +7974,8 @@ greedy_search(JOIN *join,
/* compute the cost of the new plan extended with 'best_table' */
record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time;
+ read_time+= join->positions[idx].read_time
+ + record_count / (double) TIME_FOR_COMPARE;
remaining_tables&= ~(best_table->table->map);
--size_remain;
@@ -8019,10 +8020,11 @@ void get_partial_join_cost(JOIN *join, u
if (join->best_positions[i].records_read)
{
record_count *= join->best_positions[i].records_read;
- read_time += join->best_positions[i].read_time;
+ read_time += join->best_positions[i].read_time
+ + record_count / (double) TIME_FOR_COMPARE;
}
}
- *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE;
+ *read_time_arg= read_time;
*record_count_arg= record_count;
}
@@ -8262,6 +8264,7 @@ best_extension_by_limited_search(JOIN
if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables )
{ /* Recursively expand the current partial plan */
+ current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
if (best_extension_by_limited_search(join,
remaining_tables & ~real_table_bit,
@@ -8288,7 +8291,7 @@ best_extension_by_limited_search(JOIN
Hence it may be wrong.
*/
current_read_time+= current_record_count;
- if ((search_depth == 1) || (current_read_time < join->best_read))
+ if (current_read_time < join->best_read)
{
memcpy((uchar*) join->best_positions, (uchar*) join->positions,
sizeof(POSITION) * (idx + 1));
Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110125131841-j4r7q2mkgm1vh683.bundle
