To be clear from the start: this isn't a production API — it's a back-office report finance runs once a month. Over five years it had drifted from about 1 second to 4+, and two or three people clicking at the same time meant spinner city.
The data isn't huge: 120,000 rows in orders, 400,000+ in order_items, 8,000+ in customers. Three tables joined, filtered by month and customer, sorted by amount, top 200 rows.
The First Step Is Still EXPLAIN
We untangled our 2016 slow query with EXPLAIN, and this time was no different: paste the statement in and read the plan.
```
EXPLAIN SELECT ... FROM orders o JOIN order_items i ON ... JOIN customers c ON ... WHERE o.month = '2025-08' AND c.region = 'North China' ORDER BY o.amount DESC LIMIT 200
```
Three rows of output, all the problems in plain sight:
- The customers row showed type ALL, 8,000+ rows, no index used — there was no index on region at all.
- The optimizer had picked order_items as the driving table — 400,000+ rows in the outermost loop, dragging the other two behind it.
- The last row's Extra read "Using filesort; Using temporary." The sort couldn't ride an index, so MySQL built a temp table to do it.
That's where four-plus seconds came from: a full scan of customers, the biggest table driving the join, and the results hauled off to a separate sort.
1. Build Indexes, but Build the Right Ones
The first instinct was to index region. We did — down from 4+ seconds to just over 3. Still slow.
What it really needed were composite indexes, their column order dictated by how the query uses them:
- On customers: (region, id). region filters, id is the JOIN key; keeping it second trims the number of row lookups.
- On orders: the old single-column month index became (month, customer_id, amount). month filters, customer_id joins, amount sorts — in that order, the composite covers WHERE, JOIN, and ORDER BY in one sweep.
- order_items already had an (order_id) index — good enough, left alone.
The order of operations matters: first work out "in what order does this SQL find data," then decide the index column order. Do it the other way around — a pile of single-column indexes — and MySQL will usually pick just one; the rest waste space.
EXPLAIN after the change: the customers row went from ALL to ref, and rows dropped from 8,000+ to around 600.
2. Stop Letting the Optimizer Guess the Driving Table
The 400,000-row table getting picked as driver was most likely stale statistics. ANALYZE TABLE helped, but the month-end data load knocked it off again.
In the end we used STRAIGHT_JOIN to pin the join order: customers first, filtered by region down to a few hundred rows; then use those ids against orders via (month, customer_id); the detail rows come last. Small result set up front, and every step after it stays small.
This has a cost: if the data distribution shifts, the pinned order may stop being optimal. A comment notes "order pinned assuming North China is about 7% of the data," and a switch remains to fall back to a plain JOIN.
3. Killing the Filesort
ORDER BY o.amount DESC isn't the same column as the WHERE month filter, so MySQL sorted separately.
After folding amount into the last position of the orders composite index, amount became ordered within (month, customer_id, amount). But there was still a snag: customer_id was a range condition — an IN list — and per the leftmost-prefix rule, columns after a range can't drive the sort. Filesort remained.
The fix was a rewrite: drop the outer ORDER BY and instead fetch the top N rows per customer, merging and sorting in the application layer. Each per-customer query uses the full (month, customer_id, amount) index, so sorting happens inside the index itself. Merging 200 rows in the application costs almost nothing.
In EXPLAIN, both "Using filesort" and "Using temporary" disappeared.
The Detours Along the Way
It wasn't a straight road. We tried a single-column (amount) index on orders to speed up sorting; writes slowed 15%, queries didn't improve, and the optimizer never picked it — rolled back and dropped. We also tried pushing LIMIT 200 into a subquery; the row counts became unpredictable, the report numbers went wrong, and finance noticed the same day. Both are recorded in an internal doc titled "Things Not To Do."
Results
After the four changes, the same SQL ran in just over 0.3 seconds, and three people clicking at month-end no longer queue. No sharding, no Redis, no bigger instance — two composite indexes, one STRAIGHT_JOIN, one query rewrite.
While we were there, we lowered the slow query threshold from 1 second to 0.5, to catch the next degrading statement earlier.
In Plain Terms
Nobody touched that SQL in five years — the data just grew. Indexes aren't something you build at launch and never revisit; once the data multiplies a few times over, run EXPLAIN again. Had we looked sooner, finance wouldn't have been the ones telling us "it's spinning again."
We finished that September 19 afternoon and pinned the EXPLAIN screenshot next to an internal doc called "Two Hours." Two screenshots nine years apart, same root cause: assuming that something configured once never needs checking again.
