Laravel Database Performance Optimization — Complete Guide (2026)

Laravel Database Performance Optimization — Complete Guide (2026)

A client called me because their admin panel was timing out. Every page load was 30 seconds or more. Users were complaining. The server was under constant load.

I opened Laravel Telescope and looked at the orders list endpoint. It was firing 203 database queries on every single page load.

The fix took eleven minutes. The page now loads in 180ms.

Database optimization is not magic. A 30-second page load becomes 200ms. A crashing server becomes stable. Start with the N+1 problem — that is usually the biggest issue. Then move to selective column loading and caching. Indexes come next. Laravel

This guide covers every technique I use, in the order I use them, with production code examples throughout.

Step 0 — Measure Before You Optimize

You cannot fix what you cannot see. Before touching a single line of code, set up proper profiling.

Laravel Telescope — Development Profiling

bash

composer require laravel/telescope --dev
php artisan telescope:install
php artisan migrate

Telescope shows every query fired per request, with execution time, bindings, and the code that triggered it. Install it first. Optimize second.

Laravel Debugbar — Inline Query Inspection

bash

composer require barryvdh/laravel-debugbar --dev

Debugbar adds a toolbar to every page showing query count, execution time, and memory usage inline in the browser. For spotting N+1 problems during development, nothing is faster.

Query Logging — Find Problems in Production

php

// Temporarily enable in production to diagnose a specific endpoint
DB::enableQueryLog();

// ... run your code ...

$queries = DB::getQueryLog();
\Log::info('Queries fired', [
    'count' => count($queries),
    'total_time' => array_sum(array_column($queries, 'time')),
    'queries' => $queries,
]);

DB::disableQueryLog();

1. Fix the N+1 Query Problem — The Biggest Win

This is the single most common performance issue in Laravel apps. It happens when you load a collection, then access a relationship in a loop. With 10 posts, you run 11 queries. With 100 posts, you run 101 queries. I have seen this change turn a 5-second page load into 200ms. Laravel

php

// PROBLEM — N+1 queries
$orders = Order::latest()->paginate(50);

// In the view or resource:
foreach ($orders as $order) {
    echo $order->user->name;     // Query 1 for each order
    echo $order->product->name;  // Query 2 for each order
    echo $order->status->label;  // Query 3 for each order
}
// Result: 1 + (50 × 3) = 151 queries for 50 rows

php

// SOLUTION — eager loading
$orders = Order::with(['user', 'product', 'status'])
    ->latest()
    ->paginate(50);

// In the view: same code, zero extra queries
// Result: 4 queries total. Always.

Nested Eager Loading

php

// Load nested relationships in one shot
$posts = Post::with([
    'author.profile',       // author AND their profile
    'comments.user',        // comments AND each commenter
    'tags',
    'category.parent',
])->paginate(20);

Conditional Eager Loading

php

// Load relationship only if not already loaded
$post->loadMissing('comments');

// Load with constraints
$post->load(['comments' => function ($query) {
    $query->where('approved', true)
          ->latest()
          ->limit(10);
}]);

Prevent Lazy Loading Globally

php

// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

public function boot(): void
{
    // Throws exception when lazy loading in non-production environments
    // Forces developers to eager load everything
    Model::preventLazyLoading(!app()->isProduction());
}

This single line forces every developer on the team to write eager-loaded queries. N+1 problems become exceptions during development instead of production incidents.

2. Select Only the Columns You Need

Every column you select travels from the database server to your PHP process. On tables with 30+ columns and hundreds of thousands of rows, this adds up fast.

php

// PROBLEM — loads all 40 columns, most unused
$users = User::all();

// SOLUTION — select only what you need
$users = User::select(['id', 'name', 'email', 'avatar_url'])
    ->get();

// With relationships
$posts = Post::select(['id', 'title', 'slug', 'published_at', 'user_id'])
    ->with(['author:id,name,avatar_url'])  // specify columns in with() too
    ->paginate(20);

php

// For existence checks — never load the full record
// PROBLEM
$exists = User::where('email', $email)->first() !== null;  // loads entire row

// SOLUTION
$exists = User::where('email', $email)->exists();  // single optimized query

// For counting
$count = Order::where('status', 'pending')->count();  // not ->get()->count()

