Laravel Database Optimization: Fix & Prevent N+1 Queries

Laravel Database Optimization: How to Diagnose, Fix, and Prevent N+1 Queries

Eloquent ORM is one of Laravel's greatest strengths, allowing developers to write clean, expressive code without touching raw SQL. In a local development environment with a handful of seeded database rows, querying related models on the fly works without noticeable latency.

Once an application enters production with tens of thousands of active users and hundreds of thousands of database records, unoptimized Eloquent queries become the primary bottleneck. Network roundtrips multiply, database connection pools exhaust, CPU utilization spikes to 100%, and API endpoints begin throwing 504 Gateway Timeouts.

Optimizing Laravel database performance requires understanding how Eloquent translates relationship properties into SQL, knowing how to force eager loading, and structuring indexes to match query execution plans.

sequenceDiagram
    autonumber
    actor Client as Frontend Client
    participant App as Laravel Application
    participant DB as MySQL / PostgreSQL Database

    Note over App,DB: The N+1 Query Storm (Lazy Loading Anti-Pattern)
    Client->>App: GET /api/v1/orders
    App->>DB: 1. SELECT * FROM orders LIMIT 25
    DB-->>App: Returns 25 Orders
    loop For Each of the 25 Orders
        App->>DB: SELECT * FROM users WHERE id = ?
        DB-->>App: Returns User Record
    end
    App-->>Client: 26 Total Database Roundtrips (High Latency)

    Note over App,DB: Optimized Eager Loading
    Client->>App: GET /api/v1/orders
    App->>DB: 1. SELECT id, user_id, status, total FROM orders LIMIT 25
    App->>DB: 2. SELECT id, name, email FROM users WHERE id IN (1, 4, 9, 15, ...)
    DB-->>App: Returns Batched Result Sets
    App-->>Client: 2 Total Database Roundtrips (Fast & Scalable)

Quick Summary & Performance Rules

To keep Laravel database throughput high and database response times under 20ms:

1. Lock Down Lazy Loading: Run Model::preventLazyLoading() in development and testing to catch missing relationships before code merges into production.

2. Eager Load with Explicit Columns: Always use with(['relation:id,foreign_key,column']) to avoid hydrating unnecessary columns into memory.

3. Replace Loops with Subquery Aggregates: Use withCount(), withMax(), withAvg(), and addSelect() subqueries instead of loading full relation collections just to compute a single statistic.

4. Use Keyset Pagination: Replace offset-heavy paginate() calls with cursorPaginate() for high-volume datasets.

5. Index Foreign Keys and Filter Predicates: Ensure composite indexes match the exact column order used in WHERE, JOIN, and ORDER BY clauses.

Performance Comparison: Unoptimized vs Optimized Eloquent

The table below demonstrates benchmark metrics from an actual production endpoint fetching 50 customers with their latest order and total spending history on a dataset of 120,000 records:

Step 1: Detecting and Locking Down Lazy Loading

The N+1 problem occurs when accessing a dynamic relationship property on an Eloquent model (such as$order->customer->name) without pre-loading the relation. Laravel executes an isolated SQL query for every single item in the collection.

Enforcing Strict Model Rules in Development

Laravel provides a built-in strictness mode that stops lazy loading during development and automated tests.

Open app/Providers/AppServiceProvider.php and configure strictness inside the boot() method:

namespace App\Providers;

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

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     */
    public function register(): void
    {
        //
    }

    /**
     * Bootstrap any application services.
     */
    public function boot(): void
    {
        // Disable lazy loading outside of production
        Model::preventLazyLoading(! app()->isProduction());

        // Prevent silent attribute discard when mass-assigning
        Model::preventSilentlyDiscardingAttributes(! app()->isProduction());

        // Prevent accessing attributes that were not fetched via select()
        Model::preventAccessingMissingAttributes(! app()->isProduction());

        // In production, log violations to your monitoring platform without crashing requests
        if (app()->isProduction()) {
            Model::handleLazyLoadingViolationUsing(function (Model $model, string $relation) {
                Log::warning('Production N+1 Query Detected', [
                    'model' => get_class($model),
                    'relation' => $relation,
                    'url' => request()->fullUrl(),
                ]);
            });
        }
    }
}

