Laravel Framework

Database Optimization & Performance

18 min Lesson 34 of 45

Introduction to Database Optimization

Database performance is crucial for application speed and scalability. As your application grows, inefficient database queries can become a major bottleneck. This lesson covers essential optimization techniques including solving the N+1 problem, eager loading, indexing, query optimization, caching, and proper use of database transactions.

Why Database Optimization Matters:

Reduces page load time and improves user experience
Lowers server resource consumption (CPU, memory, I/O)
Allows application to handle more concurrent users
Reduces database server costs and infrastructure needs
Improves SEO through faster page speeds
Prevents timeouts and errors under heavy load

The N+1 Query Problem

The N+1 problem occurs when you execute one query to fetch a collection, then execute N additional queries (one for each item) to fetch related data. This is one of the most common performance issues in Laravel applications.

Example of N+1 Problem:

<?php
// BAD: N+1 Problem - 101 queries for 100 posts
$posts = Post::all(); // 1 query

foreach ($posts as $post) {
    echo $post->user->name; // 100 additional queries (1 per post)
}

Performance Impact: If you have 100 posts, this code executes 101 database queries (1 for posts + 100 for users). With 1000 posts, it becomes 1001 queries!

Solution: Eager Loading

<?php
// GOOD: Eager Loading - Only 2 queries
$posts = Post::with('user')->get(); // 2 queries total

foreach ($posts as $post) {
    echo $post->user->name; // No additional queries
}

// Multiple relationships
$posts = Post::with(['user', 'comments', 'tags'])->get();

// Nested relationships
$posts = Post::with(['comments.user', 'user.profile'])->get();

// Conditional eager loading
$posts = Post::with([
    'comments' => function ($query) {
        $query->where('approved', true)->orderBy('created_at', 'desc');
    }
])->get();

Lazy Eager Loading

Load relationships after the parent model has already been retrieved:

<?php
$posts = Post::all();

// Realize you need users later
$posts->load('user');

// Load multiple relationships
$posts->load(['user', 'comments']);

// Conditional lazy eager loading
if ($includeComments) {
    $posts->load('comments');
}

Detecting N+1 Problems

<?php
// Install Laravel Debugbar
composer require barryvdh/laravel-debugbar --dev

// Or use query logging
\Illuminate\Support\Facades\DB::enableQueryLog();

// Your code here
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name;
}

// Check executed queries
$queries = \Illuminate\Support\Facades\DB::getQueryLog();
dd($queries); // See all queries executed

Prevention: Enable preventLazyLoading() in development to catch N+1 problems early:

// app/Providers/AppServiceProvider.php
public function boot()
{
    Model::preventLazyLoading(!app()->isProduction());
}

Database Indexing

Database indexes dramatically speed up data retrieval but slow down writes. Think of indexes like a book's index - they help you find information quickly without reading every page.

When to Add Indexes:

Foreign keys (user_id, post_id, etc.)
Columns used in WHERE clauses frequently
Columns used in ORDER BY clauses
Columns used in JOIN operations
Unique constraints (email, username)

<?php
// Create migration with indexes
php artisan make:migration add_indexes_to_posts_table

// In migration file
public function up()
{
    Schema::table('posts', function (Blueprint $table) {
        // Index single column
        $table->index('user_id');
        $table->index('status');
        $table->index('published_at');

        // Composite index (multiple columns)
        $table->index(['user_id', 'status']);

        // Unique index
        $table->unique('slug');

        // Full-text index (for searching)
        $table->fullText(['title', 'content']);
    });
}

public function down()
{
    Schema::table('posts', function (Blueprint $table) {
        $table->dropIndex(['user_id']);
        $table->dropIndex(['status']);
        $table->dropIndex(['published_at']);
        $table->dropIndex(['user_id', 'status']);
        $table->dropUnique(['slug']);
        $table->dropFullText(['title', 'content']);
    });
}

<?php
// Use full-text search with index
$posts = Post::whereFullText(['title', 'content'], 'Laravel tutorial')
    ->get();

// Without full-text index (much slower)
$posts = Post::where('title', 'like', '%Laravel%')
    ->orWhere('content', 'like', '%Laravel%')
    ->get();

Index Trade-offs: While indexes speed up reads, they slow down INSERT/UPDATE/DELETE operations because the index must be updated too. Don't over-index - only add indexes for columns you actually query frequently.

Query Optimization

Select Only Needed Columns

<?php
// BAD: Fetches all columns
$users = User::all();

// GOOD: Fetch only needed columns
$users = User::select('id', 'name', 'email')->get();

// In relationships
$posts = Post::with(['user:id,name,email'])->get();

Use Chunking for Large Datasets

<?php
// BAD: Loads all records into memory at once
$users = User::all(); // May cause memory issues with 100k+ records
foreach ($users as $user) {
    // Process user
}

// GOOD: Process in chunks
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

// Even better: Use lazy collections for memory efficiency
User::lazy()->each(function ($user) {
    // Process user one at a time
});

Count Efficiently

<?php
// BAD: Loads all records just to count
$count = Post::all()->count();

// GOOD: Database count (much faster)
$count = Post::count();

// Existence checks
if (Post::where('user_id', $userId)->exists()) {
    // User has posts
}

// Instead of
if (Post::where('user_id', $userId)->count() > 0) {
    // User has posts
}

Avoid SELECT N+1 with Aggregates

<?php
// BAD: N+1 for counting
$users = User::all();
foreach ($users as $user) {
    echo $user->posts()->count(); // N queries
}

// GOOD: Use withCount()
$users = User::withCount('posts')->get();
foreach ($users as $user) {
    echo $user->posts_count; // No additional queries
}

