How I Cut an 8‑Second Query to 0.7 Seconds: Real‑World SQL Optimization Steps

Introduction

Recently I optimized several slow‑query SQL statements at work and gathered practical SQL tuning experience.

After two rounds of optimization, a query that originally took 8s was reduced to 0.7s. This article shares the step‑by‑step process.

1. The Incident

I received an alert about a slow query. The original SQL was:

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
    )
)

This query counts the number of unpublished SPUs for supplier 123456789 and took 8s to run.

Using EXPLAIN I found that the spu table used a type index, while sku, mall_sku, and supplier_sku used ref indexes.

2. First Optimization

The original query relied heavily on two EXISTS clauses. I rewrote it using JOIN and GROUP BY:

SELECT count(*)
FROM (
  SELECT s2.spu_id
  FROM spu s1
  INNER JOIN sku s2 ON s2.spu_id = s1.id
  INNER JOIN mall_sku s3 ON s3.sku_id = s2.id
  WHERE 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 sku, the GROUP BY performed well. Execution time dropped to 2.5s, a three‑fold improvement.

3. Second Optimization

The remaining NOT EXISTS was changed to NOT IN, which is faster when the subquery returns a small set:

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

After this change the query ran in 0.7s. An EXPLAIN showed a full table scan on spu, eq_ref on sku, and ref on the other tables. This demonstrates that more indexes do not always mean better performance; sometimes a full scan can be optimal.

Conclusion

SQL tuning is complex and often requires multiple iterations, careful index analysis, and testing different rewrite strategies to achieve the desired performance.