N+1 problem — what it is, how to detect it, how to fix it
Concept
N+1 problem: You execute 1 query to load N records, then N additional queries (1 per record) to load a related resource. With 100 users: 101 queries. With 10,000 users: 10,001 queries. Performance degrades linearly.
Classic N+1 scenario:
$users = User::all(); // 1 query: SELECT * FROM users
foreach ($users as $user) {
echo $user->posts->count(); // N queries: SELECT * FROM posts WHERE user_id = ?
}The fix — Eager Loading: Load all related records in a SECOND query using WHERE IN, then PHP merges them. Total: 2 queries regardless of N.
- Laravel:
User::with('posts')—SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...). withCount('posts'): adds aposts_countattribute withSELECT COUNT(*).
Detection methods:
DB::enableQueryLog()+DB::getQueryLog()— see all queries per request.- Laravel Debugbar package — shows queries in browser toolbar.
- Telescope — logs all queries per request in a UI.
Model::preventLazyLoading(true)(Laravel 8.4+) — throws an exception when lazy loading occurs in non-production.
Nested relationships: User::with('posts.comments') — eager loads posts AND their comments (3 queries total, not 1+N+N).
Lazy eager loading: Already-loaded collection? $users->loadMissing('posts').
Join vs Eager Load: Eager loading (2 queries) is usually preferable over JOIN for collections (avoids Cartesian product). Join is better when you need columns from both tables in a single flat result.
Code Example
<?php
// ❌ N+1 — 1 + N queries
$posts = Post::all();
foreach ($posts as $post) {
echo $post->author->name; // SELECT * FROM users WHERE id = ? (once per post!)
}
// ✅ Eager loading — 2 queries total
$posts = Post::with('author')->get();
foreach ($posts as $post) {
echo $post->author->name; // already loaded, no query
}
// ✅ withCount — avoid loading all related models just to count
$users = User::withCount('posts')->get();
foreach ($users as $user) {
echo $user->posts_count; // no extra query
}
// ✅ Nested eager loading
$users = User::with('posts.comments.author')->get();
// 4 queries: users, posts (IN users), comments (IN posts), users again (IN comments)
// ✅ Conditional eager loading
$posts = Post::with(['comments' => fn($q) => $q->where('approved', true)])->get();
// Detection during development
\DB::enableQueryLog();
$posts = Post::all();
foreach ($posts as $post) { $post->author->name; }
dd(\DB::getQueryLog()); // see all N+1 queries
// Prevent lazy loading globally (throws exception on lazy load)
// In AppServiceProvider::boot():
\Illuminate\Database\Eloquent\Model::preventLazyLoading(! app()->isProduction());
// ✅ loadMissing — eager load on already-fetched collection
$posts = Post::all(); // fetched elsewhere
$posts->loadMissing('author'); // one query for all authors
$posts->loadMissing('author', 'tags'); // two extra queries-- What eager loading does under the hood (2 queries vs N+1)
-- Query 1:
SELECT * FROM posts;
-- Query 2 (Laravel replaces ? with all found user_ids):
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5, ...);
-- PHP then matches posts to users by user_id in memory