List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:January 25 2011 1:18pm
Subject:bzr commit into mysql-trunk branch (ole.john.aske:3549) Bug#59326
View as plain text  
#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
Thread
bzr commit into mysql-trunk branch (ole.john.aske:3549) Bug#59326Ole John Aske25 Jan
  • Re: bzr commit into mysql-trunk branch (ole.john.aske:3549) Bug#59326Olav Sandstaa16 Feb
    • Re: bzr commit into mysql-trunk branch (ole.john.aske:3549) Bug#59326Ole John Aske16 Feb