MySQL Query Optimization in Laravel: Advanced Techniques

Database performance is one of the biggest factors in Laravel application speed. Slow queries drain server resources and frustrate users. In this guide I'll share the optimisation techniques I've used in production — including fixing N+1 bugs in a real payroll system that reduced query counts by over 80%.

The N+1 Query Problem

The most common performance killer in Laravel. It happens when you load a collection and then trigger a new query for each item inside a loop:

// BAD: N+1 — 1 query for users + 1 per user for profile
$users = User::all();
foreach ($users as $user) {
    echo $user->profile->bio;
}

// GOOD: 2 queries total regardless of user count
$users = User::with('profile')->get();
foreach ($users as $user) {
    echo $user->profile->bio;
}

Query Optimisation Techniques

1. Select Only What You Need

// Loads all columns — wasteful
$users = User::all();

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

2. Add Database Indexes

Indexes are the single highest-impact optimisation for read-heavy queries. Add them to any column you filter or sort by frequently:

// In your migration
Schema::table('users', function (Blueprint $table) {
    $table->index('email');
    $table->index(['status', 'created_at']); // Composite index
});

3. Cache Expensive Queries

$popularPosts = Cache::remember('popular_posts', 3600, function () {
    return Post::where('views', '>', 1000)
               ->orderBy('views', 'desc')
               ->take(10)
               ->get();
});

Handling Large Datasets

Chunk Large Collections

// Processes 1000 records at a time — avoids memory exhaustion
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process each user
    }
});

Use Lazy Collections

// Streams records one at a time — lowest memory footprint
User::lazy()->each(function ($user) {
    // Process without loading all records into memory
});

Monitoring Query Performance

Use Laravel Telescope in development or enable query logging to find slow queries:

DB::enableQueryLog();

// ... run your code ...

$queries = DB::getQueryLog();
dd($queries); // Shows every query, its bindings, and execution time

                    Pro tip: Always test with realistic data volumes. A query that runs fine against 500 rows in development can take 30 seconds against 500,000 rows in production. Use database seeders to generate realistic test data.
                

Conclusion

Start with N+1 fixes and eager loading — these are the lowest-effort, highest-impact changes. Then add indexes to frequently queried columns and introduce caching for expensive reads. Profile before and after each change so you know what's actually making a difference.