使用子查询和连接改进大型查询

I have the following SQL query which takes 8s to run. How would I optimize the below statement?

我有以下SQL查询,需要8秒才能运行。我如何优化以下声明?

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))

Note that the following runs in 0.03s --

请注意,以下运行时间为0.03秒 -

               WHERE
               (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
               AND
               (master_id=33568)

But using the subquery adds the extra 8s --

但是使用子查询会增加额外的8s -

               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))

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 --

无论出于何种原因,似乎当我把它分成两个查询时它表现得比一个好得多,这就是我现在所拥有的 -

tv_series_ids = cursor.execute('SELECT id FROM table WHERE master_id=%s)

And the new WHERE clause --

和新的WHERE子句 -

WHERE
(doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)
AND
(master_id=%s OR tv_series_id IN %s)

Could someone please explain to me why pre-fetching the ids performs so much better than doing a sub-query?

有人可以向我解释为什么预先获取ID比执行子查询要好得多吗?

Here are the two execution plans --

这是两个执行计划 -

1 个解决方案

#1

You can move the second

你可以移动第二个

AND
(master_id=%s OR tv_series_id IN (SELECT id FROM main_iteminstance WHERE master_id=%s))

To FROM section with JOIN like this

像这样的JOIN到FROM部分

FROM
    main_iteminstance i INNER JOIN main_territorypricing p ON p.item_id=i.id
    INNER JOIN main_iteminstance i2 ON (i.tv_series_id = i2.id OR i.master_id=%s)
WHERE
    (doesnt_exist_on_store_anymore=0 AND p.store_url IS NOT NULL)

#1