From: Roy Lyseng
Date: August 31 2010 9:30am
Subject: Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3478)
 Bug#51070
List-Archive: http://lists.mysql.com/commits/117190
Message-Id: <4C7CCB9B.6040604@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>
>> Actually I think most of this code should be removed and replaced with
>> semijoin, flattening, or materialization because they are all superior.
>
> yes, but it seems that at least semijoin and materialization cannot be applied
> in certain cases _in_the_current_state_of_code_.
> For example, materialization doesn't yet work with partial matches, fixing this
> is todo: https://intranet.mysql.com/worklog/Server-BackLog/?tid=3830
> also described in "1.1 Subquery engine for hash semi-joins"
> of
> https://intranet.mysql.com/worklog/Server-Sprint/?tid=1110
>
> I don't know whether there is a theoretical impossibility to replace all
> IN->EXISTS with the new optimizer features (assuming we would implement all the
> missing parts like WL#3830).

Maybe if we extend the semijoin model, but that is a long way to go. I also 
prefer semijoin materialization to "plain" materialization (because one may do 
cost-based choices), but semijoin algorithms cannot handle e.g. aggregated 
subqueries.

Thanks,
Roy