From: Dan Nelson Date: February 25 2010 7:48am Subject: Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables List-Archive: http://lists.mysql.com/mysql/220797 Message-Id: <20100225074834.GQ70798@dan.emsphone.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii In the last episode (Feb 24), Perrin Harkins said: > On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang 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