Optimizing NOT EXISTS Queries with LEFT JOIN in MySQL: A Performance Comparison

In many business scenarios developers use NOT EXISTS to ensure that returned rows are not present in a specific set, but they often encounter significant performance degradation and rumors that NOT EXISTS does not use indexes.

Below is the original query that suffered from slow execution (over 29 seconds):

SELECT count(1)
FROM t_monitor m
WHERE NOT exists (
    SELECT 1
    FROM t_alarm_realtime AS a
    WHERE a.resource_id = m.resource_id
      AND a.resource_type = m.resource_type
      AND a.monitor_name = m.monitor_name
);

The query was rewritten using a LEFT JOIN and an IS NULL filter, resulting in a much faster execution (about 1.2 seconds):

SELECT count(1)
FROM t_monitor m
LEFT JOIN t_alarm_realtime AS a
  ON a.resource_id = m.resource_id
  AND a.resource_type = m.resource_type
  AND a.monitor_name = m.monitor_name
WHERE a.resource_id IS NULL;

Optimization effect: the execution time dropped from more than 29 seconds to 1.2 seconds, a 25‑fold improvement.

Does NOT EXISTS really avoid indexes? Execution plans for both statements show that indexes are used on the involved tables. The difference is that the NOT EXISTS version employs a DEPENDENT SUBQUERY while the LEFT JOIN version uses a straightforward table join.

Profiling with MySQL reveals that the NOT EXISTS query spends most of its time in the executing and Sending data phases, whereas the LEFT JOIN query mainly consumes time in Sending data (1.2 s). Assuming each execute+Sending data pair takes ~50 µs, the NOT EXISTS query would need roughly 578 436 such pairs for the outer table size, resulting in ~28.9 s of processing.

Therefore, the performance of NOT EXISTS heavily depends on the size of the outer result set (N). When N is small (e.g., 10 rows), the query can finish within a few milliseconds, but with large N (millions of rows) it becomes extremely slow and can exhaust I/O and CPU resources.

Another issue discovered during optimization was a data‑type mismatch: the resource_id column was defined as VARCHAR in one table and BIGINT in the other, preventing the subquery from using the index efficiently. Similar mismatches in real‑world systems (e.g., order numbers stored as strings in one table and integers in another) can cause severe performance problems.

Overall, rewriting NOT EXISTS as a LEFT JOIN … IS NULL when appropriate, ensuring consistent column types, and verifying index usage are key steps to achieve optimal query performance.

Execution plan for NOT EXISTS:

Execution plan for LEFT JOIN:

Profiling screenshot for NOT EXISTS:

Profiling screenshot for LEFT JOIN: