How I Cut an 8‑Second SQL Query to 0.7 Seconds: Step‑by‑Step Optimization

Introduction

The author recently tackled several slow‑query problems at work and decided to share a practical SQL performance‑tuning case that reduced execution time from 8 seconds to 0.7 seconds.

Case Scene

An alert email reported a slow query. The original statement attempted to count the number of unpublished SPUs for a specific supplier (ID 123456789) and took about 8 seconds.

SELECT count(*)
FROM spu s1
WHERE EXISTS (
    SELECT *
    FROM sku s2
    INNER JOIN mall_sku s3 ON s3.sku_id = s2.id
    WHERE s2.spu_id = s1.id
      AND s2.status = 1
      AND NOT EXISTS (
          SELECT *
          FROM supplier_sku s4
          WHERE s4.mall_sku_id = s3.id
            AND s4.supplier_id = 123456789
            AND s4.status = 1
      )
);

Running EXPLAIN showed that the spu table used a type index while the other tables used ref indexes, indicating that merely adding indexes would not solve the problem.

First Optimization

The query relied heavily on two EXISTS clauses. By rewriting the logic with a JOIN and a GROUP BY, the author eliminated unnecessary sub‑queries.

SELECT count(*)
FROM (
    SELECT s2.spu_id
    FROM spu s1
    INNER JOIN sku s2 ON s2.spu_id = s1.id AND s2.status = 1
    INNER JOIN mall_sku s3 ON s3.sku_id = s2.id
    WHERE NOT EXISTS (
        SELECT *
        FROM supplier_sku s4
        WHERE s4.mall_sku_id = s3.id
          AND s4.supplier_id = ...
    )
    GROUP BY s2.spu_id
) a;

Because spu_id is indexed in the sku table, the GROUP BY runs quickly. Execution time dropped to about 2.5 seconds—a three‑fold improvement—but further speed was still desired.

Second Optimization

The remaining NOT EXISTS was changed to NOT IN, which is more efficient when the sub‑query returns a relatively small set.

SELECT count(*)
FROM (
    SELECT s2.spu_id
    FROM spu s1
    INNER JOIN sku s2 ON s2.spu_id = s1.id AND s2.status = 1
    INNER JOIN mall_sku s3 ON s3.sku_id = s2.id
    WHERE s3.id NOT IN (
        SELECT s4.mall_sku_id
        FROM supplier_sku s4
        WHERE s4.mall_sku_id = s3.id
          AND s4.supplier_id = ...
    )
    GROUP BY s2.spu_id
) a;

After this change the query ran in roughly 0.7 seconds. A subsequent EXPLAIN revealed that spu now performed a full table scan, sku used an eq_ref index, and the other tables kept ref indexes. The case demonstrates that more indexes do not always equal better performance; sometimes a full scan on a small table can be advantageous.

Takeaways

SQL tuning is complex and often requires multiple iterations. Key lessons include:

Analyze execution plans to understand how each table is accessed.

Replace costly EXISTS/NOT EXISTS patterns with joins or NOT IN when appropriate.

Index selection must consider data distribution; adding indexes indiscriminately may not help.

Always benchmark after each change, as the optimal plan can differ based on table sizes and join order.