The N+1 Query: Why Your Site Is Slower Than Your Numbers Say

Every production system I’ve maintained has the same quiet hero: the database query that was fast enough that nobody noticed it. And the same quiet villain: the query that “worked fine” until traffic arrived and it started doing the same work ten thousand times a minute.

The query that isn’t doing what you think

Here’s a pattern I’ve seen in more PHP codebases than I’d like to admit:

foreach ($orders as $order) {
    $items = $db->query(
        "SELECT * FROM order_items WHERE order_id = ?"
        , [$order['id']]
    );
    // render $items
}

If that loop runs 200 times, you just ran 201 queries. Each one individually fast — that’s the trap. The database never tells you it’s the aggregate that’s slow.

The fix: one query, joined or batched

Two options, in order of preference:

1. JOIN, if you need the data together anyway. One round trip, the database does the work where the data lives.
2. BATCH with IN (...), if the shapes don’t fit a JOIN. Collect the IDs, fetch everything in one statement, group in PHP.

$ids = array_column($orders, 'id');
// chunk to keep the IN() list sane
$chunks = array_chunk($ids, 500);
$items = [];
foreach ($chunks as $chunk) {
    $placeholders = implode(',', array_fill(0, count($chunk), '?'));
    $rows = $db->query(
        "SELECT * FROM order_items WHERE order_id IN ($placeholders)",
        $chunk
    );
    foreach ($rows as $row) {
        $items[$row['order_id']][] = $row;
    }
}
// $items[order_id] is now ready to render, zero extra queries

How to find these before they find you

• Turn on slow_query_log with a low threshold in staging (50ms is a fine start).
• Run EXPLAIN ANALYZE on the queries your most common page hits — not the exotic ones. The boring pages are where the volume lives.
• Watch Threads_running under realistic load. A sudden climb while CPU is flat is your N+1 signature.

The uncomfortable part: none of this shows up as an error. The site is “fine” until it isn’t. Budget an afternoon to profile your five most-visited templates. It’s the cheapest performance work you’ll ever do.

Next in this series: the front-end side of the same problem — why your page is probably slower than your numbers say.

— Stella