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.
FROM table1 AS t1
WHERE t1.col2 IN
SELECT DISTINCT(t2.col1) FROM table2 AS t2 WHERE t2.col2 IN
FROM table3 AS t3
WHERE t3.col2 = '04' ORDER BY t3.col1 ASC
ORDER BY t2.col1 ASC
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