Hi Ole John,
On 01/13/11 21:14, Ole John Aske wrote:
> On 13.01.2011 18:34, Evgeny Potemkin wrote:
>> Hi Ole John,
>>
>> The patch and the idea is good, thanks for looking into this! But, anyway,
>> I'm unsure about this fix.
>> 1) Your reasoning is quite obvious and straight but the actual cost for some
>> queries has grown (see changes in test results). This could be argued that
>> our cost model is far from good, but then we have to measure the improvement
>> (or regression) somehow. AFAIU this optimization should lead to less rows to
>> be read, but there is no handler stats in your tests. Please add them. BTW it
>> would be good to also show handler stats for queries with grown cost.
> Agree, I can try to add some handler stats to 'greedy' tests to prove that the
> optimizer will choose the QEP with fewest handler calls with this patch.
> Re 'cost for some queries has grown': Remember these costs are only relative
> numbers. If the cost for the chosen QEP grows, The cost for the discarded QEP
> has grown even more.
I agree that the cost is an abstract value, but we have to take it into account
since basically it's our cost model. Changing the way it is calculated is
actually changing the cost model, and we have to be sure that the change brings
an improvement and doesn't introduce regressions. Thus it's a small but yet
serious change.
Re cost growth: I need to check few things. Will send my findings later.
BTW, the fix definitely will go to trunk only. It's too risky for the rest.
>
>> 2) There already were few cases when an obvious 100% improvement caused
>> regressions in some cases. It would be good if you contact our QA and ask
>> them to test this patch for perf. improvements/regressions on big tables.
> This optimizer patch will only affect 'star-join' QEPs.
The thing is that your fix doesn't have any conditions about star joins. Thus,
even if the most effect would be seen on star joins, the change would affect all
joins.
> I don't know the perf tests used by QA, but my general experience is that such
> tests normally are
> very explicit about join ordering. Thus they normally leave little to the
> optimizer in terms of reordering the joins.
QA uses random query generator which could generate any meaningless query;)
Including star and non-star joins. I don't see any problem here.
>
>> 3) Roy has noticed that join buffering could significantly affect the time
>> spent. It would be good if QA will test this patch with join cache turned off
>> and on (only test with default value would be enough).
>
> I don't really see the connection with this bug here: Sure, there are
> anomalities in the result Roy observes wrt. increased runtime when using join
> cache.
> However these results are *without* my patch. Furthermore, when my patch is
> applied the fastest of the QEPs are chosen even for Roy's test.
>
> My opinion is that the performance regression with join cache should be
> reported as another bug.
I agree that the join cache is a completely separate problem and the goal isn't
to benchmark join cache. The idea was to see base difference(when join cache
turned off) against unmodified server and the difference on a 'typical' server
where join cache level is set to default.
>
> Regards Ole John
>
>
>>
>> Regards, Evgen.
>>
>> On 01/06/11 15:21, Ole John Aske wrote:
>>> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on
>>> revid:nirbhay.choubey@stripped
>>>
>>> 3534 Ole John Aske 2011-01-06
>>> Fix for bug#59326: Greedy optimizer produce stupid query execution
>>> plans.
>>>
>>> The 'greedy' query plan optimizer fails to consider the size of
>>> intermediate query result when calculating the accumulated
>>> 'cost' of a query. This may result in slowly executing queries
>>> when there are much faster QEP's available.
>>>
>>> When there are multiple JOIN orders available, it would normally be
>>> optimal to join the tables producing the smallest intermediate
> results
>>> first. This means that any EQ_REF should be joined before REF's,
> which
>>> in turn should be joined prior to full table scans (ALL).
>>>
>>> This fix add the 'CPU-cost' of processing 'current_record_count'
>>> records at
>>> each level to 'current_read_time' *before* it is used as 'accumulated
>
>>> cost'
>>> argument to recursive best_extension_by_limited_search() calls. This
>>> ensures
>>> that the cost of a huge join-fanout early in the QEP is correctly
>>> reflected
>>> in the cost of the final QEP.
>>>
>>> Several new MTR tests has been added to greedy_optimizer.test to
>>> ensure that
>>> QEP plans joins the tables in the order EQ_REF, REF, ALL whenever
>>> possible.
>>>
>>> Also updated result files for several test where the QEP or
> 'query_cost'
>>> changes due to this fix. The changed query plans has been verified to
>
>>> be an
>>> improvement over the original QEPs.
>>> @ mysql-test/r/greedy_optimizer.result
>>> Several QEP changed (improved) due to changes in greedy optimizer.
>>> Generally EQ_REF and REF's are now executed prior to table
> scans(ALL)
>>> whenever possible.
>>>
>>> Added result for new testcases
>>> @ mysql-test/r/select.result
>>> Changed order of result rows due to changed QEP
>>> @ mysql-test/r/subselect.result
>>> Changed (improved) QEP.
>>>
>>> modified:
>>> mysql-test/r/greedy_optimizer.result
>>> mysql-test/r/join.result
>>> mysql-test/r/select.result
>>> mysql-test/r/status.result
>>> mysql-test/r/subselect.result
>>> mysql-test/t/greedy_optimizer.test
>>> sql/sql_select.cc
>>> === modified file 'mysql-test/r/greedy_optimizer.result'
>>> --- a/mysql-test/r/greedy_optimizer.result 2009-01-16 10:45:17 +0000
>>> +++ b/mysql-test/r/greedy_optimizer.result 2011-01-06 12:21:00 +0000
>>> @@ -200,28 +200,28 @@ select @@optimizer_search_depth;
>>> 0
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> -1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1371.437037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> -1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1371.437037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -229,11 +229,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -241,11 +241,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -253,11 +253,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -265,11 +265,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> set optimizer_search_depth=1;
>>> select @@optimizer_search_depth;
>>> @@optimizer_search_depth
>>> @@ -352,28 +352,28 @@ select @@optimizer_search_depth;
>>> 62
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> -1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1371.437037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> -1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1371.437037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -381,11 +381,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -393,11 +393,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -405,11 +405,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
>>> @@ -417,11 +417,11 @@ id select_type table type possible_keys
>>> 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21
>>> 1 Using where
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 289.418727
>>> +Last_query_cost 362.618727
>>> set optimizer_prune_level=1;
>>> select @@optimizer_prune_level;
>>> @@optimizer_prune_level
>>> @@ -441,7 +441,7 @@ id select_type table type possible_keys
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1693.637037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> @@ -453,55 +453,55 @@ id select_type table type possible_keys
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1693.637037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
>>> +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
>>> +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> set optimizer_search_depth=1;
>>> select @@optimizer_search_depth;
>>> @@optimizer_search_depth
>>> @@ -593,7 +593,7 @@ id select_type table type possible_keys
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1693.637037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 =
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
>>> @@ -605,55 +605,55 @@ id select_type table type possible_keys
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62
>>> 1 Using index
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 821.837037
>>> +Last_query_cost 1693.637037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using index
>>> -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using index
>>> -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using index
>>> +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
>>> +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 =
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 =
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>> id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
>>> +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
>>> +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using
>>> where; Using join buffer
>>> -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12
>>> 1 Using where
>>> 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using
>>> where; Using join buffer
>>> -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14
>>> 1 Using where
>>> 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using
>>> where; Using join buffer
>>> -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16
>>> 1 Using where
>>> show status like 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 794.837037
>>> +Last_query_cost 844.037037
>>> drop table t1,t2,t3,t4,t5,t6,t7;
>>> CREATE TABLE t1 (a int, b int, d int, i int);
>>> INSERT INTO t1 VALUES (1,1,1,1);
>>> @@ -729,6 +729,261 @@ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_
>>> ;
>>> 1
>>> 1
>>> -SET optimizer_search_depth = DEFAULT;
>>> DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
>>> -End of 5.0 tests
>>> +#
>>> +# Bug #59326: Greedy optimizer produce stupid query execution plans.
>>> +#
>>> +CREATE TABLE t10(
>>> +K INT NOT NULL AUTO_INCREMENT,
>>> +I INT,
>>> +PRIMARY KEY(K)
>>> +);
>>> +INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
>>> +CREATE TABLE t100 LIKE t10;
>>> +INSERT INTO t100(I)
>>> +SELECT X.I FROM t10 AS X,t10 AS Y;
>>> +CREATE TABLE t10000 LIKE t10;
>>> +INSERT INTO t10000(I)
>>> +SELECT X.I FROM t100 AS X, t100 AS Y;
>>> +EXPLAIN SELECT * FROM t10,t100,t10000;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10,t10000,t100;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t100,t10,t10000;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t100,t10000,t10;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10000,t10,t100;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10000,t100,t10;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> join buffer
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT * FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT * FROM t100,t10,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT * FROM t100,t10000,t10
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT * FROM t10000,t10,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT * FROM t10000,t100,t10
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.K
> 1
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I
>>> 1 Using index
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I
>>> 1 Using index
>>> +1 SIMPLE t10000 ALL NULL NULL NULL NULL 10000
>>> Using where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ALL NULL NULL NULL NULL 100 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y index NULL PRIMARY 4 NULL 10000 Using
>
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X index NULL PRIMARY 4 NULL 10000 Using
>
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y ALL NULL NULL NULL NULL 10000 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +AND Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X ALL NULL NULL NULL NULL 10000 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=X.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y ALL NULL NULL NULL NULL 10000 Using
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +AND Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 ALL NULL NULL NULL NULL 10
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X ALL NULL NULL NULL NULL 10000 Using
>>> where; Using join buffer
>>> +CREATE INDEX IX ON t10(I);
>>> +CREATE INDEX IX ON t100(I);
>>> +CREATE INDEX IX ON t10000(I);
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I
>>> 1 Using index
>>> +1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000
>>> Using where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I
>>> 1 Using index
>>> +1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000
>>> Using where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I
>
>>> 1 Using index
>>> +1 SIMPLE t100 ref IX IX 5 test.t10.I 10 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y index NULL PRIMARY 4 NULL 10000 Using
>
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X index NULL PRIMARY 4 NULL 10000 Using
>
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +AND Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X ref IX IX 5 test.t10.I 1000 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=X.K;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +AND Y.K=t10.I;
>>> +id select_type table type possible_keys key key_len
>>> ref rows Extra
>>> +1 SIMPLE t10 index IX IX 5 NULL 10 Using index
>>> +1 SIMPLE Y eq_ref PRIMARY PRIMARY 4 test.t10.I 1
>
>>> Using index
>>> +1 SIMPLE X ref IX IX 5 test.Y.K 1000 Using
>>> where; Using index
>>> +DROP TABLE t10,t100,t10000;
>>> +SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
>>> +END OF 5.0 TESTS
>>>
>>> === modified file 'mysql-test/r/join.result'
>>> --- a/mysql-test/r/join.result 2010-11-22 21:34:22 +0000
>>> +++ b/mysql-test/r/join.result 2011-01-06 12:21:00 +0000
>>> @@ -894,7 +894,7 @@ id select_type table type possible_keys
>>> 1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1
>>> show status like '%cost%';
>>> Variable_name Value
>>> -Last_query_cost 24.016090
>>> +Last_query_cost 28.016090
>>> select '^^: The above should be ~= 20 + cost(select * from t1). Value less
>
>>> than 20 is an error' Z;
>>> Z
>>> ^^: The above should be ~= 20 + cost(select * from t1). Value less than 20
>
>>> is an error
>>>
>>> === modified file 'mysql-test/r/select.result'
>>> --- a/mysql-test/r/select.result 2010-06-24 08:00:48 +0000
>>> +++ b/mysql-test/r/select.result 2011-01-06 12:21:00 +0000
>>> @@ -2204,10 +2204,10 @@ a a
>>> select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on
>>> t1.a>1;
>>> a a
>>> 2 1
>>> -3 1
>>> 2 2
>>> -3 2
>>> 2 3
>>> +3 1
>>> +3 2
>>> 3 3
>>> select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using
>>> ( a );
>>> a
>>>
>>> === modified file 'mysql-test/r/status.result'
>>> --- a/mysql-test/r/status.result 2009-06-10 08:58:36 +0000
>>> +++ b/mysql-test/r/status.result 2011-01-06 12:21:00 +0000
>>> @@ -149,7 +149,7 @@ a a
>>> 1 1
>>> SHOW SESSION STATUS LIKE 'Last_query_cost';
>>> Variable_name Value
>>> -Last_query_cost 4.805836
>>> +Last_query_cost 5.205836
>>> DROP TABLE t1;
>>> show status like 'com_show_status';
>>> Variable_name Value
>>>
>>> === modified file 'mysql-test/r/subselect.result'
>>> --- a/mysql-test/r/subselect.result 2010-11-08 10:55:43 +0000
>>> +++ b/mysql-test/r/subselect.result 2011-01-06 12:21:00 +0000
>>> @@ -1353,10 +1353,10 @@ a
>>> explain extended select * from t2 where t2.a in (select t1.a from t1,t3
>>> where t1.b=t3.a);
>>> id select_type table type possible_keys key key_len
>>> ref rows filtered Extra
>>> 1 PRIMARY t2 index NULL a 5 NULL 4 100.00
>>> Using where; Using index
>>> -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001
>>> 100.00 Using where; Using index
>>> -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3
>>> 100.00 Using where; Using index; Using join buffer
>>> +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3
>>> 100.00 Using index
>>> +2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a
>>> 1167 100.01 Using where; Using index
>>> Warnings:
>>> -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2`
>>> where<in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from
> `test`.`t1` join
>>> `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and
>>> (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
>>> +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2`
>>> where<in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from
> `test`.`t1` join
>>> `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and
>>> (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
>>> insert into t1 values (3,31);
>>> select * from t2 where t2.a in (select a from t1 where t1.b<> 30);
>>> a
>>>
>>> === modified file 'mysql-test/t/greedy_optimizer.test'
>>> --- a/mysql-test/t/greedy_optimizer.test 2009-01-16 10:45:17 +0000
>>> +++ b/mysql-test/t/greedy_optimizer.test 2011-01-06 12:21:00 +0000
>>> @@ -380,7 +380,145 @@ LEFT JOIN (
>>> ) ON t4.d = t1.d
>>> ;
>>>
>>> -SET optimizer_search_depth = DEFAULT;
>>> DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
>>>
>>> ---echo End of 5.0 tests
>>> +
>>> +--echo #
>>> +--echo # Bug #59326: Greedy optimizer produce stupid query execution plans.
>>> +--echo #
>>> +
>>> +CREATE TABLE t10(
>>> + K INT NOT NULL AUTO_INCREMENT,
>>> + I INT,
>>> + PRIMARY KEY(K)
>>> +);
>>> +INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
>>> +
>>> +CREATE TABLE t100 LIKE t10;
>>> +INSERT INTO t100(I)
>>> +SELECT X.I FROM t10 AS X,t10 AS Y;
>>> +
>>> +CREATE TABLE t10000 LIKE t10;
>>> +INSERT INTO t10000(I)
>>> +SELECT X.I FROM t100 AS X, t100 AS Y;
>>> +
>>> +## All crossproducts should be executed in order t10,t100,t10000
>>> +EXPLAIN SELECT * FROM t10,t100,t10000;
>>> +EXPLAIN SELECT * FROM t10,t10000,t100;
>>> +EXPLAIN SELECT * FROM t100,t10,t10000;
>>> +EXPLAIN SELECT * FROM t100,t10000,t10;
>>> +EXPLAIN SELECT * FROM t10000,t10,t100;
>>> +EXPLAIN SELECT * FROM t10000,t100,t10;
>>> +
>>> +
>>> +## Ordering between T100,T10000 EQ-joined T10 should
>>> +## be with smallest table joined first
>>> +EXPLAIN SELECT * FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t100,t10,t10000
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t100,t10000,t10
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10000,t10,t100
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10000,t100,t10
>>> +WHERE t100.K=t10.I
>>> + AND t10000.K=t10.K;
>>> +
>>> +## EQ_REF Should be executed before table scan(ALL)
>>> +## - Independent of #records in table being EQ_REF-joined
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> + AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> + AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t100.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t100.I;
>>> +
>>> +## EQ_REF& Cross join two instances of t10000 with t10:
>>> +## Always EQ_REF join first before producing cross product
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> + AND Y.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> + AND Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> + AND Y.I=X.K;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> + AND Y.K=t10.I;
>>> +
>>> +## Create indexes to test REF access
>>> +CREATE INDEX IX ON t10(I);
>>> +CREATE INDEX IX ON t100(I);
>>> +CREATE INDEX IX ON t10000(I);
>>> +
>>> +## EQ_REF Should be executed before 'REF'
>>> +## - Independent of #records in table being EQ_REF-joined
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> + AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> + AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t100.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> + AND t10000.K=t100.I;
>>> +
>>> +## EQ_REF& Cross join two instances of t10000 with t10:
>>> +## Always EQ_REF join first before producing cross product
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> + AND Y.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> + AND Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> + AND Y.I=X.K;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> + AND Y.K=t10.I;
>>> +
>>> +DROP TABLE t10,t100,t10000;
>>> +
>>> +SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
>>> +
>>> +--ECHO END OF 5.0 TESTS
>>>
>>> === modified file 'sql/sql_select.cc'
>>> --- a/sql/sql_select.cc 2010-12-28 23:47:05 +0000
>>> +++ b/sql/sql_select.cc 2011-01-06 12:21:00 +0000
>>> @@ -5377,6 +5377,8 @@ best_extension_by_limited_search(JOIN
>>> }
>>> }
>>>
>>> + current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
>>> +
>>> if ( (search_depth> 1)&& (remaining_tables&
> ~real_table_bit) )
>>> { /* Recursively expand the current partial plan */
>>> swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
>>> @@ -5395,7 +5397,6 @@ best_extension_by_limited_search(JOIN
>>> 'join' is either the best partial QEP with 'search_depth'
>>> relations,
>>> or the best complete QEP so far, whichever is smaller.
>>> */
>>> - current_read_time+= current_record_count / (double)
> TIME_FOR_COMPARE;
>>> if (join->sort_by_table&&
>>> join->sort_by_table !=
>>> join->positions[join->const_tables].table->table)
>>>
>>>
>>>
>>>
>>
>
>
