List:Internals« Previous MessageNext Message »
From:Zhixuan Zhu Date:October 8 2009 2:56pm
Subject:RE: Question about subquery optimization
View as plain text  
Thanks very much.

One last question: When is 6.0 planed to release? Source and GA.

Thanks again,
Grace

-----Original Message-----
From: Roy.Lyseng@stripped [mailto:Roy.Lyseng@stripped] 
Sent: Thursday, October 08, 2009 1:57 AM
To: Zhixuan Zhu
Cc: Sergei Golubchik; internals@stripped
Subject: Re: Question about subquery optimization



Zhixuan Zhu wrote:
> Hi Sergei,
> 
> Thanks for the reply. I would like to know if most of the new optimizations have to
> operate on the index? If our engine doesn't support index and there's no index on the
> tables, will the following transformation take place and be reflected in the execution
> plan?
> 
>  Example:
>  SELECT * FROM t1 WHERE t1.key in (SELECT t2.key FROM t2)
>  ->
>  SELECT * FROM t1, t2(distinct t2.key) WHERE t1.key=(semi)t2.key;

The transformation into a semijoin is always done, if applicable. Join order 
selection and index analysis are performed after this transformation, and they 
are based on cost factors.

The "old" way of handling subqueries is also considered as a possible execution 
plan, so essentially the transformation creates a larger number of possible 
plans to choose from.

Thanks,
Roy
> 
> Thanks so much,
> Grace
> 
> 
> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@stripped] 
> Sent: Wednesday, October 07, 2009 3:12 PM
> To: Zhixuan Zhu
> Cc: internals@stripped
> Subject: Re: Question about subquery optimization
> 
> Hi, Zhixuan!
> 
> On Oct 07, Zhixuan Zhu wrote:
>> Hello there,
>>
>> We're developing a custom storage engine based on MySQL server 5.1.39.
>> Does this version optimize IN/EXIST sub queries with SEMI or ANTI joins?
>>
>> Example:
>> SELECT * FROM t1 WHERE t1.key in (SELECT t2.key FROM t2)
>> ->
>> SELECT * FROM t1, t2(distinct t2.key) WHERE t1.key=(semi)t2.key;
> 
> No, not in 5.1, afaik.
>  
>> If yes, is there any way to find out the optimized condition at the
>> Storage Engine interface (select_lex? join? where? having?)?
>> If not, what is the plan to deliver that sort of optimization? 
> 
> Check the 6.0 tree on launchpad, e.g.
> lp:~mysql/mysql-server/mysql-6.0-bugteam.
> 
> You can also find this presentation interesting:
> http://www.mysqlconf.com/mysql2008/public/schedule/detail/595
>  
> Regards / Mit vielen Grüßen,
> Sergei
> 
Thread
Question about subquery optimizationZhixuan Zhu7 Oct
  • Re: Question about subquery optimizationGuilhem Bichot7 Oct
  • Re: Question about subquery optimizationSergei Golubchik7 Oct
    • RE: Question about subquery optimizationZhixuan Zhu7 Oct
      • Re: Question about subquery optimizationRoy Lyseng8 Oct
        • RE: Question about subquery optimizationZhixuan Zhu8 Oct
          • Re: Question about subquery optimizationSergei Golubchik8 Oct