CODEWITHSUNDEEP
mysql
user1145643
user1145643

Reputation: 898

MySQL Using IN() SubQuery Creates Much Longer Execution Time

What is the difference between the following? The first query takes 0.00 to execute, the second takes 0.00 to execute, the third takes 0.71 to execute. For some reason when I put the two queries together in example 3 it takes much longer to execute. Table traffic has an index on shortcode and ip, table redirect has an index on campaign and pubid.

Is there another type of index that could speed this scenario up?

Query 1: (0.00 Execution)

SELECT * FROM traffic 
    WHERE shortcode IN ('CODE1')
    GROUP BY ip

Query 2: (0.00 Execution)

SELECT shortcode FROM redirect WHERE campaign = '385' AND pubid = '35'

Query 3: (0.71 Execution)

SELECT * FROM traffic 
    WHERE shortcode IN 
        (SELECT shortcode FROM redirect WHERE campaign = '385' AND pubid = '35') 
    GROUP BY ip

Upvotes: 1

Views: 84

Answers (1)

Rick James
Rick James

Reputation: 142298

In older versions of MySQL, the IN ( SELECT ... ) construct was very poorly optimized. For every row in traffic it would re-execute the subquery. What version of MySQL are you using? The simple and efficient solution was to turn it into a JOIN.

SELECT  t.*
    FROM  traffic AS t
    JOIN  redirect AS r USING(shortcode)
    WHERE  campaign = '385'
      AND  pubid = '35'
    GROUP BY  ip

You also need INDEX(campaign, pubid, shortcode) .

There is a "bug" in the either query -- You are asking for all columns, but grouping by only ip. If the rest of the columns are not really dependent on ip.

Upvotes: 3

Related Questions