How I Cut an 8‑Second Slow Query to 0.7 Seconds with Two Optimizations

1. The Scene

The author received an alert about a slow SQL query that took 8 seconds. The query counts the number of unpublished SPUs for supplier ID 123456789 using multiple EXISTS and NOT EXISTS subqueries across four tables: spu, sku, mall_sku, and supplier_sku.

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 spu used a type index, while the other three tables used ref indexes, indicating that merely adding indexes would not solve the performance problem.

2. First Optimization

The author noticed the two EXISTS clauses and rewrote the query using a JOIN combined with GROUP BY. The rewritten SQL (note: some syntax errors in the original source are kept) is:

SELECT count(*) FROM (
    SELECT s2.spu_id
    FROM spu s1
    INNER JOIN 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 = ...
      )
    GROUP BY s2.spu_id
) a;

Because spu_id is indexed in the sku table, the GROUP BY operation is fast. After this change the query time dropped to about 2.5 seconds, a three‑fold improvement, but still not fast enough.

3. Second Optimization

The remaining NOT EXISTS was replaced with NOT IN, which can be more efficient when the subquery returns a small set. The final version is:

SELECT count(*) FROM (
    SELECT s2.spu_id
    FROM spu s1
    INNER JOIN 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 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 execution time fell to 0.7 seconds. A subsequent EXPLAIN revealed that the spu table now performed a full table scan, sku used an eq_ref index, and the other two tables used ref indexes. The author notes that more indexes do not always mean better performance; sometimes a full scan on one table can be beneficial when joining many tables, and each case must be tested.

The article concludes that SQL performance tuning is complex, often requiring multiple iterations and careful testing of different rewrite strategies.