Re: sub-query optimization

On 14 Feb 2003, Brad Hilton wrote:

> I am hoping someone can help explain why modifying the following query
> can effect such a huge change in speed. The query is:
>
> select * from articles
> where exists
> ( select 1 from article_categories
> where
> article_categories.article_id = articles.id and
> article_categories.category_id is null
> )
>
> The original query was much more complex, but I have trimmed it down to
> highlight the problem. The query above also manifests the problem. OK,
> the above query (with 100,000 records in the articles table) takes 1292
> msec (see output below). If I modify the query slightly:
>
> --------
> select 1 from article_categories
> -->
> select 1 from articles, article_categories
> ---------

After putting the latter in the subselect do you actually have the same
query? In one case articles is an outer reference for the particular
row. In the other it's a reference to the copy of articles in the
subselect. Wouldn't that give the wrong results when you have any matches
(since there'd exist a row from the subselect even if it wasn't the one
matching the outer query)?