Diksha Diksha - 3 months ago 7
MySQL Question

Reduce time in nested mysql query

I have a nested MySQL query having relation in tables with over 500000 records in each. The query takes 60 seconds to fetch results and Indexing has been done in all tables.

Please suggest to reduce its execution time. Thanks in advance.

SELECT t1.col1,t1.col2
FROM table1 AS t1
WHERE t1.col2 IN
(
SELECT DISTINCT(t2.col1) FROM table2 AS t2 WHERE t2.col2 IN
(
SELECT t3.col1
FROM table3 AS t3
WHERE t3.col2 = '04' ORDER BY t3.col1 ASC
)
ORDER BY t2.col1 ASC
)

Answer

IN clause makes a full table scan. I believe you will have much better performance if you use inner join, like:

SELECT t1.col1,t1.col2
    FROM table1 AS t1
INNER JOIN table2 t2
    ON t1.col2=t2.col1
INNER JOIN table3 t3
    ON t2.col1=t3.col1
WHERE  t3.col2 = '04'
ORDER BY t3.col1 ASC,t2.col1 ASC
Comments