mysql - SQL 查询需要很长时间才能运行

Question

我正在尝试运行此 SQL 查询:

SELECT 
        a.ticketnumber 
    FROM 
        ticket_updates a 
    LEFT JOIN 
        ticket_updates b 
    ON 
        b.ticketnumber = a.sequence AND b.type = 'reminder_complete' 
    WHERE 
        b.ticketnumber IS NULL AND 
        (a.type = 'reminder' OR a.type = 'reminder_high') AND 
        (a.for_agent = '' OR a.for_agent = '2') AND 
        a.notes <= '2018-05-10 23:00:00' AND 
        a.ticketnumber NOT IN (
    SELECT ticketnumber 
                                FROM ticket_updates 
                                WHERE 
                                type = 'reminder_complete' AND 
                                ticketnumber = a.ticketnumber)

但由于某种原因，返回任何结果需要 14.8126 秒。

测试时，它返回 1 行，我无法弄清楚为什么它这么慢。我相信这可能与连接有关，但我希望有人可以帮助并指出我正确的方向？

如果我错过了任何信息，请原谅我的无知。

Answer 1

除了其他答案之外，您还需要这些索引:

a:  (type, for_agent, notes, ticket_number)
a:  (for_agent, type, notes, ticket_number)
ticket_updates:  (type, ticket_number)

将LEFT JOIN和NOT IN结合起来可能有帮助:

AND NOT EXISTS 
   ( SELECT 1 
       FROM ticket_updates b
      WHERE b.ticketnumber IN ( a.sequence, a.ticketnumber )
        AND b.type = 'reminder_complete' )
   )