如何利用InnoDB引擎减少查询时间

Question

我正在尝试优化这个查询。现在需要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
)

Answer 1

如果在子查询中使用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
)

不过，这个查询仍然使用self进行优化。

Answer 2

若要增加速度，请将此复合索引添加到g

INDEX(olayid, durum, gonderilecegitarih)

请提供SHOW CREATE TABLE 800_emsenaryolar_emgidenbulten；我想验证您在adresid上也有一个索引。