300ms to 133ms by removing work the result never needed.
Not every optimization needs a new cache layer or architectural rewrite. Sometimes the database is simply doing more work than the caller asked for.
A frequently executed query multiplied rows through joins and then paid again to deduplicate them.
The final result was small, but the execution plan showed more than 25,000 rows flowing through an inner-join branch before a temporary table removed duplicates. That is a useful smell: the database is doing substantial relationship work that the caller may not actually need.
rows in the expensive join branch
reduce candidates before relationship work
same caller-visible result
EXPLAIN ANALYZE showed exactly where rows exploded.
The following fragment is generalized from the observed MySQL 8 plan. Internal table, column and index names are replaced, and row counts are rounded while preserving the execution-plan shape.
-> Table scan on <temporary>
(actual time≈300ms rows≈100)
-> Temporary table with deduplication
(actual time≈300ms rows≈100)
-> Nested loop inner join
(actual time≈0.3..271ms rows≈25k)
-> Filter:
((r.flagged = 1) and (r.archived_at is null))
(actual time≈0.2..67ms rows≈13k)
-> Intersect rows sorted by row ID
(actual time≈0.2..60ms rows≈13k)
-> Index range scan on r using idx_flaggedThe useful number is not simply “300ms.” It is the shape: roughly 13k candidate rows become roughly 25k joined rows, then a temporary structure throws almost all of that work away to return only about 100 rows.
Constrain existence without materializing every matching relationship row.
Where the caller only needs to know whether a related record exists, an existence check can be cheaper than joining every matching row and then using DISTINCT to repair the multiplication.
SELECT DISTINCT r.record_id
FROM records AS r
JOIN record_members AS rm
ON rm.record_id = r.record_id
WHERE r.flagged = 1
AND r.archived_at IS NULL
AND rm.archived_at IS NULL;SELECT r.record_id
FROM records AS r
WHERE r.flagged = 1
AND r.archived_at IS NULL
AND EXISTS (
SELECT 1
FROM record_members AS rm
WHERE rm.record_id = r.record_id
AND rm.archived_at IS NULL
);The SQL above illustrates the access-pattern change rather than reproducing the full proprietary query verbatim. The measured plan/timings are from the production investigation.
Measure the execution plan, then change the access pattern—not the business contract.
- Captured actual timing with EXPLAIN ANALYZE
- Identified the join producing most intermediate rows
- Checked whether the caller needed joined columns or only relationship existence
- Narrowed the candidate/relationship path earlier
- Retained the result shape expected by the application
- Re-ran the plan and measured the improvement
Less work, same contract.
This is based on a production MySQL query-optimization investigation. The measured timing is retained; the plan fragment is rounded and internal identifiers are generalized; identifying application context and the complete proprietary query are intentionally omitted.