Eloquent performance pitfalls — over-fetching, missing indexes
Concept
Eloquent hides SQL complexity but introduces performance pitfalls that are easy to miss in development but devastating in production.
N+1 queries (covered in lv-12-009): The most common pitfall. preventLazyLoading() in development catches these at runtime.
Over-fetching columns: User::all() fetches every column. On tables with text/blob columns (like article body, image data, logs), this wastes memory and bandwidth. Use select('id', 'name', 'email') to limit columns.
Over-fetching rows: Never use ::all() in production code paths without a LIMIT. Always paginate or constrain.
Missing database indexes: The most impactful performance issue. Every where() clause on an unindexed column causes a full table scan. Always index: foreign key columns, columns used in WHERE, ORDER BY, GROUP BY. Check slow queries with DB::listen() or a query log.
The withCount / withSum trick: Avoid loading entire relationships just to count them. $user->posts->count() loads all posts. $user->posts()->count() runs SELECT COUNT(*). User::withCount('posts')->get() adds posts_count with a subquery.
Eager loading too much: Loading deep nested relationships (with('a.b.c.d')) when only 1-2 fields are needed from the leaf. Use with(['relation:id,name']) to select specific columns.
Touching parent timestamps: $touches on child models adds an UPDATE on every child save. Can cause heavy write load on the parent table.
Code Example
<?php
// BAD — fetches all columns including large text fields
$articles = Article::all();
// GOOD — only what's needed for the listing page
$articles = Article::select('id', 'title', 'slug', 'published_at', 'author_id')
->with(['author:id,name'])
->paginate(15);
// BAD — N+1
$users = User::all();
foreach ($users as $user) {
echo $user->posts->count(); // N queries
}
// GOOD — withCount
$users = User::withCount('posts')->get();
foreach ($users as $user) {
echo $user->posts_count; // from SELECT COUNT(*) subquery
}
// BAD — loaded relationship to check existence
if ($user->orders->isEmpty()) { ... } // loads all orders
// GOOD — COUNT query
if (!$user->orders()->exists()) { ... } // SELECT EXISTS(...)
// Finding slow queries in development
DB::listen(function($query) {
if ($query->time > 100) { // > 100ms
\Illuminate\Support\Facades\Log::warning('Slow query', [
'sql' => $query->sql,
'bindings' => $query->bindings,
'time' => $query->time,
]);
}
});
// Query count in tests
DB::enableQueryLog();
// ... run code ...
$queries = DB::getQueryLog();
expect(count($queries))->toBeLessThan(5); // assert query count
// Indexes in migrations — critical for FK columns
Schema::table('orders', function (Blueprint $table) {
$table->index('user_id'); // FK column
$table->index('status'); // frequently filtered
$table->index(['status', 'created_at']); // compound index for sorted filtered results
});