Optimizing a MySQL NOT IN( query

Question

I am trying to optimize this MySQL query. I want to get a count of the number of customers that do not have an appointment prior to the current appointment being looked at. In other words, if they have an appointment (which is what the NOT IN( subquery is checking for), then exclude them.

However, this query is absolutely killing performance. I know that MySQL is not very good with NOT IN( queries, but I am not sure on the best way to go about optimizing this query. It takes anywhere from 15 to 30 seconds to run. I have created indexes on CustNo, AptStatus, and AptNum.

SELECT
        COUNT(*)             AS NumOfCustomersWithPriorAppointment,
FROM 
        transaction_log AS tl
LEFT JOIN
        appointment AS a
ON
        a.AptNum = tl.AptNum
INNER JOIN
        customer AS c
ON
        c.CustNo = tl.CustNo
WHERE
        a.AptStatus  IN (2)
AND     a.CustNo NOT IN
        (
                SELECT
                        a2.CustNo
                FROM
                        appointment a2
                WHERE
                        a2.AptDateTime < a.AptDateTime)


AND     a.AptDateTime > BEGIN_QUERY_DATE
AND     a.AptDateTime < END_QUERY_DATE

Thank you in advance.

Please provide `SHOW CREATE TABLE` statements for each of the tables in your query. — Willem Renzema, Oct 15 '17 at 03:32
Is `BEGIN_QUERY_DATE` a constant? Or another column? Rewrite the query using `LEFT JOIN .. IS NULL`; tell us if it is faster. — Rick James, Oct 15 '17 at 04:01

score 1 · Accepted Answer · answered Oct 15 '17 at 04:53

Try the following:

SELECT
        COUNT(*)             AS NumOfCustomersWithPriorAppointment,
FROM 
        transaction_log AS tl
INNER JOIN
        appointment AS a
ON
        a.AptNum = tl.AptNum
LEFT OUTER JOIN appointment AS earlier_a
        ON earlier_a.CustNo = a.CustNo
        AND earlier_a.AptDateTime < a.AptDateTime
INNER JOIN
        customer AS c
ON
        c.CustNo = tl.CustNo
WHERE
        a.AptStatus  IN (2)
AND     earlier_a.AptNum IS NULL


AND     a.AptDateTime > BEGIN_QUERY_DATE
AND     a.AptDateTime < END_QUERY_DATE

This will benefit from a composite index on (CustNo,AptDateTime). Make it unique if that fits your business model (logically it seems like it should, but practically it may not, depending on how you handle conflicts in your application.)

Provide SHOW CREATE TABLE statements for all tables if this does not create a sufficient performance improvement.

This query nailed it exactly. Thank you so much for your help and suggestions! — 3C41DC, Oct 16 '17 at 14:56

Optimizing a MySQL NOT IN( query

1 Answers1