N+1 problem — executing N extra queries for N rows instead of one join

Beginner5 min read·eng-15-013

sqlinterviewperformance

Concept

N+1 problem — a query anti-pattern where fetching a list of N items and then loading a related property for each item results in N+1 database queries instead of 1 or 2.

Classic example: Load 10 users, then show each user's role. Without eager loading: 1 query to get users + 10 queries to get each user's role = 11 queries (N+1).

Why N+1 is bad: In development with 5 records, it's invisible. In production with 10,000 records, it's 10,001 queries per request — often causing request timeouts or database overload.

How it happens in Eloquent: When you access a relation that hasn't been loaded, Eloquent fires a new query automatically (lazy loading). This is convenient but dangerous.

Detecting N+1 in Laravel:

Laravel Debugbar: Shows all queries per request.
Telescope: SQL tab shows all queries with timings.
DB::enableQueryLog() + DB::getQueryLog().
preventLazyLoading() in AppServiceProvider::boot() — throws an exception when lazy loading is detected.

Solutions:

Eager loading (with()): Load relations in one query using a WHERE IN.
Lazy eager loading (load()): After you already have the collection, load relations.
withCount(): Load the count of a relation without loading all related records.
Sub-select pattern: Add a calculated column via subquery.

Code Example

php

<?php
// ❌ N+1 PROBLEM — 1 + N queries
$users = User::all(); // 1 query: SELECT * FROM users
foreach ($users as $user) {
    echo $user->role->name; // lazy loads role: SELECT * FROM roles WHERE id = ?
    // For 100 users: 100 extra queries!
}
// Total: 1 + 100 = 101 queries

// ✅ EAGER LOADING with() — 2 queries total
$users = User::with('role')->get();
// Query 1: SELECT * FROM users
// Query 2: SELECT * FROM roles WHERE id IN (1, 2, 3, ...)  ← one query for ALL roles

foreach ($users as $user) {
    echo $user->role->name; // already loaded — no extra query
}
// Total: 2 queries regardless of N

// ✅ NESTED EAGER LOADING
$orders = Order::with(['user', 'items.product'])->get();
// Loads: orders, then users, then items, then products for those items
// 4 queries total, not 1 + N + N*M

// ✅ withCount() — count relations without loading records
$users = User::withCount('orders')->get();
// Query 1: SELECT *, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as orders_count FROM users
foreach ($users as $user) {
    echo $user->orders_count; // no extra query
}

// ✅ load() — lazy eager loading (after the fact)
$users = User::all(); // already loaded without relations
if ($someCondition) {
    $users->load('role'); // loads roles in one query only if needed
}

// ✅ loadMissing() — load only if not already loaded
$user->loadMissing('orders');

// DETECTING N+1
// In AppServiceProvider::boot():
if (app()->isLocal()) {
    \Illuminate\Database\Eloquent\Model::preventLazyLoading(true);
    // Now any lazy load throws: LazyLoadingViolationException
    // Forces you to add with() for every relation
}

// Logging queries
\DB::enableQueryLog();
$users = User::with('role')->get()->each(fn($u) => $u->orders); // find the N+1!
$log   = \DB::getQueryLog(); // see every query executed
\DB::disableQueryLog();

echo count($log) . " queries executed\n";

PreviousDeadlock — two transactions each waiting for the other to release a lockVocabulary — Database & SQL NextEager loading vs lazy loading — fetching related data now vs on-demandVocabulary — Database & SQL