Fetching user orders took ~2ms in staging.
In production it took ~4s for 100 orders. 💀
Fetch the user’s orders. Then for every order:
- fetch its item specifications
- fetch its shipping/payment details
That is 1 + 100 + 100 queries.
It worked fine in staging because the test user had 2 orders. Real users had 100, and a few had way more.
This time there was no one-line fix. Every option had a drawback 👇
1. One big JOIN
Orders + items + shipping in one query.
Catch: 100 orders × 5 items = 500 rows, with the same order data repeated across rows.
2. Bulk fetch with IN
- 1 query for orders
- 1 query for items
WHERE order_id IN (...) - 1 query for shipping
WHERE order_id IN (...)
Then stitch everything together in memory using a map keyed by order_id.
3 queries, whether it’s 5 orders or 500.
3. Paginate
Don’t load 100 orders in one request. Load 20 at a time.
Catch: less data per request, but pagination alone doesn’t fix N+1. 20 orders is still 41 queries.
4. Snapshot item specs on the order
Product specs can change after purchase, but an order should show what the customer actually bought.
So we store the relevant specs with the order itself, at checkout. No lookup needed later.
What we shipped
We went with 2 + 3.
Now whatever page of orders the user opens, the API makes 3 queries, not 1 + 2N ✅
The loop looked like business logic. Hidden inside it were 200 trips to my database.