N+1 Query Problem

Also Known As

TL;DR

Explanation

Classic example: fetch 100 users (1 query), then loop and fetch each user's orders separately (100 queries) = 101 queries total. The fix is eager loading — a single JOIN or a second query with WHERE user_id IN (...). ORMs like Eloquent and Doctrine make N+1 easy to introduce through lazy-loading relationships. SQL query logging or a profiler (Blackfire, Laravel Debugbar) reveals N+1 patterns.

Diagram

sequenceDiagram
    participant PHP
    participant MySQL
    PHP->>MySQL: SELECT * FROM users (1 query)
    loop For each of N users
        PHP->>MySQL: SELECT * FROM orders WHERE user_id=?
    end
    Note over PHP,MySQL: N+1 queries total<br/>Fix: JOIN or WHERE IN

Common Misconception

Why It Matters

Common Mistakes

• Calling a database function inside a foreach loop over query results.
• Using ORM lazy-loading relationships without checking the query count in development.
• Fixing N+1 with N separate queries instead of a single IN clause.

Avoid When

• Never call a database query inside a foreach loop over query results without batching.

When To Use

• Use JOIN or WHERE id IN (...) to batch-load related records in one query.
• Use eager loading in ORMs (with(), load()) to prevent automatic lazy-loading N+1.

Code Examples

// N+1: 1 query for users + N queries for orders
$users = $pdo->query('SELECT * FROM users')->fetchAll();
foreach ($users as $user) {
    $stmt = $pdo->prepare('SELECT * FROM orders WHERE user_id = ?');
    $stmt->execute([$user['id']]);
    $user['orders'] = $stmt->fetchAll(); // N separate queries!
}

// Fix: one query with JOIN or IN clause
$userIds = array_column($users, 'id');
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$stmt = $pdo->prepare("SELECT * FROM orders WHERE user_id IN ($placeholders)");
$stmt->execute($userIds);
$orders = $stmt->fetchAll();

// Group by user_id in PHP
$ordersByUser = [];
foreach ($orders as $order) {
    $ordersByUser[$order['user_id']][] = $order;
}

References

• ↗ https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/reference/dql-doctrine-query-language.html

Tags

Related Terms