Queries in a Loop

Why one query per row makes a page slow, how to replace the loop with one IN query or a join, and how to measure the difference.

Syntax$in = implode(',', array_fill(0, count($ids), '?')); // SELECT ... WHERE id IN ($in)

Fetch 50 orders, then look up each order's customer inside the loop, and the page has run 51 queries: one for the list, one per row. That is the N+1 problem. Each query is fast on its own, and the code reads perfectly well, which is why it survives review. The page is slow because of the round trips, not the work.

Seeing it

It shows up as the same statement repeated with a different parameter: in the database's slow or general query log, in a counter you increment in your own database wrapper, or in a profiler where one small function has thousands of calls. Count the queries for one request before you change anything.

Collect the ids, ask once

Gather the ids from the first result set, build one placeholder for each, and fetch them all with IN. Index the returned rows by id in PHP, and the lookup inside the loop becomes an array read. Two queries have replaced 51, and the ids are still bound rather than pasted into the SQL.

Or join

A JOIN does it in one query, repeating the customer's columns on every order row. Prefer the join when the relation is one-to-one. Prefer the two-query IN version for one-to-many, where a join multiplies the rows and you have to group them in PHP anyway.

Check the result

Count queries and total query time for the same request afterwards. A change from 51 queries to two that adds a full table scan is not an improvement.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Working out why a listing page with 50 rows takes two seconds while each query in the log is under a millisecond.
  • Turning a per-row lookup into one IN query when loading authors, categories or prices for a list.
  • Choosing between a join and a second query when loading a one-to-many relation such as orders and their items.
  • Adding a query counter to the development environment so a new N+1 is noticed when it is written.

More examples

The version that causes it

Worth reading closely, because this is what the problem looks like in real code and nothing about it is obviously wrong. The statement is prepared once, which is the usual advice, and it is still 51 round trips. Two orders from the same customer fetch that customer twice. The clue in a query log is one statement repeated with different bound values, in a tight group, all from the same request.

Example · php
<?php

declare(strict_types=1);

// 1 + N queries: the execute() inside the loop runs once per order.
$orders = $pdo->query('SELECT id, customer_id, total FROM orders ORDER BY id LIMIT 50')
    ->fetchAll(PDO::FETCH_ASSOC);

$customer = $pdo->prepare('SELECT name FROM customers WHERE id = ?');

foreach ($orders as $order)
{
    $customer->execute([$order['customer_id']]);
    $name = (string) $customer->fetchColumn();

    echo htmlspecialchars($name, ENT_QUOTES, 'UTF-8'), "<br>\n";
}

Fixed with a single IN query

Two queries for any number of orders. The only thing interpolated into the SQL is a run of question marks whose count comes from count($ids), so no value from the database or the request reaches the query text. PDO::FETCH_KEY_PAIR turns a two-column result into id => name, which is exactly the lookup table the loop needs. A statement with a fixed number of placeholders cannot be reused for a different list length, and binding the whole imploded list as one parameter matches nothing, so the SQL is built per call.

Example · php
<?php

declare(strict_types=1);

$orders = $pdo->query('SELECT id, customer_id, total FROM orders ORDER BY id LIMIT 50')
    ->fetchAll(PDO::FETCH_ASSOC);

$ids = array_values(array_unique(array_column($orders, 'customer_id')));
$names = [];

if ($ids !== [])
{
    $placeholders = implode(',', array_fill(0, count($ids), '?'));

    $statement = $pdo->prepare("SELECT id, name FROM customers WHERE id IN ({$placeholders})");
    $statement->execute($ids);

    $names = $statement->fetchAll(PDO::FETCH_KEY_PAIR);
}

foreach ($orders as $order)
{
    echo htmlspecialchars($names[$order['customer_id']] ?? 'unknown', ENT_QUOTES, 'UTF-8'), "<br>\n";
}

The join, and counting queries while you work

Use the join when you want one row per order and each order has one customer. The counter is the measuring half of the lesson: swap PDO for CountingPdo in development, log the total at the end of the request, and the N+1 announces itself as a number instead of a hunch. Note that it counts prepare() only; add an override for query() with its own signature if your code uses that too, and remember prepare() counts statements created, not executions, so a loop calling execute() on one statement still needs the log to be read carefully.

Example · php
<?php

declare(strict_types=1);

// One query. The customer columns repeat on each order row.
$sql = 'SELECT o.id, o.total, c.name
          FROM orders o
          JOIN customers c ON c.id = o.customer_id
      ORDER BY o.id
         LIMIT 50';

$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

// A wrapper that counts what a request actually sends.
final class CountingPdo extends PDO
{
    public int $queries = 0;

    public function prepare(string $query, array $options = []): PDOStatement|false
    {
        $this->queries++;

        return parent::prepare($query, $options);
    }
}

// register_shutdown_function(static fn () => error_log("queries: {$pdo->queries}"));

Related

php pdo preparedphp pdo fetchphp cachingphp profilingarray_column

Discussion

  • Be the first to comment on this lesson.