N+1 Query Problem & Eager Loading
Identify and solve the notorious N+1 query problem that kills application performance
The N+1 query problem is the most common performance killer in Eloquent applications. It occurs when you load a collection of models and then access a relationship on each one. The name comes from executing 1 query to get the parent records, plus N additional queries (one for each parent) to get related records.
Why is this a problem?
• Database Overhead: 100 users = 101 queries instead of 2
• Network Latency: Each query has network overhead
• Memory Usage: Database connections are expensive
• Slow Response Times: Can turn a 50ms response into 5 seconds
Real-World Impact:
A blog displaying 20 posts with author names might run 21 queries without eager loading. At 10ms per query, that's 210ms just for database calls. With eager loading, it's 2 queries = 20ms. That's a 10x improvement!
The Solution: Eager Loading
Use with() to load relationships upfront in just 2 queries.
Why is this a problem?
• Database Overhead: 100 users = 101 queries instead of 2
• Network Latency: Each query has network overhead
• Memory Usage: Database connections are expensive
• Slow Response Times: Can turn a 50ms response into 5 seconds
Real-World Impact:
A blog displaying 20 posts with author names might run 21 queries without eager loading. At 10ms per query, that's 210ms just for database calls. With eager loading, it's 2 queries = 20ms. That's a 10x improvement!
The Solution: Eager Loading
Use with() to load relationships upfront in just 2 queries.
Code Examples
The N+1 Problem (BAD)
✗ Avoid This
php
<?php
// ❌ N+1 Problem - Runs 1 + N queries
$posts = Post::all(); // 1 query: SELECT * FROM posts
foreach ($posts as $post) {
echo $post->author->name; // N queries: SELECT * FROM users WHERE id = ?
echo $post->category->name; // N more queries!
}
// If you have 20 posts:
// 1 query for posts
// 20 queries for authors
// 20 queries for categories
// TOTAL: 41 queries! 😱
// Laravel Debugbar will show:
// Query 1: SELECT * FROM posts
// Query 2: SELECT * FROM users WHERE id = 1
// Query 3: SELECT * FROM categories WHERE id = 5
// Query 4: SELECT * FROM users WHERE id = 2
// Query 5: SELECT * FROM categories WHERE id = 3
// ... and so on
// Real-world example: E-commerce product list
$products = Product::all(); // 100 products
foreach ($products as $product) {
echo $product->brand->name; // 100 queries
echo $product->category->name; // 100 queries
echo $product->images->first(); // 100 queries
}
// TOTAL: 301 queries for ONE PAGE! 🔥💡 Each time you access a relationship inside a loop, Eloquent runs a new query. This multiplies quickly and destroys performance.
Eager Loading Solution (GOOD)
💡 Solution
php
<?php
// ✅ Solution: Eager Loading with with()
$posts = Post::with(['author', 'category'])->get();
foreach ($posts as $post) {
echo $post->author->name; // No query! Data already loaded
echo $post->category->name; // No query! Data already loaded
}
// Only 3 queries total:
// Query 1: SELECT * FROM posts
// Query 2: SELECT * FROM users WHERE id IN (1, 2, 3, 4, ...)
// Query 3: SELECT * FROM categories WHERE id IN (5, 3, 8, ...)
// Real-world example: E-commerce product list (fixed)
$products = Product::with(['brand', 'category', 'images'])->get();
foreach ($products as $product) {
echo $product->brand->name; // No query
echo $product->category->name; // No query
echo $product->images->first(); // No query
}
// TOTAL: 4 queries (products, brands, categories, images) ✅
// Nested eager loading:
$posts = Post::with([
'author', // Load authors
'comments', // Load comments
'comments.author', // Load comment authors
'tags', // Load tags
])->get();
// Constrained eager loading:
$posts = Post::with([
'comments' => function ($query) {
$query->where('approved', true)
->orderBy('created_at', 'desc')
->limit(5);
}
])->get();
// Lazy eager loading (when you forgot to eager load):
$posts = Post::all(); // Oops, forgot with()
$posts->load('author'); // Load now (still better than N+1)💡 Eager loading with() loads all relationships upfront. Instead of N+1 queries, you get a fixed number of queries regardless of how many records.
Detecting N+1 in Development
💡 Solution
php
<?php
// Install Laravel Debugbar (shows all queries)
// composer require barryvdh/laravel-debugbar --dev
// Or use Laravel Telescope
// php artisan telescope:install
// Enable query logging in AppServiceProvider:
use Illuminate\Support\Facades\DB;
public function boot(): void
{
if (app()->environment('local')) {
DB::listen(function ($query) {
Log::info('Query: ' . $query->sql);
Log::info('Bindings: ' . json_encode($query->bindings));
Log::info('Time: ' . $query->time . 'ms');
});
}
}
// Prevent N+1 in tests and development:
// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;
public function boot(): void
{
// Throw exception on lazy loading (strict mode)
Model::preventLazyLoading(! app()->isProduction());
// Now if you forget eager loading:
$posts = Post::all();
$posts[0]->author; // Throws exception in dev!
// "Attempted to lazy load [author] on [Post] but lazy loading is disabled"
}
// Use in code to detect issues:
$posts = Post::all();
DB::enableQueryLog();
foreach ($posts as $post) {
echo $post->author->name;
}
$queries = DB::getQueryLog();
dump($queries); // See all queries that ran💡 Use debugging tools to detect N+1 problems during development. Enable preventLazyLoading() to catch them automatically.