Very slow MySQL subquery
mysql
Solution
mysql does not optimize a subquery in the WHERE clause (edit: it re-runs the subquery for every row tested)
to convert to a JOIN, try something like
SELECT email FROM t1
LEFT JOIN t2 ON (t1.email = t2.email)
WHERE t2.email IS NULL
this should run very fast, a covering index query. The query optimizer should walk the email index of t1, check the email index of t2, and output those emails that are in t1 but not in t2.
Edit: I should add, mysql does optimize a subquery in the JOIN clause: it runs the subquery and puts the results into a "derived table" (temporary table without any indexes), and joins the derived table like any other. The syntax is a bit funny, each derived table must have an alias, ie `... JOIN (SELECT ...) AS derived ON ...`.
Problem
I have two tables that each contain about 500 customer data records. Each record in each of the tables has an email field. Sometimes the same email addresses exist on both tables, sometimes not. I want to retrieve every email address on table1 that doesn't exist on table2. The email field in each table is indexed. I'm doing the select with a sub query that is really slow, 10 to 20 seconds. ``` select email from t1 where email not in (select email from t2) ``` There's actually about 30K rows in each table, but I can knock it down to 500 each very quickly with an additional 'where' to filter by category. It's only when I add that subquery that it slows down dramatically. So, I am sure this can be faster, and I know a join should be much faster than the subquery, but can't figure out how to do that. I found a left outer join explanation here on SO, that looked like it should help, but got nowhere with it. Any help is appreciated.