// For aggregates — use the database, not PHP
$total = Order::where('user_id', $userId)->sum('amount');
$average = Product::where('category_id', $id)->avg('price');

3. Database Indexing — The Permanent Fix

Ensure all columns used in WHERE, JOIN, or ORDER BY clauses are indexed. CopyProgramming

A missing index on a filtered column does a full table scan. On a million-row table that is a 2-3 second query that should take 5 milliseconds.

php

// Migration — adding indexes properly
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();  // auto-indexed
    $table->foreignId('product_id')->constrained(); // auto-indexed
    $table->string('status');
    $table->decimal('amount', 10, 2);
    $table->timestamp('created_at')->nullable();
    $table->timestamp('updated_at')->nullable();

    // Index columns you filter by
    $table->index('status');
    $table->index('created_at');

    // Composite index for common query patterns
    // Matches: WHERE user_id = ? AND status = ?
    $table->index(['user_id', 'status']);

    // Composite with order: WHERE status = ? ORDER BY created_at DESC
    $table->index(['status', 'created_at']);
});

When to Use Composite Indexes

php

// This query benefits from index(['user_id', 'status', 'created_at'])
$orders = Order::where('user_id', $userId)
    ->where('status', 'completed')
    ->orderBy('created_at', 'desc')
    ->get();

Rule: the leftmost columns in a composite index must match your WHERE clause columns. The ORDER BY column goes last.

Check Slow Queries in PostgreSQL

sql

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '100';  -- log queries > 100ms

-- Check which queries need indexes
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1 AND status = 'pending'
ORDER BY created_at DESC;

-- Look for "Seq Scan" — that means no index is being used
-- "Index Scan" means your index is working

sql

-- MySQL equivalent
EXPLAIN SELECT * FROM orders
WHERE user_id = 1 AND status = 'pending'
ORDER BY created_at DESC;
-- Look for "ALL" in the type column — that means full table scan

4. Handle Large Datasets — chunk() and cursor()

Loading 50,000 records into memory to process them is how you crash a server.

php

// PROBLEM — loads everything into memory at once
$users = User::where('active', true)->get();  // 50,000 User objects in RAM
foreach ($users as $user) {
    Mail::to($user)->send(new WeeklyDigest());
}

// SOLUTION 1 — chunk() for operations with database writes
User::where('active', true)->chunk(500, function ($users) {
    foreach ($users as $user) {
        Mail::to($user)->queue(new WeeklyDigest());
    }
});
// Processes 500 at a time. Memory stays constant.

// SOLUTION 2 — cursor() for read-only operations on huge datasets
// Uses PHP generators — one record in memory at a time
foreach (User::where('active', true)->cursor() as $user) {
    $this->processUser($user);
}
// Minimum possible memory usage

php

// chunk() vs cursor() — when to use which
// chunk() : you need the full collection methods ($users->pluck, etc.)
//            you are writing to the database inside the loop
//            you need chunk-level transactions

// cursor() : read-only processing
//            maximum memory efficiency
//            simple per-record operations

5. Bulk Operations — Stop Inserting One at a Time

php

// PROBLEM — 1,000 queries for 1,000 records
foreach ($importedItems as $item) {
    Product::create([
        'name'       => $item['name'],
        'price'      => $item['price'],
        'category_id' => $item['category_id'],
    ]);
}

// SOLUTION — one query
Product::insert($items);
// Prepare your array first, then insert in one shot

// Bulk insert with timestamps
$now = now();
$items = array_map(fn($item) => array_merge($item, [
    'created_at' => $now,
    'updated_at' => $now,
]), $importedItems);

Product::insert($items);

php

// upsert() — insert or update in one query (Laravel 8+)
// Perfect for sync operations and imports
Product::upsert(
    $items,                    // records to insert/update
    ['sku'],                   // unique key to check
    ['name', 'price', 'stock'] // columns to update on conflict
);

// updateOrCreate() — use only when you need the model back
// For large datasets, upsert() is dramatically faster
$product = Product::updateOrCreate(
    ['sku' => $sku],
    ['name' => $name, 'price' => $price]
);

6. Query Caching with Redis

