Reduce time in nested mysql query

Question

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
    )

You should only order your final result set. Try profiling the query to see what is causing the long execution time; It could well be the DISTINCT clause you have as they can often cause delay. In addition you could try using a JOIN instead of WHERE ... IN as that can often be faster. — Martin
– Martin, Commented Aug 23, 2016 at 12:53
seconding @MartinParkin, you should easily be able to rewrite the query to use joins instead of where...in — Rahul Sharma
– Rahul Sharma, Commented Aug 23, 2016 at 12:54

Martin · Accepted Answer · 2016-08-23 12:57:23Z

3

My earlier comment was:

You should only order your final result set. Try profiling the query to see what is causing the long execution time; It could well be the DISTINCT clause you have as they can often cause delay. In addition you could try using a JOIN instead of WHERE ... IN as that can often be faster.

Not tested code but further to my comment earlier, the same query using JOINs would look similar to:

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

I would expect such a query to perform significantly faster than using WHERE ... IN.

answered Aug 23, 2016 at 12:57

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

apomene · Accepted Answer · 2016-08-23 12:54:45Z

2

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

answered Aug 23, 2016 at 12:54

apomene

14.4k10 gold badges51 silver badges76 bronze badges

1 Comment

deniskoronets Over a year ago

Nested select queries are always slow and heavy because they store intermediate data in memory. Switch your nested queries to a join (as it showed by @apomene), it will prevent memory load and improve execution speed.

Manish · Accepted Answer · 2016-08-23 13:03:00Z

0

You can also use EXISTS condition like

Select t1.col1,t1.col2 FROM table1 AS t1 WHERE EXISTS (
 Select t2.col1 FROM table2 as t2 where EXISTS (
  Select t3.col1 FROM table3 AS t3 where t3.col2 = '04' and t2.col2=t3.col1
 ) and t1.col2=t2.col1)

answered Aug 23, 2016 at 13:03

Manish

3,7051 gold badge23 silver badges24 bronze badges

Collectives™ on Stack Overflow

Reduce time in nested mysql query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related