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