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
