Laravel Eloquent Performance Tuning & N+1 Fixes

Introduction: The Double-Edged Sword of ORM Convenience

Laravel's Eloquent ORM is celebrated across the PHP ecosystem for its expressive syntax, intuitive active record pattern, and seamless relationship declarations. Writing $user->scores()->latest()->get() allows developers to ship features with speed and clean code readability.

However, the abstraction provided by Eloquent can obscure the actual SQL queries being dispatched to the database engine. In production environments with thousands of concurrent users, un-optimized Eloquent calls can trigger the infamous N+1 Query Problem, memory bloat, and locking bottlenecks that degrade API response times from 20ms to several seconds.

In this technical guide, we explore how to identify and eradicate N+1 bottlenecks, enforce strict model safety, leverage subquery selects, and implement cursor pagination for high-volume database datasets.

1. Demystifying the N+1 Query Problem

The N+1 query problem occurs when an application executes 1 initial query to fetch a parent dataset of $N$ records, and then proceeds to execute $N$ additional sequential queries inside a loop to fetch each record's related model:

1 Initial Query: SELECT * FROM users LIMIT 50; (Fetches 50 users)
+ 50 Additional Queries: SELECT * FROM tier_badges WHERE user_id = ?; (1 per user)
= 51 Total Database Roundtrips!

The Unoptimized Code Example:

// ❌ WRONG: Triggers N+1 queries when accessing $user->badge
$topPlayers = User::query()
    ->orderByDesc('lifetime_points')
    ->take(50)
    ->get();

foreach ($topPlayers as $user) {
    // Each iteration executes: SELECT * FROM badges WHERE user_id = X
    echo $user->name . ' - ' . $user->badge->title . PHP_EOL;
}

The Fix: Eager Loading with with()

// ✅ CORRECT: Executes exactly 2 optimized queries regardless of record count
$topPlayers = User::query()
    ->with('badge')
    ->orderByDesc('lifetime_points')
    ->take(50)
    ->get();

-- Query 1:
SELECT * FROM `users` ORDER BY `lifetime_points` DESC LIMIT 50;

-- Query 2:
SELECT * FROM `badges` WHERE `badges`.`user_id` IN (1, 2, 3, ... 50);

2. Enforcing Strict Model Safety in Development

To prevent N+1 bugs from slipping into production, Laravel provides a strict mode setting that throws an immediate LazyLoadingViolationException if any model attempts to lazy-load a relationship.

Add this configuration to your AppServiceProvider.php:

namespace App\Providers;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        // Prevent lazy loading in local development & testing environments
        Model::preventLazyLoading(! app()->isProduction());

        // Prevent silently discarding un-fillable attributes
        Model::preventSilentlyDiscardingAttributes(! app()->isProduction());

        // Prevent accessing missing model attributes
        Model::preventAccessingMissingAttributes(! app()->isProduction());
    }
}

With Model::preventLazyLoading(true) active, any missing with() eager load immediately crashes the local test runner with a clear stack trace, guaranteeing zero lazy-loading regressions in production releases.

3. High-Performance Subquery Selects with addSelect()

When calculating aggregated stats (such as a user's total completed rounds or their highest score), developers often eager load entire relationship collections into PHP memory:

// ❌ HEAVY: Loads 10,000 score objects into PHP memory just to calculate an average!
$users = User::with('scores')->get();
foreach ($users as $user) {
    $avg = $user->scores->avg('points');
}

Instead of hydrating thousands of Eloquent model instances into RAM, push the calculation directly into the database engine using withAvg(), withCount(), or raw subquery selects:

// ✅ FAST: Computes aggregate in SQL without hydrating child models
$users = User::query()
    ->withCount('scores')
    ->withAvg('scores', 'points')
    ->withMax('scores', 'points')
    ->orderByDesc('scores_max_points')
    ->paginate(20);

// Access results directly as model attributes:
// $user->scores_count
// $user->scores_avg_points
// $user->scores_max_points

4. Cursor Pagination vs. Offset Pagination for Deep Datasets

Standard offset pagination (User::paginate(50)) generates SQL using LIMIT 50 OFFSET 10000. As the offset increases, MySQL must read and discard 10,000 index rows before returning the target 50 records. On large tables (e.g., 500,000 quiz history logs), page 200 can take 2,000ms+ to execute.

Cursor Pagination uses a "where id > ?" condition based on indexed columns, delivering consistent sub-5ms query times regardless of pagination depth:

// ❌ Standard Offset (Slow on deep pages: O(N) scan)
$logs = QuizAttempt::orderByDesc('id')->paginate(50);

// ✅ Cursor Pagination (Sub-5ms on any page: O(1) index seek)
$logs = QuizAttempt::orderByDesc('id')->cursorPaginate(50);

-- Offset Pagination (Page 200)
SELECT * FROM `quiz_attempts` ORDER BY `id` DESC LIMIT 50 OFFSET 10000;

-- Cursor Pagination (Page 200)
SELECT * FROM `quiz_attempts` WHERE `id` < 489500 ORDER BY `id` DESC LIMIT 50;

5. Chunking vs. Lazy Collections for Bulk Processing

When processing millions of records in scheduled Artisan commands or background queue jobs, never use User::all(), which will exhaust PHP's memory limit (memory_limit).

Use Lazy Collections backed by database cursors to stream records with a flat ~2MB memory footprint:

namespace App\Console\Commands;

use App\Models\User;
use Illuminate\Console\Command;

class RecalculateTiersCommand extends Command
{
    protected $signature = 'tiers:recalculate';

    public function handle(): void
    {
        // Streams 100,000+ users with constant flat memory usage (~2MB)
        User::query()
            ->lazy(1000)
            ->each(function (User $user) {
                $user->updateTierBadge();
            });

        $this->info('Tier recalculation complete!');
    }
}

Frequently Asked Questions (FAQ)

What is the performance difference between with() and load()?

Can eager loading be applied to nested relationships?

Why does offset pagination slow down on high page numbers?

How does select(['id', 'name']) optimize query memory?