List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 25 2010 7:48am
Subject:Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
View as plain text  
In the last episode (Feb 24), Perrin Harkins said:
> On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang <yanghatespam@stripped> wrote:
> > Any ideas on how to optimize this by convincing mysql to see the
> > independence use a const join?
> 
> http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
> 
> You need to rewrite as a join or use a FROM subquery.  You should pretty
> much always avoid using IN/NOT IN.

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.  Current MySQL
versions almost always treat subqueries as dependent, even ones that are
obviously not.  The 6.0 branch was a significant improvement, but that
branch has been killed off, and there's no indication of the fixes being
backported to 5.x .

-- 
	Dan Nelson
	dnelson@stripped
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