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;
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
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Principal Software Engineer/Server Architect
/_/ /_/\_, /___/\___\_\___/ Sun Microsystems GmbH, HRB München 161028
<___/ Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring
