Database Optimization Techniques for Laravel Applications

Indexing Strategies

Proper indexing is the foundation of database performance. Here's how to identify what to index:

<?php

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) {
            // Single column index for frequent lookups
            $table->index('status');

            // Composite index for queries that filter by multiple columns
            $table->index(['user_id', 'status', 'created_at']);

            // Unique index for constraints
            $table->unique(['user_id', 'order_number']);
        });
    }
};

Query Optimization

Use Laravel Debugbar or Telescope to identify slow queries. Common optimizations include:

• Select only needed columns
• Use chunking for large datasets
• Avoid N+1 queries with eager loading

<?php

// Bad - Selects all columns
$users = User::all();

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

// Bad - Memory issues with large datasets
$users = User::all();
foreach ($users as $user) {
    $this->processUser($user);
}

// Good - Chunking for large datasets
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        $this->processUser($user);
    }
});

// Even better - Lazy collections (no memory issues)
User::lazy()->each(function ($user) {
    $this->processUser($user);
});

Redis Caching

Cache frequently accessed data:

<?php

namespace App\Services;

use App\Models\User;
use Illuminate\Support\Facades\Cache;

class UserService
{
    public function getActiveUsers()
    {
        return Cache::remember('users.active', 3600, function () {
            return User::where('active', true)
                ->with('roles')
                ->get();
        });
    }

    public function getUser(int $id): User
    {
        return Cache::remember("user.{$id}", 3600, function () use ($id) {
            return User::with(['roles', 'permissions'])->findOrFail($id);
        });
    }

    public function updateUser(int $id, array $data): User
    {
        $user = User::findOrFail($id);
        $user->update($data);

        // Invalidate cache
        Cache::forget("user.{$id}");
        Cache::forget('users.active');

        return $user->fresh();
    }
}

Database Query Caching

Cache expensive query results at the query level:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Cache;

class Post extends Model
{
    public static function getPopular(int $limit = 10)
    {
        return Cache::tags(['posts'])->remember(
            "posts.popular.{$limit}",
            now()->addHour(),
            fn () => static::query()
                ->withCount('views')
                ->orderByDesc('views_count')
                ->limit($limit)
                ->get()
        );
    }

    protected static function booted(): void
    {
        static::saved(fn () => Cache::tags(['posts'])->flush());
        static::deleted(fn () => Cache::tags(['posts'])->flush());
    }
}

Conclusion

Database optimization is crucial for application performance. Always profile your queries, use appropriate indexes, and implement caching strategies for frequently accessed data.

Written by Abdo Shrief Senior Laravel Developer based in Cairo.
https://www.abdoshrief.tech