With preventLazyLoading(true) active, any code path attempting to lazy-load a relation immediately throws an Illuminate\Database\LazyLoadingViolationException in your local environment or CI pipeline:

Attempted to lazy load [customer] on model [App\Models\Order] but lazy loading is disabled.

This guarantees that unoptimized loops are fixed during feature development rather than discovered in production alerts.

For more architectural best practices on keeping controllers clean and performant, check out our guide on 3 Laravel Mistakes That Break Production.

Step 2: Mastering Eager Loading and Column Selection

The direct fix for N+1 queries is eager loading using the with() method.

The Problem: Hydrating All Columns

When developers write Order::with('user')->get(), Laravel runs SELECT * FROM users WHERE id IN (...). If your users table contains 40 columns (including JSON profile blobs, hashed passwords, timestamps, and address fields), Eloquent must allocate PHP memory to hydrate all 40 attributes for every single related model.

flowchart TD
    A["Order::with('user:id,name,email')"] --> B["Fetch 50 Orders"]
    B --> C["Fetch 50 Users (Selected Columns Only)"]
    C --> D["Memory Footprint: ~2 MB"]

    E["Order::with('user')->get() (SELECT *)"] --> F["Fetch 50 Orders"]
    F --> G["Fetch 50 Users (All 40 Columns + Blobs)"]
    G --> H["Memory Footprint: ~18 MB"]

The Solution: Constrained Column Eager Loading

Always specify the explicit columns needed by the consumer.

[!IMPORTANT] When constraining eager loaded columns, you must include the id (primary key) and the relevant foreign key connecting the parent and child models. If you omit the foreign key, Eloquent cannot match the child models to their parent entities in memory and will return null.

namespace App\Services;

use App\Models\Order;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;

class OrderQueryService
{
    /**
     * Fetch paginated orders with optimized eager loading constraints.
     */
    public function getRecentOrders(): LengthAwarePaginator
    {
        return Order::query()
            ->select([
                'id',
                'user_id',
                'payment_method_id',
                'reference_number',
                'total_amount_cents',
                'status',
                'created_at',
            ])
            ->with([
                // Crucial: 'id' must be selected for Eloquent relation mapping
                'user:id,name,email',
                'paymentMethod:id,user_id,gateway_name,last_four',
                // Nested relationship eager loading with column constraint
                'items:id,order_id,product_id,quantity,unit_price_cents' => [
                    'product:id,title,sku',
                ],
            ])
            ->where('status', '!=', 'cancelled')
            ->latest('id')
            ->paginate(25);
    }
}

Dynamic Lazy Eager Loading (loadMissing)

When receiving an already instantiated model instance (e.g., inside an API Controller or queued Job), use loadMissing() instead of load(). loadMissing() inspects the model's internal relationship cache and only executes SQL queries if the relation has not already been loaded:

namespace App\Http\Controllers\Api\v1;

use App\Http\Controllers\Controller;
use App\Http\Resources\OrderResource;
use App\Models\Order;

class OrderDetailController extends Controller
{
    public function show(Order $order): OrderResource
    {
        // Only queries database if 'items' or 'user' were not pre-loaded by route binding
        $order->loadMissing([
            'user:id,name,email',
            'items.product:id,title,sku,price_cents',
        ]);

        return new OrderResource($order);
    }
}

Step 3: Eliminating Relational Overhead with Subquery Aggregates

A common mistake is eager loading an entire collection of related models solely to display a count, average, maximum, or single aggregate value.

The Anti-Pattern: Loading Entire Relations for Statistics

// ANTI-PATTERN: Loads 10,000 order models into PHP memory just to sum total sales
$users = User::with('orders')->get();