Laravel performance is optimized at three levels: database (eager loading, query optimization, caching), application (route caching, config caching, queue usage for background tasks), and infrastructure (OPcache, CDN, Redis for session and cache). Boundev

php

// Cache data that rarely changes
// The same complex query running 10,000 times per minute
// on data that changes once per hour is wasteful

// Basic query caching
$categories = Cache::remember('all_categories', 3600, function () {
    return Category::with('children')
        ->whereNull('parent_id')
        ->orderBy('sort_order')
        ->get();
});

// User-specific caching
$stats = Cache::remember(
    "user_stats_{$userId}",
    1800,  // 30 minutes
    function () use ($userId) {
        return [
            'total_orders'   => Order::where('user_id', $userId)->count(),
            'total_spent'    => Order::where('user_id', $userId)->sum('amount'),
            'pending_orders' => Order::where('user_id', $userId)
                                     ->where('status', 'pending')
                                     ->count(),
        ];
    }
);

// Invalidate cache when data changes
public function update(UpdateOrderRequest $request, Order $order): JsonResponse
{
    $order->update($request->validated());

    // Clear related caches
    Cache::forget("user_stats_{$order->user_id}");
    Cache::forget('dashboard_stats');
    Cache::tags(['orders'])->flush();  // If using Redis tags

    return new OrderResource($order);
}

php

// Cache tags for grouped invalidation (Redis only)
$orders = Cache::tags(['orders', "user_{$userId}"])
    ->remember("user_{$userId}_orders", 1800, function () use ($userId) {
        return Order::with(['product', 'status'])
            ->where('user_id', $userId)
            ->latest()
            ->paginate(20);
    });

// Invalidate all order caches at once
Cache::tags(['orders'])->flush();

7. Database Transactions — Performance and Safety Together

php

// Use transactions for multi-step operations
// They also improve performance for bulk writes

// PROBLEM — multiple separate queries, inconsistent state if one fails
public function processOrder(array $data): Order
{
    $order = Order::create($data);
    $this->updateInventory($order);
    $this->createInvoice($order);
    $this->notifyWarehouse($order);
    return $order;
}

// SOLUTION — wrapped in a transaction
public function processOrder(array $data): Order
{
    return DB::transaction(function () use ($data) {
        $order = Order::create($data);
        $this->updateInventory($order);
        $this->createInvoice($order);
        $this->notifyWarehouse($order);
        return $order;
    });
}

// Bulk inserts inside a transaction are significantly faster
DB::transaction(function () use ($items) {
    foreach (array_chunk($items, 500) as $chunk) {
        Product::insert($chunk);
    }
});

8. Pagination — Never Use ->all() on Large Tables

php

// PROBLEM — loads everything
$products = Product::all();  // Never do this in production

// Standard pagination — use for most cases
$products = Product::with(['category', 'images'])
    ->where('active', true)
    ->orderBy('created_at', 'desc')
    ->paginate(20);  // Returns LengthAwarePaginator with total count

// Simple pagination — faster (no COUNT query)
$products = Product::where('active', true)
    ->simplePaginate(20);  // Returns Paginator, no total count

// Cursor pagination — fastest for large datasets (no offset)
// Best for infinite scroll and large tables
$products = Product::where('active', true)
    ->cursorPaginate(20);  // Uses cursor-based navigation, no OFFSET

Performance difference on a 1 million row table:

• paginate() — requires COUNT(*) query, slower but gives total pages
• simplePaginate() — no COUNT, just next/prev, faster
• cursorPaginate() — fastest, no OFFSET scan, best for high-traffic APIs

9. Raw Queries for Complex Operations

Sometimes Eloquent is the wrong tool. For bulk updates, complex aggregations, and operations on millions of rows — raw SQL is faster and clearer.

php

// Bulk update without loading models
DB::statement(
    'UPDATE users SET subscription_status = ? WHERE trial_ends_at < ?',
    ['expired', now()]
);

