List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:February 25 2010 11:19am
Subject:Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
View as plain text  
On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson <dnelson@stripped> wrote:

> IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely
> efficient); it's subqueries in general that are killers.
>

If the dependent subquery is nothing but index lookups, it's still blazingly
fast, though :)

I just optimized one like that:

select nid from search_total left join search_index on search_total.nid =
search_index.nid where search_index.nid is null;

got optimized to

select nid from search_total where nid not in (select nid from
search_index);

This shaved 3 seconds off a 10-second query (field is indexed in both
tables, plenty of room in the key cache). Now, if there was a way to tell
MySQL that the subquery isn't dependant, it should turn into a near-zero
query.

I also tested a *not exists* construct, which turned out to be about a
hundreth of a second slower.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Thread
EXPLAIN says DEPENDENT SUBQUERY despite no free variablesYang Zhang24 Feb
  • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesPerrin Harkins24 Feb
    • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesDan Nelson25 Feb
      • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesJohan De Meersman25 Feb
      • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesPerrin Harkins25 Feb
  • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesBaron Schwartz25 Feb