List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:February 16 2011 9:34am
Subject:Re: bzr commit into mysql-trunk branch (ole.john.aske:3549) Bug#59326
View as plain text  
Hi Ole John,

I think the patch looks good and that it is correct to include CPU cost 
for comparing records for intermediate result in the aggregated cost for 
the join. The commit comments are very good and convincing.

Thanks for extending the greedy optimizer test with new test cases. I 
have only looked in detail on a few of the many changes in execution 
plans but they all seems to be improvements.

I have only minor comments (see inline). OK to push this patch.

Olav


On 25/01/2011 14:18, Ole John Aske wrote:
> #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 ###;
> +}

1. I think it would be good to add a comment to beginning of this file 
to explain what it is and what it is used for and how to use it (see for 
example mysql_test/include/wait_until_rows_count.inc).

2. (feel free to ignore this) I think we are trying to standardize on 
having reserved words in UPPER case in our test files.

> === 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;
> +

Same as above.

> === 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...

I understand why you added the "- 0.001" but do not like it. I would 
have preferred that we rather removed this from 
best_extension_by_limited_search() which has the same uncommented 
adjustment of the cost. But looking up the commit that introduced this 
constant it has the following comment (see revid: 
sp1r-serg@stripped).

sql_select.cc:
   if many plans have identical cost, which one will be used depends on 
how compiler optimizes floating-point calculations
   this fix adds repeatability to the optimizer

So I guess it is OK to have it here too....

>   }
>
>
> @@ -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;

With this change this is the first code that is run both for the if-part 
and the else-part of this if-statement. I think it would be better to 
move this outside/before the if (and you might have already done so in 
one of the patches that I have not yet looked at).
It might also be a good idea to add a comment to it saying why we add 
the CPU cost here and not earlier in the function where we add the IO cost.

>           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));
>
>
>
>


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