Slow query after adding subquery

Question

Running the following works great:

SELECT email FROM User WHERE empNum IN (126,513,74)

However, this takes a very long to reply (no errors) using:

SELECT email FROM table1 WHERE empNum IN (
   SELECT empNum FROM table2 WHERE accomp = 'onhold' GROUP BY empNum
)

What is causing this?

Answer 1

How about that one?

SELECT DISTINCT table1.email
FROM table1
INNER JOIN table2 USING(empNum)
WHERE table2.accomp = 'onhold'

You should probably make an index on table2.accomp if you use that query often enough:

CREATE INDEX accomp ON table2 (accomp);

or maybe

CREATE INDEX accomp ON table2 (empNum,accomp);

To perform some crude (but deciding) benchmarks:

1. log in mysql console

2. clear the query cache(*):

   RESET QUERY CACHE;
   

3. run the slow query and write down the timing

4. create an index
5. clear the query cache
6. run the slow query and write down the timing
7. drop the index
8. create the other index
9. clear the cache
10. run the slow query one more time
11. compare the timings and keep the best index (by droping the current one and creating the correct one if necessary)

_{(*) You will need the relevant privileges to run that command}

Answer 2

I think the join statement you need is:

SELECT email FROM table1
INNER JOIN table2
ON table1.empNum=table2.empNum
AND table2.accomp = 'onhold'