Eloquent Query Optimization: Fix N+1 Before It Fixes Your Bill

Eloquent makes database work feel effortless—until your app hits production traffic and a single page fires 200 queries. The N+1 problem is the most common culprit, but it is far from the only one.

This post covers the optimization patterns I reach for first when a Laravel endpoint is slow.

Diagnosing the problem

Before optimizing, measure. Laravel Telescope, Debugbar, or a simple query log tell you what is actually happening:

// Temporarily in AppServiceProvider boot()
DB::listen(function ($query) {
    logger()->debug($query->sql, $query->bindings);
});

Or enable query logging in a test:

it('does not N+1 on the users index', function () {
    User::factory()->count(10)->has(Post::factory()->count(3))->create();

    DB::enableQueryLog();

    $this->getJson('/api/users')->assertOk();

    expect(count(DB::getQueryLog()))->toBeLessThan(5);
});

Set a query budget per endpoint and enforce it in tests. This prevents regressions.

N+1 and eager loading

The classic mistake:

// ❌ 1 query for users + N queries for each user's posts
$users = User::all();

foreach ($users as $user) {
    echo $user->posts->count();
}

The fix:

// ✅ 2 queries total
$users = User::with('posts')->get();

foreach ($users as $user) {
    echo $user->posts->count();
}

For nested relationships:

$orders = Order::with(['customer', 'items.product'])->paginate(20);

Constrained eager loading

Sometimes you only need a subset of related records:

$users = User::with(['posts' => function ($query) {
    $query->where('published', true)
        ->latest()
        ->limit(5);
}])->get();

Eager load counts without loading rows

When you only need counts, use withCount():

$users = User::withCount(['posts', 'comments'])->get();

// Access via $user->posts_count — no extra queries

Select only what you need

SELECT * on wide tables wastes memory and bandwidth:

// ❌ Loads every column including blobs and JSON
$users = User::all();

// ✅ Explicit columns
$users = User::select(['id', 'name', 'email', 'avatar_url'])->get();

For relationships, constrain columns on both sides:

$users = User::select(['id', 'name'])
    ->with(['posts' => fn ($q) => $q->select(['id', 'user_id', 'title', 'published_at'])])
    ->get();

Remember: when selecting constrained columns on a belongsTo or hasMany, always include the foreign key.

Chunking large datasets

Never load millions of rows into memory:

// ❌ Loads everything into RAM
User::where('inactive', true)->get()->each(fn ($user) => $user->delete());

// ✅ Process in chunks
User::where('inactive', true)
    ->chunkById(500, function ($users) {
        foreach ($users as $user) {
            $user->delete();
        }
    });

For exports and reports, use cursor() for lazy iteration:

foreach (Order::where('status', 'completed')->cursor() as $order) {
    fputcsv($handle, [$order->id, $order->total]);
}

Indexes that actually matter

Eloquent generates SQL—you still need proper indexes. Common patterns:

// Migration
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->index();
    $table->string('status')->index();
    $table->timestamp('created_at')->index();
    $table->index(['user_id', 'status']); // composite for filtered user queries
});

Use EXPLAIN on slow queries:

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

Look for type: ALL (full table scan) on large tables—that means a missing index.

Avoid query patterns that bypass indexes

// ❌ Function on column prevents index use
User::whereRaw('LOWER(email) = ?', [strtolower($email)])->first();

// ✅ Store normalized email, index it, query directly
User::where('email', strtolower($email))->first();

// ❌ Leading wildcard kills index
Product::where('name', 'LIKE', "%{$search}%")->get();

// ✅ Full-text search or dedicated search engine for fuzzy matching
Product::whereFullText('name', $search)->get();

Subqueries vs joins

For aggregations, subqueries in select() are often cleaner than joins:

$users = User::query()
    ->addSelect([
        'latest_order_at' => Order::select('created_at')
            ->whereColumn('orders.user_id', 'users.id')
            ->latest()
            ->limit(1),
    ])
    ->get();

For filtering by aggregate, use whereHas() sparingly—it can be slow on large tables. Sometimes a join is faster:

// Slower on large datasets
User::whereHas('orders', fn ($q) => $q->where('total', '>', 1000))->get();

// Often faster with a subquery or join
User::whereIn('id', function ($query) {
    $query->select('user_id')
        ->from('orders')
        ->where('total', '>', 1000);
})->get();

Profile both approaches with your actual data volumes.

Caching expensive queries

For read-heavy, rarely-changing data:

$categories = Cache::remember('categories.active', now()->addHour(), function () {
    return Category::where('active', true)
        ->orderBy('sort_order')
        ->get(['id', 'name', 'slug']);
});

Invalidate on write:

// In CategoryObserver
public function saved(Category $category): void
{
    Cache::forget('categories.active');
}

Use cache tags if your driver supports them (Redis, Memcached):

Cache::tags(['categories'])->flush();

Lazy loading prevention in development

Catch N+1 early with Model::preventLazyLoading() in non-production:

// AppServiceProvider
Model::preventLazyLoading(! app()->isProduction());

This throws an exception whenever you access an unloaded relationship—fail fast during development instead of silently burning queries in production.

Quick reference

Symptom	Likely cause	Fix
Query count scales with rows	N+1	with(), withCount()
High memory on batch jobs	Loading all rows	chunkById(), cursor()
Slow filtered lists	Missing index	Composite indexes on filter columns
Slow search	LIKE %term%	Full-text index or Meilisearch/Algolia
Repeated identical queries	No caching	Cache::remember() with invalidation

Eloquent is not slow—unoptimized usage of Eloquent is. These patterns keep your queries predictable and your infrastructure costs sane.