List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:January 13 2011 7:25pm
Subject:Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326
View as plain text  
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.
> 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. 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.

> 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 those QEPs are chosen even for Roy's test.

My opinion is that the performance regression with join cache should be 
reported as another bug.

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


Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske6 Jan
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Evgeny Potemkin13 Jan
    • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske13 Jan
    • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske13 Jan
      • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Evgeny Potemkin14 Jan