title: Improve a large query with a subquery and join
body: <p>I have the following SQL query which takes 8s to run. How would I optimize the below statement?</p>
  
  <pre><code>SELECT
         p.id, territory_id, p.platform_id,
         GROUP_CONCAT(DISTINCT COALESCE(language_of_content_primary_id, base_language_id), ',', IFNULL(languages_of_content_additional, '')) languages,
         p.store_url, allows_free_streaming, requires_paid_subscription, group_concat(sd_retail_price) purchase
  FROM
      main_iteminstance i INNER JOIN main_territorypricing p ON p.item_id=i.id
  WHERE
      (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
      AND
      (master_id=%s OR tv_series_id IN (SELECT id FROM main_iteminstance WHERE master_id=%s))
  GROUP BY
      territory_id, platform_id''', (self.pk, self.pk))
  </code></pre>
  
  <p>Note that the following runs in 0.03s --</p>
  
  <pre><code>               WHERE
                 (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
                 AND
                 (master_id=33568)
  </code></pre>
  
  <p>But using the subquery adds the extra 8s --</p>
  
  <pre><code>               WHERE
                 (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
                 AND
                 (master_id=%s OR tv_series_id IN (SELECT id FROM main_iteminstance WHERE master_id=%s))
  </code></pre>
  
  <p>For whatever reason, it seems that when I broke this up into two queries it performed much better than one, here is what I now have --</p>
  
  <pre><code>tv_series_ids = cursor.execute('SELECT id FROM table WHERE master_id=%s)
  </code></pre>
  
  <p>And the new <code>WHERE</code> clause --</p>
  
  <pre><code>WHERE
  (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
  AND
  (master_id=%s OR tv_series_id IN %s)
  </code></pre>
  
  <p>Could someone please explain to me why pre-fetching the ids performs so much better than doing a sub-query?</p>
  
  <p>Here are the two execution plans --</p>
  
  <p><a href="https://i.stack.imgur.com/NFcJJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NFcJJ.png" alt="enter image description here"></a></p>