Marcc Atayde

Posted on Jun 9

Database Optimization Techniques for High-Traffic Laravel Applications

#laravel #database #performance #php

Your Laravel app works great in development. A few hundred users hit it in production and suddenly query times balloon, your server sweats, and you're staring at a Telescope dashboard wondering what went wrong. Database performance issues are rarely dramatic at first — they creep in quietly, then compound fast.

This article covers the practical database optimization techniques that actually move the needle for high-traffic Laravel applications: the ones you can implement today, measure immediately, and rely on in production.

1. Eliminate N+1 Queries Before They Kill You

The N+1 problem is the silent killer of Laravel performance. It happens when you load a collection and then trigger an additional query per item in a loop.

// ❌ N+1 Problem — fires 1 + N queries
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // separate query per post
}

// ✅ Eager Loading — fires 2 queries total
$posts = Post::with('author')->get();
foreach ($posts as $post) {
    echo $post->author->name;
}

For nested relationships, chain them:

$posts = Post::with(['author', 'comments.user', 'tags'])->paginate(20);

Enable query logging in development to catch these early:

\DB::enableQueryLog();
// ... run your code
dd(\DB::getQueryLog());

Or better yet, use Laravel Telescope or Debugbar on your local environment — both flag N+1 issues automatically.

2. Index Strategically, Not Blindly

Indexes speed up reads but slow down writes. The goal is to index columns you actually filter and sort by — not every column.

// In a migration
Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');                    // foreign key lookups
    $table->index('status');                     // frequent WHERE clause
    $table->index(['user_id', 'created_at']);    // composite index for user history queries
});

Composite Index Column Order Matters

MySQL uses indexes left-to-right. If you frequently query WHERE user_id = ? AND status = ?, put user_id first in the composite index — it has higher cardinality and will narrow the result set faster.

To confirm your indexes are being used, prefix any slow query with EXPLAIN:

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

If you see type: ALL in the output, MySQL is doing a full table scan — your index isn't being used.

3. Use Query Chunking and Lazy Collections for Bulk Operations

Processing thousands of rows at once will exhaust memory. Laravel gives you two elegant solutions.

// chunk() — processes records in batches
User::where('subscribed', true)->chunk(500, function ($users) {
    foreach ($users as $user) {
        // process each user
    }
});

// lazyById() — uses a cursor for memory-efficient streaming
User::where('subscribed', true)->lazyById()->each(function ($user) {
    // process one at a time, low memory footprint
});

For bulk inserts, avoid looping create() calls. Use insert() to send a single query:

// ❌ Fires 1000 separate INSERT queries
foreach ($records as $record) {
    Order::create($record);
}

// ✅ Single INSERT with multiple rows
Order::insert($records); // $records is an array of arrays

4. Cache Aggressively at the Right Layer

Not all data needs to hit the database on every request. Laravel's cache layer is your best friend here.

// Cache an expensive aggregation for 10 minutes
$stats = Cache::remember('dashboard:stats', 600, function () {
    return Order::selectRaw('
        COUNT(*) as total_orders,
        SUM(amount) as revenue,
        AVG(amount) as avg_order_value
    ')->whereMonth('created_at', now()->month)->first();
});

For per-user data, namespace your cache keys:

$key = "user:{$userId}:order_summary";
$summary = Cache::remember($key, 300, fn() => $this->buildSummary($userId));

Use Redis as your cache driver in production — it's orders of magnitude faster than the database or file cache, and it supports atomic operations, pub/sub, and TTL natively.

CACHE_DRIVER=redis
REDIS_HOST=127.0.0.1
REDIS_PORT=6379

5. Select Only What You Need

This one is simple but often ignored. SELECT * pulls every column, including large text fields, JSON blobs, and timestamps you don't need.

// ❌ Fetches every column, including bio, avatar_url, settings JSON...
$users = User::all();

// ✅ Only fetch what the view actually uses
$users = User::select('id', 'name', 'email')->get();

This reduces memory usage and network overhead between your database and PHP process — especially noticeable with large datasets.

6. Optimize Pagination for Large Tables

Standard paginate() uses a COUNT(*) query under the hood. On tables with millions of rows, this becomes expensive.

For infinite scroll or "load more" UIs, use cursorPaginate() instead:

// Standard pagination — expensive COUNT on large tables
$posts = Post::latest()->paginate(20);

// Cursor pagination — no COUNT, extremely efficient
$posts = Post::latest()->cursorPaginate(20);

Cursor pagination uses an encoded pointer to the last record rather than an OFFSET, which means performance stays consistent regardless of how deep you paginate.

7. Use Database Read Replicas for Read-Heavy Workloads

If your application is read-heavy (most are), routing read queries to a replica and writes to the primary can dramatically reduce load on your main database server.

Laravel supports this natively in config/database.php:

'mysql' => [
    'read' => [
        'host' => ['192.168.1.2', '192.168.1.3'], // replica IPs
    ],
    'write' => [
        'host' => ['192.168.1.1'], // primary
    ],
    'sticky' => true, // ensure writes are immediately readable
    'driver' => 'mysql',
    // ... other config
],

With sticky => true, any write during the current request will be read back from the primary — avoiding the replication lag gotcha.

8. Profile First, Optimize Second

Before touching anything in production, measure. Gut feelings about bottlenecks are often wrong.

Tools worth using:

Laravel Telescope — query time, slow queries, N+1 detection
Laravel Pulse — aggregated performance data across your app
Clockwork — browser devtools-style profiling for PHP
MySQL Slow Query Log — catches queries over a configurable threshold

Enable the slow query log in MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries taking over 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Then use mysqldumpslow or pt-query-digest from Percona Toolkit to analyze the output.

This kind of profiling-first approach is something we apply consistently when auditing client applications at HanzWeb Agency — the slowest queries are rarely the ones developers suspect.

Conclusion

Database optimization isn't a one-time task — it's an ongoing discipline. The most impactful starting points are almost always the same: fix your N+1 queries, add the right indexes, and cache expensive reads with Redis. From there, cursor pagination, selective columns, and read replicas become your next levers as traffic scales.

Measure before you optimize, use Laravel's built-in tooling to surface the real bottlenecks, and build the habit of reviewing query logs as part of your deployment process. Your database will thank you — and so will your users.

DEV Community