$data = $users->map(function (User $user) {
    return [
        'name' => $user->name,
        'order_count' => $user->orders->count(), // Counts in PHP collection
        'total_spent' => $user->orders->sum('total_amount'), // Sums in PHP collection
        'latest_order_date' => $user->orders->max('created_at'),
    ];
});

This pattern wastes megabytes of server memory because hundreds of full model instances are constructed in PHP memory when the database engine could calculate the results in microseconds.

The Refactored Solution:withCount(),withAggregate(), and Subqueries

Laravel provides dedicated methods to execute subquery aggregations inside the primary SELECT query:

namespace App\Services;

use App\Models\Order;
use App\Models\User;
use Illuminate\Database\Eloquent\Collection;

class UserAnalyticsService
{
    /**
     * Retrieve users with database-calculated aggregates in a single SQL query.
     */
    public function getUserSpendingSummary(): Collection
    {
        return User::query()
            ->select(['id', 'name', 'email', 'created_at'])
            // Calculates COUNT(orders.id) AS orders_count
            ->withCount([
                'orders' => fn ($query) => $query->where('status', 'completed'),
            ])
            // Calculates SUM(orders.total_amount_cents) AS total_spent_cents
            ->withSum([
                'orders as total_spent_cents' => fn ($query) => $query->where('status', 'completed'),
            ], 'total_amount_cents')
            // Calculates MAX(orders.created_at) AS latest_order_at
            ->withMax('orders as latest_order_at', 'created_at')
            // Fetch the latest order status using a correlated subquery
            ->addSelect([
                'latest_order_status' => Order::query()
                    ->select('status')
                    ->whereColumn('orders.user_id', 'users.id')
                    ->latest('id')
                    ->limit(1),
            ])
            ->where('is_active', true)
            ->orderByDesc('total_spent_cents')
            ->limit(50)
            ->get();
    }
}

Generated SQL Inspection

This single query eliminates all N+1 iterations and memory overhead:

SELECT 
    `users`.`id`, 
    `users`.`name`, 
    `users`.`email`, 
    `users`.`created_at`,
    (SELECT COUNT(*) FROM `orders` WHERE `orders`.`user_id` = `users`.`id` AND `status` = 'completed') AS `orders_count`,
    (SELECT SUM(`orders`.`total_amount_cents`) FROM `orders` WHERE `orders`.`user_id` = `users`.`id` AND `status` = 'completed') AS `total_spent_cents`,
    (SELECT MAX(`orders`.`created_at`) FROM `orders` WHERE `orders`.`user_id` = `users`.`id`) AS `latest_order_at`,
    (SELECT `status` FROM `orders` WHERE `orders`.`user_id` = `users`.`id` ORDER BY `id` DESC LIMIT 1) AS `latest_order_status`
FROM `users`
WHERE `is_active` = 1
ORDER BY `total_spent_cents` DESC
LIMIT 50;

Step 4: Streaming Large Datasets with Memory-Safe Iterators

When writing background jobs, data exports, or CLI migration scripts that process millions of records, using Model::all() or Model::get() will exhaust the PHP memory_limit.

flowchart LR
    subgraph Bad["❌ Memory Crash Risk"]
        M1["User::all()"] --> M2["Allocates 500,000 Models in RAM"] --> M3["Fatal Error: Allowed Memory Size Exhausted"]
    end

    subgraph Good["✅ Scalable Stream"]
        C1["User::cursor()"] --> C2["PHP Generator (1 Row in RAM at a time)"] --> C3["Constant 4MB Memory Usage"]
    end

Comparingchunk(),chunkById(), andcursor()

Thechunk()Offset Trap

If you update records inside a chunk() callback that affect the query's WHERE condition, the offset shifts and rows are silently skipped:

// BUG: Records will be skipped!
// When the first 100 records are updated to processed = true,
// the second chunk with OFFSET 100 skips records 101-200.
Order::where('is_processed', false)->chunk(100, function ($orders) {
    foreach ($orders as $order) {
        $order->update(['is_processed' => true]);
    }
});

