SQL Query taking a long time to run

Question

I am trying to run this SQL query:

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)

But for some reason, its taking 14.8126 seconds to return any results.

When testing, its returning 1 row and i cannot work out why its so slow. I believe its probably something to do with the joins but im hoping someone can help and point me in the right direction please?

I apologise if i have missed out any info, so please excuse my ignorance.

Answer 1

Aside from the other Answers, you need these indexes:

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

It might help to combine the LEFT JOIN and the NOT IN:

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