CODEWITHSUNDEEP
mysqlsql
hvs
hvs

Reputation: 536

Nested Subquery Too Slow

The following mysql query which uses double nested subqueries is too slow

SELECT t.name
FROM creatives AS c
INNER JOIN term_relationships AS tr ON tr.creative_id=c.creative_id
INNER JOIN terms AS t ON t.term_id=tr.term_id
WHERE c.creative_id IN
   (SELECT creative_id
    FROM term_relationships
    WHERE term_id IN
       (SELECT offer_term_id
        FROM offer_urls))
AND t.taxonomy LIKE 'ad_network';

The inner query 

SELECT creative_id
    FROM term_relationships
    WHERE term_id IN
       (SELECT offer_term_id
        FROM offer_urls)

is extremely fast (0.04 seconds)

but the full query doesn't deliver the results. I gave up after waiting for about 5 minutes.

Any way to optimize this?

Upvotes: 0

Views: 64

Answers (2)

Gab
Gab

Reputation: 3520

Try:

SELECT t.name
FROM creatives AS c
JOIN term_relationships AS tr ON tr.creative_id=c.creative_id
JOIN terms AS t ON t.term_id=tr.term_id
JOIN term_relationships tr2 ON tr2.creative_id = c.creative_id
JOIN offer_urls ou ON ou.offer_term_id = tr2.term_id
WHERE t.taxonomy LIKE 'ad_network';

This version written only with JOINs will be much quicker.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Try rewriting this using exists:

SELECT t.name
FROM terms t JOIN
     term_relationships tr
     ON t.term_id = tr.term_id JOIN
     creatives c
     ON tr.creative_id = c.creative_id
WHERE EXISTS (SELECT 1
              FROM term_relationships tr2 JOIN
                   offer_urls ou
                   ON tr.term_id = ou.offer_term_id
              WHERE c.creative_id = tr2.creative_id
             ) AND
     t.taxonomy LIKE 'ad_network';

Then for this you want the following indexes:

  • terms(taxonomy, term_id)
  • offer_urls(offer_term_id)
  • term_relationships(creative_id, term_id);
  • creatives(creative_id)

You may have already defined some of these using primary key definitions.

Upvotes: 0

Related Questions