why using IN (or NOT IN) clause in a query makes it really slow

Question

I have a query:

SELECT DISTINCT field1 FROM table1 WHERE field2 = something

(table1 contains 1 million records, execution time:0.106sec, returns: 20 records)

Another query

SELECT DISTINCT similarField1 FROM table2 WHERE similarField2 = somethingElse

(table2 contains half million records, execution time:0.078sec, returns: 20 records)

Now if I run a query, by combining above both:

SELECT DISTINCT field1 FROM table1 WHERE field2 = something AND field1 NOT IN (SELECT DISTINCT similarField1 FROM table2 WHERE similarField2 = somethingElse)

It does't give result even running for 10mins. Why it has became dramatically slow, and what could be a potential solution.

edit: I am using MySQL with dbvisualizer 6.5

It is slow because MySQL. You may try to `LEFT JOIN` the two selects and return only those rows there there is no match. (leftjoinedquery.field1 is null). — GolezTrol, Feb 09 '15 at 10:15
I agree with GolezTrol. If its possible, you should join the tables. — Charlie Vieillard, Feb 09 '15 at 10:23
thanks Golez! I tried `SELECT DISTINCT field1 FROM table1 LEFT OUTER JOIN table2 ON table1.field1 = table2.similarField1 WHERE table1.field2 = something AND table2.similarField2 = somethingElse` surely it increased the speed (took 35sec), but not giving the result I wanted — Anil Bhaskar, Feb 09 '15 at 10:28

score 1 · Accepted Answer · edited May 23 '17 at 11:43

You don't need to use DISTINCT on the sub-query. Try to use NOT EXISTS which probably is more efficient in SQL-Server:

SELECT DISTINCT field1 
FROM   table1 
WHERE  field2 = @something 
AND NOT EXISTS
(
    SELECT 1 FROM table2
    WHERE table2.similarfield1 = table1.field2 
     AND  table2.similarfield2 = @somethingelse
)

Edit: Since you have updated the tags, i'm not sure if this is more efficient in MySql. However, i'd prefer NOT EXISTS anyway since it also works with NULL values(if you use IS NULL) and is easier to read and to maintain.

score 0 · Answer 2 · edited Feb 09 '15 at 11:42

0

my query and advice are similar to @TimSchmelter.

In fact you should not use distinct at all. First you should remove distinct and check if you are getting duplicate records you have just ask part of your problem.Table design are not clear.

You should post your complete problem and query here without any hesitant. Also don't forget to apply index on feild2, feild1,similarField1,similarField2.

SELECT DISTINCT field1
FROM table1 tbl1
WHERE field2 = something
    AND NOT EXISTS (
        SELECT similarField1
        FROM table2 tbl2
        WHERE tbl1.field1 = tbl2.similarField1
            AND similarField2 = somethingElse
        )

edited Feb 09 '15 at 11:42

Anil Bhaskar

3,718
4
33
51

answered Feb 09 '15 at 10:29

KumarHarsh

5,046
1
18
22

thanks. Table design is simple, nothing else is to be mentioned... i guess. Tried your solution but no help, it keeps running even after 10 mins. I guess left outer join might help. – Anil Bhaskar Feb 09 '15 at 11:24

why using IN (or NOT IN) clause in a query makes it really slow

2 Answers2