// Complex aggregation — faster in SQL than PHP
$monthlyRevenue = DB::select('
    SELECT
        DATE_FORMAT(created_at, "%Y-%m") as month,
        SUM(amount) as revenue,
        COUNT(*) as order_count,
        AVG(amount) as avg_order_value
    FROM orders
    WHERE status = "completed"
        AND created_at >= ?
    GROUP BY DATE_FORMAT(created_at, "%Y-%m")
    ORDER BY month DESC
    LIMIT 12
', [now()->subMonths(12)]);

// Use selectRaw for expressions within Eloquent
$products = Product::selectRaw('
    id,
    name,
    price,
    price * 1.18 as price_with_tax,
    (SELECT COUNT(*) FROM order_items WHERE product_id = products.id) as total_sold
')
->where('active', true)
->get();

10. Read/Write Database Splitting

For high-traffic applications, configure Laravel to send writes to the primary database and reads to replicas.

php

// config/database.php
'mysql' => [
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', '127.0.0.1'),
            env('DB_READ_HOST_2', '127.0.0.1'),  // Multiple replicas
        ],
    ],
    'write' => [
        'host' => [env('DB_WRITE_HOST', '127.0.0.1')],
    ],
    'sticky'   => true,  // Read your own writes in same request
    'driver'   => 'mysql',
    'port'     => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', ''),
    'username' => env('DB_USERNAME', ''),
    'password' => env('DB_PASSWORD', ''),
    'charset'  => 'utf8mb4',
],

php

// Force a specific connection when needed
// After a write, sometimes you need to read from primary
$freshOrder = Order::on('mysql::write')->find($orderId);

// Or use the sticky option (already configured above)
// sticky: true means if you wrote in this request,
// reads also go to primary for the rest of that request

11. Laravel 13 — Prepared Statement Caching

Laravel 13 introduced prepared statement caching in the query builder. This is automatic — no configuration needed.

php

// This query type is now cached as a prepared statement
// Second identical structure reuses the statement handle
$user1 = User::find(1);  // Prepares statement
$user2 = User::find(2);  // Reuses cached statement — faster
$user3 = User::find(3);  // Reuses again

Laravel performance is optimized at multiple levels. Commands like config:cache, route:cache, and view:cache can reduce response time by 20-50% as Laravel does not have to parse configuration files for each request. Boundev

12. Production Deployment Optimization Commands

bash

#!/bin/bash
# deploy.sh — run after every deployment

# Cache all the things
php artisan config:cache    # Merge all config files into one
php artisan route:cache     # Compile routes into a single file
php artisan view:cache      # Pre-compile all Blade templates
php artisan event:cache     # Cache event-listener mappings

# Optimize Composer autoloader
composer install --optimize-autoloader --no-dev

# These four commands together can reduce response time 20-50%

Complete Database Performance Checklist

Queries

• Zero N+1 queries — Model::preventLazyLoading() in AppServiceProvider
• select() on all queries that don't need every column
• exists() instead of ->first() !== null
• count(), sum(), avg() in the database, not PHP
• chunk() or cursor() for processing large datasets
• insert() or upsert() for bulk operations — never create in a loop

Indexing

• All foreign keys indexed (Laravel does this automatically with constrained())
• All columns used in WHERE clauses indexed
• All columns used in ORDER BY indexed
• Composite indexes for common query patterns
• EXPLAIN used to verify indexes are being hit

Caching

• Redis configured as cache driver
• Frequently accessed, rarely changing data cached
• Cache invalidation strategy defined for each cached value
• Cache tags used for grouped invalidation where needed

Architecture

• Database transactions for multi-step write operations
• cursorPaginate() for high-traffic list endpoints
• Read replicas configured for high-traffic applications
• Query logging in place to catch new N+1 problems

Monitoring

• Laravel Telescope installed in development
• Slow query logging enabled in production
• Alerts on query count per request exceeding threshold

Wrapping Up

Database optimization is not magic. It is about understanding what your application is actually asking the database to do — and making sure that work is as efficient as possible. Start with the N+1 problem. That is usually the biggest issue. Then selective columns. Then indexes. Then caching. Laravel

The patterns above are not theoretical. Every one of them has turned a slow production application into a fast one. The 30-second admin panel that became 180ms was a real client project. The fix was always in this list somewhere.

Profile first. Identify the actual bottleneck. Fix it with the appropriate technique. Measure again. Repeat.

That is database optimization. Not magic — just discipline applied consistently.

Tushar Modi — Full Stack Developer, Jaipur tusharmodi.in