Search code examples
mysqlsqlquery-optimization

MySQL, query with "NOT IN ( SELECT ... )" very slow

Here is the query runs over 10 minutes. 

SELECT *
FROM tableA
WHERE name NOT IN(
SELECT a.name
FROM tableA a
INNER JOIN tableB b ON a.code = b.code 
INNER JOIN tableC c ON c.number = b.number 
INNER JOIN tableD d ON d.code = b.code 
INNER JOIN tableE e ON e.content= d.content)

Is there alternative query runs faster or any solutions? Thanks a lot.

Solution

  • try not exists

    SELECT *
FROM tableA t1
WHERE not exists (
SELECT 1
from tableB b
INNER JOIN tableC c ON c.number = b.number 
INNER JOIN tableD d ON d.code = b.code 
INNER JOIN tableE e ON e.content= d.content
where t1.code = b.code

)