/laravel-agent:db:optimize

Overview

The /db:optimize command analyzes your Laravel application's database queries and schema to identify performance bottlenecks and optimization opportunities. It scans for N+1 queries, Big O complexity issues, missing indexes, slow queries, and suggests eager loading strategies to improve database performance.

Usage

/laravel-agent:db:optimize [target]

Examples

# Analyze entire application
/laravel-agent:db:optimize

# Analyze specific controller
/laravel-agent:db:optimize OrderController

# Analyze specific table
/laravel-agent:db:optimize orders

What Gets Analyzed

The command performs a comprehensive database optimization analysis:

Optimization Process

The command follows a systematic approach to database optimization:

1. Scan for N+1 queries - Identifies loops and iterations that trigger multiple database queries
2. Detect Big O complexity issues - Finds O(n²) patterns like nested loops and inefficient lookups
3. Check missing indexes - Analyzes query patterns to find columns that would benefit from indexing
4. Analyze slow queries - Reviews query complexity and execution time
5. Suggest eager loading - Recommends optimal relationship loading strategies
6. Review schema efficiency - Evaluates table structure and suggests improvements

Example: N+1 Query Detection

Before optimization:

<?php

// Controller with N+1 problem
public function index()
{
    $orders = Order::all();

    foreach ($orders as $order) {
        echo $order->customer->name; // N+1 query!
        echo $order->items->count();  // Another N+1!
    }
}

After optimization:

<?php

// Optimized with eager loading
public function index()
{
    $orders = Order::with(['customer', 'items'])->get();

    foreach ($orders as $order) {
        echo $order->customer->name; // Uses eager loaded data
        echo $order->items->count();  // No additional query
    }
}

Example: Big O Complexity Detection

Before optimization (O(n²)):

<?php

// O(n²) - Nested loop comparing all items
$users = User::all();
$orders = Order::all();

foreach ($users as $user) {
    foreach ($orders as $order) {
        if ($order->user_id === $user->id) {
            // Process - runs n×m times!
        }
    }
}

// O(n²) - contains() is O(n), called n times
$existingEmails = User::pluck('email');
foreach ($newUsers as $userData) {
    if (!$existingEmails->contains($userData['email'])) {
        User::create($userData);
    }
}

After optimization (O(n)):

<?php

// O(n) - Eager load relationships
$users = User::with('orders')->get();
foreach ($users as $user) {
    foreach ($user->orders as $order) {
        // Process - each order accessed once
    }
}

// Or: O(n) - Use groupBy for O(1) lookups
$ordersByUser = Order::all()->groupBy('user_id');
foreach ($users as $user) {
    $userOrders = $ordersByUser->get($user->id, collect());
}

// O(n) - Use flip() for O(1) has() lookups
$existingEmails = User::pluck('email')->flip();
foreach ($newUsers as $userData) {
    if (!$existingEmails->has($userData['email'])) {
        User::create($userData);
    }
}

Example: Missing Index Detection

Before optimization:

<?php

// Query without index
$orders = Order::where('status', 'pending')
    ->where('user_id', $userId)
    ->orderBy('created_at', 'desc')
    ->get();

Generated migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::table('orders', function (Blueprint $table) {
            // Composite index for common query pattern
            $table->index(['status', 'user_id', 'created_at']);
        });
    }

    public function down()
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex(['status', 'user_id', 'created_at']);
        });
    }
};

Target Scopes

You can scope the optimization analysis to specific parts of your application:

• Entire Application - Run without arguments to analyze all controllers, models, and queries
• Specific Controller - Pass a controller name to focus on its query patterns
• Specific Table - Pass a table name to analyze its schema and related queries
• Specific Model - Pass a model name to review its relationships and query usage

Best Practices

1. Run regularly - Make database optimization part of your development workflow
2. Start with N+1 queries - These often provide the biggest performance wins
3. Test index suggestions - Not all indexes improve performance; test on production-like data
4. Monitor query counts - Use Laravel Debugbar or Telescope to verify improvements
5. Consider caching - Some queries may be better cached than optimized
6. Profile before and after - Measure actual performance improvements

Performance Impact

Typical optimization results:

• N+1 fixes - Can reduce query count from hundreds to single digits
• Big O fixes - Convert O(n²) to O(n), massive improvements for large datasets
• Index additions - Often improve query speed by 10-100x
• Eager loading - Reduces page load time by 50-90% in data-heavy views
• Schema optimization - Improves storage efficiency and join performance

Related Agent

This command uses the laravel-database agent.

See Also

• /laravel-agent:db:migration - Create database migrations
• /laravel-agent:model:make - Generate models with relationships
• laravel-database skill - Auto-invoked for database operations