Safe Keyset Chunking (chunkById)

Always use chunkById() for batch operations. It anchors pagination to the primary key (id > last_id), preventing offset displacement:

namespace App\Console\Commands;

use App\Models\Order;
use App\Services\OrderSettlementService;
use Illuminate\Console\Command;

class SettlePendingOrdersCommand extends Command
{
    protected $signature = 'orders:settle-pending';
    protected $description = 'Process pending settlement for all verified orders';

    public function handle(OrderSettlementService $settlementService): int
    {
        $processedCount = 0;

        Order::query()
            ->where('status', 'paid')
            ->where('is_settled', false)
            ->with(['items:id,order_id,product_id,unit_price_cents', 'user:id,email'])
            ->chunkById(500, function ($orders) use ($settlementService, &$processedCount) {
                foreach ($orders as $order) {
                    $settlementService->settle($order);
                    $processedCount++;
                }

                $this->info("Processed batch of 500 orders. Total: {$processedCount}");
            });

        return Command::SUCCESS;
    }
}

High-Speed Memory-Efficient CSV Streaming (cursor)

For data exports where you need to loop over 500,000 records without buffering them into arrays:

namespace App\Services;

use App\Models\User;
use Symfony\Component\HttpFoundation\StreamedResponse;

class UserExportService
{
    /**
     * Stream millions of records directly to HTTP response output.
     */
    public function streamUsersCsv(): StreamedResponse
    {
        return response()->streamDownload(function () {
            $handle = fopen('php://output', 'w');
            
            // Output CSV Headers
            fputcsv($handle, ['ID', 'Name', 'Email', 'Joined Date']);

            // cursor() utilizes PHP Generators: only 1 Eloquent model exists in memory at any instant
            $users = User::query()
                ->select(['id', 'name', 'email', 'created_at'])
                ->orderBy('id')
                ->cursor();

            foreach ($users as $user) {
                fputcsv($handle, [
                    $user->id,
                    $user->name,
                    $user->email,
                    $user->created_at->toISOString(),
                ]);
            }

            fclose($handle);
        }, 'users-export-' . now()->format('Y-m-d') . '.csv', [
            'Content-Type' => 'text/csv',
            'Cache-Control' => 'no-store, no-cache',
        ]);
    }
}

Step 5: Designing High-Throughput Composite Database Indexes

Writing optimized Eloquent queries is only half the battle. If the underlying database table lacks indexes matching your filter and sorting criteria, the database engine executes a Full Table Scan, reading every single row from disk.

flowchart TD
    subgraph WithoutIndex["❌ Without Composite Index"]
        Q1["SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC"]
        Q1 --> S1["Full Table Scan (1,000,000 Rows Scanned)"]
        S1 --> S2["FileSort in Memory / Disk Temp Table"]
        S2 --> S3["Query Time: 850 ms"]
    end

    subgraph WithIndex["✅ With Composite Index (user_id, status, created_at)"]
        Q2["SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC"]
        Q2 --> I1["B-Tree Index Seek (Exact Lookup)"]
        I1 --> I2["Zero Filesort (Pre-ordered Index Nodes)"]
        I2 --> I3["Query Time: 1.4 ms"]
    end

The Leftmost Prefix Rule

A database index works like a telephone directory sorted alphabetically. When creating a compound index on['user_id', 'status', 'created_at'], MySQL/PostgreSQL can efficiently use the index for queries filtering on:

1. user_id

However, the database cannot use this composite index if your query filters only by status or created_at.

Creating Production-Grade Migration Indexes

When designing migrations, index foreign keys and frequent query combinations:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            // Foreign Key index (Critical for eager loading JOINs and IN queries)
            $table->index('user_id');

            // Composite index for customer order history filtering & sorting
            // Order matters: Equality columns first (user_id, status), followed by sort column (created_at)
            $table->index(['user_id', 'status', 'created_at'], 'idx_orders_user_status_created');

            // Fast lookup for invoice reconciliation
            $table->index(['reference_number', 'status'], 'idx_orders_ref_status');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex(['user_id']);
            $table->dropIndex('idx_orders_user_status_created');
            $table->dropIndex('idx_orders_ref_status');
        });
    }
};