// Multiple counts
$users = User::withCount(['posts', 'comments', 'likes'])->get();

// Conditional count
$users = User::withCount([
    'posts',
    'posts as published_posts_count' => function ($query) {
        $query->where('status', 'published');
    }
])->get();

Query Caching

Cache expensive query results to avoid hitting the database repeatedly:

<?php
use Illuminate\Support\Facades\Cache;

// Cache query results for 1 hour
$posts = Cache::remember('popular_posts', 3600, function () {
    return Post::where('views', '>', 1000)
        ->with(['user', 'comments'])
        ->orderBy('views', 'desc')
        ->limit(10)
        ->get();
});

// Cache with tags (for easier clearing)
$posts = Cache::tags(['posts'])->remember('popular_posts', 3600, function () {
    return Post::popular()->get();
});

// Clear tagged cache
Cache::tags(['posts'])->flush();

// Cache forever (until manually cleared)
$stats = Cache::rememberForever('site_stats', function () {
    return [
        'total_users' => User::count(),
        'total_posts' => Post::count(),
    ];
});

// Clear specific cache
Cache::forget('popular_posts');

Model Caching Pattern

<?php
// app/Models/Post.php
class Post extends Model
{
    public static function popular()
    {
        return Cache::remember('posts:popular', 3600, function () {
            return static::where('views', '>', 1000)
                ->orderBy('views', 'desc')
                ->limit(10)
                ->get();
        });
    }

    // Clear cache when model is saved
    protected static function booted()
    {
        static::saved(function () {
            Cache::forget('posts:popular');
        });

        static::deleted(function () {
            Cache::forget('posts:popular');
        });
    }
}

Database Transactions

Transactions ensure data integrity by grouping multiple database operations into a single atomic unit:

<?php
use Illuminate\Support\Facades\DB;

// Automatic transaction (recommended)
DB::transaction(function () {
    $user = User::create(['name' => 'John', 'email' => 'john@example.com']);

    $user->profile()->create(['bio' => 'Developer']);

    $user->roles()->attach(1);

    // If any operation fails, all will be rolled back
});

// Manual transaction
DB::beginTransaction();

try {
    $order = Order::create([
        'user_id' => $userId,
        'total' => $total,
    ]);

    foreach ($items as $item) {
        $order->items()->create($item);
    }

    Inventory::decrement($item['product_id'], $item['quantity']);

    DB::commit(); // Commit if everything succeeds

} catch (\Exception $e) {
    DB::rollBack(); // Rollback on error

    throw $e;
}

Transaction Best Practices:

Keep transactions short - don't include external API calls
Don't nest transactions unnecessarily
Use transactions for operations that must succeed or fail together
Transactions lock database rows - minimize lock time

Pagination Best Practices

<?php
// Standard pagination
$posts = Post::paginate(20); // 20 per page

// Simple pagination (faster, no total count)
$posts = Post::simplePaginate(20);

// Cursor pagination (best for large datasets)
$posts = Post::cursorPaginate(20);

// In controller
public function index()
{
    $posts = Post::with('user')
        ->latest()
        ->paginate(20);

    return view('posts.index', compact('posts'));
}

// In view
{{ $posts->links() }}

// Custom pagination view
{{ $posts->links('pagination.custom') }}

Query Scopes for Reusability

<?php
// app/Models/Post.php
class Post extends Model
{
    // Local scope
    public function scopePublished($query)
    {
        return $query->where('status', 'published')
            ->where('published_at', '<=', now());
    }

    public function scopePopular($query, $threshold = 1000)
    {
        return $query->where('views', '>=', $threshold);
    }

    public function scopeByAuthor($query, $userId)
    {
        return $query->where('user_id', $userId);
    }

    // Global scope (applied to all queries)
    protected static function booted()
    {
        static::addGlobalScope('published', function ($query) {
            $query->where('status', 'published');
        });
    }
}

// Usage
$posts = Post::published()->popular()->get();
$myPosts = Post::published()->byAuthor(auth()->id())->get();

// Remove global scope when needed
$allPosts = Post::withoutGlobalScope('published')->get();

Practice Exercise 1: Optimize Blog Posts Listing

Given this inefficient code, optimize it:

<?php
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name;
    echo $post->category->name;
    echo $post->comments->count();
    echo $post->tags->pluck('name')->implode(', ');
}

Requirements:

Fix N+1 problems with eager loading
Use withCount() for comment counts
Add pagination (20 per page)
Cache the results for 5 minutes
Select only needed columns

Practice Exercise 2: Add Strategic Indexes

Create a migration to add indexes for this posts table:

Add index for user_id (foreign key)
Add index for status column
Add composite index for (status, published_at)
Add unique index for slug
Add full-text index for title and content

Test: Write queries that benefit from these indexes and use EXPLAIN to verify index usage.

Practice Exercise 3: Transaction-based Order Processing

Implement a checkout process using database transactions:

Create order record
Create order items
Decrease product inventory
Record payment transaction
Send order confirmation (outside transaction)

Requirements:

All database operations must succeed or all fail
Check inventory availability before processing
Handle errors gracefully with rollback
Log any transaction failures

Summary

In this lesson, you learned essential database optimization techniques:

Identifying and solving N+1 query problems with eager loading
Using database indexes to speed up queries
Optimizing queries by selecting only needed columns
Chunking and lazy collections for processing large datasets
Using withCount() to avoid N+1 with aggregates
Implementing query result caching with Laravel's Cache facade
Using database transactions for data integrity
Implementing efficient pagination strategies
Creating reusable query scopes
Best practices for database performance

Database optimization is crucial for building scalable applications. These techniques will significantly improve your application's performance and user experience. In the next lesson, we'll build a complete CRUD application putting all these concepts together.