mysql - 如何使用InnoDB引擎减少此查询的查询时间

我正在尝试优化此查询。现在需要28秒。

AS曾经在我的查询中丢失。添加后，查询时间减少了20％

SELECT
    g.id,
    g.adresid,
    g.senaryoid,
    g.olayid,
    g.gonderilecegitarih
FROM
    (
        SELECT
            adresid
        FROM
            expose2.800_emsenaryolar_emgidenbulten
        WHERE
            olayid = '3320'
    ) AS s
RIGHT JOIN expose2.800_emsenaryolar_emgidenbulten AS g ON s.adresid = g.adresid
WHERE
    s.adresid IS NULL
AND g.olayid = '2784'
AND g.durum = '1'
AND g.gonderilecegitarih < DATE_SUB(
    '2015-05-13 15:40:15',
    INTERVAL 1 DAY
)

最佳答案

如果您在子查询中使用s.adresid IS NULL条件，它将更快地连接，然后更多行...

SELECT
    g.id,
    g.adresid,
    g.senaryoid,
    g.olayid,
    g.gonderilecegitarih
FROM (
        SELECT adresid FROM expose2.800_emsenaryolar_emgidenbulten WHERE olayid = '3320' and  s.adresid IS NULL
    ) AS s
RIGHT JOIN expose2.800_emsenaryolar_emgidenbulten AS g ON s.adresid = g.adresid

AND g.olayid = '2784'
AND g.durum = '1'
AND g.gonderilecegitarih < DATE_SUB(
    '2015-05-13 15:40:15',
    INTERVAL 1 DAY
)

该查询仍然使用自我联接进行了优化。