List:Internals« Previous MessageNext Message »
From:Zhixuan Zhu Date:October 7 2009 8:35pm
Subject:RE: Question about subquery optimization
View as plain text  
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
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