Step 6: Implementing Keyset and Cursor Pagination

Standard offset pagination ($query->paginate(20)) executes SQL containing LIMIT 20 OFFSET 50000.

To satisfy OFFSET 50000, the database engine must scan all 50,020 rows, discard the first 50,000, and return the final 20. As users navigate deeper into pages, response time degrades exponentially.

Replacing Offset withcursorPaginate()

Keyset pagination stores the last evaluated record's primary key or timestamp in an encoded base64 token and runs WHERE id < :last_seen_id ORDER BY id DESC LIMIT 20.

namespace App\Http\Controllers\Api\v1;

use App\Http\Controllers\Controller;
use App\Http\Resources\ActivityLogResource;
use App\Models\ActivityLog;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;

class ActivityLogController extends Controller
{
    /**
     * High-speed infinite-scroll feed using cursor pagination.
     */
    public function index(): AnonymousResourceCollection
    {
        $logs = ActivityLog::query()
            ->select(['id', 'user_id', 'action', 'ip_address', 'created_at'])
            ->with(['user:id,name'])
            ->latest('id') // Must be indexed!
            ->cursorPaginate(30);

        return ActivityLogResource::collection($logs);
    }
}

The response includes next_cursor and prev_cursor tokens. Every subsequent page execution takes 1 to 2 milliseconds, regardless of whether the user is on page 1 or page 5,000.

For architectural patterns on standardizing your API responses with cursor metadata, refer to our comprehensive guide on API Design Principles.

Step 7: Real-World Refactoring Case Study (Before and After)

Below is an actual production refactoring of a multi-tenant order report controller.

Before: The N+1 Heavy Implementation (Anti-Pattern)

// app/Http/Controllers/Api/ReportController.php (BEFORE)
namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Merchant;
use Illuminate\Http\JsonResponse;

class ReportController extends Controller
{
    public function merchantSummary(): JsonResponse
    {
        // 1. Single query to fetch 50 merchants
        $merchants = Merchant::where('is_active', true)->paginate(50);

        // 2. Loop triggers 4 extra queries PER merchant (200 extra queries!)
        $data = $merchants->map(function ($merchant) {
            return [
                'id' => $merchant->id,
                'name' => $merchant->business_name,
                // Triggers query: SELECT * FROM users WHERE id = ...
                'owner_name' => $merchant->owner->name,
                // Triggers query: SELECT * FROM orders WHERE merchant_id = ...
                'total_orders' => $merchant->orders()->count(),
                // Triggers query: SELECT * FROM orders WHERE merchant_id = ...
                'lifetime_revenue' => $merchant->orders()->where('status', 'completed')->sum('total_amount'),
                // Triggers query: SELECT * FROM subscriptions WHERE merchant_id = ...
                'active_plan' => $merchant->subscriptions()->where('is_active', true)->first()?->plan_name ?? 'Free',
            ];
        });

        return response()->json($data);
    }
}

After: Optimized Clean Architecture Service

// app/Services/MerchantReportService.php (AFTER)
namespace App\Services;

use App\Models\Merchant;
use App\Models\Subscription;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;

class MerchantReportService
{
    public function getPaginatedSummary(int $perPage = 50): LengthAwarePaginator
    {
        return Merchant::query()
            ->select([
                'id',
                'owner_user_id',
                'business_name',
                'created_at',
            ])
            ->with([
                'owner:id,name,email',
            ])
            ->withCount([
                'orders as total_orders',
            ])
            ->withSum([
                'orders as lifetime_revenue' => fn ($q) => $q->where('status', 'completed'),
            ], 'total_amount')
            ->addSelect([
                'active_plan' => Subscription::query()
                    ->select('plan_name')
                    ->whereColumn('subscriptions.merchant_id', 'merchants.id')
                    ->where('is_active', true)
                    ->latest('id')
                    ->limit(1),
            ])
            ->where('is_active', true)
            ->orderByDesc('id')
            ->paginate($perPage);
    }
}

