On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson <dnel...@allantgroup.com> 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
