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
>