// app/Http/Controllers/Api/ReportController.php (AFTER)
namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Http\Resources\MerchantSummaryResource;
use App\Services\MerchantReportService;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;

class ReportController extends Controller
{
    public function merchantSummary(MerchantReportService $reportService): AnonymousResourceCollection
    {
        $summary = $reportService->getPaginatedSummary(50);

        return MerchantSummaryResource::collection($summary);
    }
}

Benchmark Impact

Before: 201 Queries | 1,420 ms Response Time | 38.4 MB Memory
After:    2 Queries |    28 ms Response Time |  3.1 MB Memory

Common Gotchas & Troubleshooting

1. Missing Foreign Keys in Column Constraints

• Symptom:$order->user returns null even though the user exists in the database.

• Cause: Writing Order::with('user:name,email')->get() without including the primary key id.

• Fix: Always include the primary key and foreign key: Order::with('user:id,name,email')->get().

2. Using Dynamic Relationship Methods Instead of Properties Inside Loops

• Symptom: Eager loading was declared with with('comments'), but queries are still executing per iteration.

• Cause: Calling$post->comments()->where('is_approved', true)->get() inside the Blade template or API Resource executes a new SQL query on the relation method rather than reading the pre-loaded collection.

• Fix: Filter the already-loaded collection in memory:$post->comments->where('is_approved', true), or constrain the eager load upfront: Post::with(['comments' => fn($q) => $q->where('is_approved', true)]).

3. Missing Indexes on Soft Deletes (deleted_at)

• Symptom: Queries on tables using the SoftDeletes trait slow down as the table grows, even with foreign key indexes.

• Cause: Laravel automatically injects WHERE deleted_at IS NULL into every query. If your index only covers user_id, the database cannot perform an efficient index scan.

• Fix: Create compound indexes including deleted_at:$table->index(['user_id', 'deleted_at']).

FAQ

What is an N+1 query problem in Laravel Eloquent?

An N+1 query problem occurs when Laravel executes one initial query to fetch parent records (1), and then executes an additional query for each individual record (N) to retrieve a related model, resulting in N+1 database roundtrips that degrade server response times.

How do you completely disable lazy loading in Laravel?

Add Model::preventLazyLoading(!app()->isProduction()) inside your AppServiceProviderboot() method. This throws a LazyLoadingViolationException during development and testing whenever un-eager-loaded relations are accessed.

Why does with('relation:name') return null relations in Laravel?

When restricting columns in eager loading constraints, you must always include the foreign key and primary key (e.g., with('user:id,name,role')). If the foreign key connecting the parent and child models is omitted, Eloquent cannot match the hydrated records.

What is the difference between chunk() and chunkById() in Laravel?

chunk() uses offset pagination, which skips or reprocesses rows if you update or delete records inside the callback. chunkById() uses keyset pagination based on the primary key, ensuring consistent chunking without skipping records or causing memory spikes.

When should you use cursorPaginate() instead of paginate()?

Use cursorPaginate() for large datasets (tens of thousands to millions of rows) and infinite scrolling feeds. It replaces high-offset queries (OFFSET 50000) with fast indexed WHERE id > X conditions, maintaining constant query execution time.

Next Steps & Related Architecture Guides

To continue upgrading your Laravel application's performance and architecture, explore these guides:

• 3 Laravel Mistakes That Break Production (And Fixes): Learn how to prevent deadlocks from network calls inside database transactions and avoid.env caching traps.

• REST API Design Principles for Scalable Applications: Best practices for versioning, pagination structures, error handling, and idempotent endpoints.

• Laravel Dynamic SMTP Mail Configuration: How to configure multi-tenant and runtime mailers dynamically with custom encryption and failover handling.