Laravel Database Optimization Guide

Advanced Database Performance Techniques for Laravel

Master Laravel database optimization with this comprehensive guide covering indexing strategies, query optimization, connection management, and advanced performance techniques for high-traffic production applications.

Time Required: 3-6 hours
Difficulty: Intermediate to Advanced
Prerequisites: Laravel application, database administration knowledge, SQL proficiency

Table of Contents

1. Database Performance Overview
2. Strategic Indexing
3. Query Optimization
4. Connection Management
5. Database Caching Strategies
6. Performance Monitoring
7. Database Scaling
8. Performance Troubleshooting

Database Performance Overview

Database optimization is crucial for Laravel application performance:

Performance Impact Areas

Optimization Technique	Performance Gain	Implementation Effort	Maintenance
Proper Indexing	80-95% faster queries	Medium	Low
Query Optimization	50-90% faster execution	High	Medium
Connection Pooling	30-60% better throughput	Low	Low
Read Replicas	2-10x read capacity	High	High
Database Sharding	Linear scaling	Very High	Very High

Performance Metrics to Monitor

• Query Execution Time: Target < 50ms for most queries
• Connection Pool Utilization: Target < 80%
• Database CPU Usage: Target < 70%
• Slow Query Count: Target < 1% of total queries
• Index Hit Ratio: Target > 95%
• Buffer Pool Hit Ratio: Target > 99%

Strategic Indexing

Step 1: Analyze Query Patterns

Identify queries that need optimization:

# Enable MySQL slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

# Create Laravel command to analyze queries
php artisan make:command AnalyzeQueries

// app/Console/Commands/AnalyzeQueries.php
<?php

namespace AppConsoleCommands;

use IlluminateConsoleCommand;
use IlluminateSupportFacadesDB;

class AnalyzeQueries extends Command
{
    protected $signature = 'db:analyze-queries';
    protected $description = 'Analyze database queries for optimization opportunities';

    public function handle()
    {
        $this->info('Analyzing Database Query Patterns');
        $this->info('====================================');
        
        // Get slow queries
        $this->analyzeSlowQueries();
        
        // Check missing indexes
        $this->checkMissingIndexes();
        
        // Analyze table statistics
        $this->analyzeTableStatistics();
        
        // Check unused indexes
        $this->checkUnusedIndexes();
    }

    private function analyzeSlowQueries()
    {
        $this->info("\n1. Slow Query Analysis");
        $this->line("=====================");
        
        // Get slow queries from performance schema (MySQL 5.7+)
        $slowQueries = DB::select("
            SELECT query_time, lock_time, rows_sent, rows_examined, 
                   LEFT(sql_text, 100) as sql_snippet
            FROM performance_schema.events_statements_history 
            WHERE timer_wait > 1000000000  -- 1 second in picoseconds
            ORDER BY timer_wait DESC
            LIMIT 10
        ");
        
        $this->table(
            ['Query Time', 'Lock Time', 'Rows Sent', 'Rows Examined', 'SQL Snippet'],
            collect($slowQueries)->map(function ($query) {
                return [
                    number_format($query->query_time, 3) . 's',
                    number_format($query->lock_time, 3) . 's',
                    $query->rows_sent,
                    $query->rows_examined,
                    substr($query->sql_snippet, 0, 50) . '...'
                ];
            })->toArray()
        );
    }

    private function checkMissingIndexes()
    {
        $this->info("\n2. Missing Index Analysis");
        $this->line("=========================");
        
        // Check for queries not using indexes
        $unindexedQueries = DB::select("
            SELECT table_name, column_name, cardinality
            FROM information_schema.statistics 
            WHERE table_schema = DATABASE()
            AND cardinality IS NULL
        ");
        
        if (empty($unindexedQueries)) {
            $this->info("No obvious missing indexes detected");
        } else {
            $this->warn("Found " . count($unindexedQueries) . " potential missing indexes");
        }
    }

    private function analyzeTableStatistics()
    {
        $this->info("\n3. Table Statistics");
        $this->line("==================");
        
        $tableStats = DB::select("
            SELECT 
                table_name,
                table_rows,
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
                ROUND((data_length / 1024 / 1024), 2) AS data_mb,
                ROUND((index_length / 1024 / 1024), 2) AS index_mb
            FROM information_schema.tables 
            WHERE table_schema = DATABASE()
            AND table_rows > 1000
            ORDER BY table_rows DESC
        ");
        
        $this->table(
            ['Table', 'Rows', 'Total Size (MB)', 'Data (MB)', 'Indexes (MB)'],
            collect($tableStats)->map(function ($stat) {
                return [
                    $stat->table_name,
                    number_format($stat->table_rows),
                    $stat->size_mb,
                    $stat->data_mb,
                    $stat->index_mb
                ];
            })->toArray()
        );
    }

    private function checkUnusedIndexes()
    {
        $this->info("\n4. Index Usage Analysis");
        $this->line("======================");
        
        // This requires MySQL 5.7+ performance schema
        $unusedIndexes = DB::select("
            SELECT DISTINCT 
                s.table_name,
                s.index_name,
                s.column_name
            FROM information_schema.statistics s
            LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i 
                ON s.table_schema = i.object_schema 
                AND s.table_name = i.object_name 
                AND s.index_name = i.index_name
            WHERE s.table_schema = DATABASE()
            AND s.index_name != 'PRIMARY'
            AND i.count_read IS NULL
            AND i.count_write IS NULL
        ");
        
        if (empty($unusedIndexes)) {
            $this->info("All indexes are being used");
        } else {
            $this->warn("Found " . count($unusedIndexes) . " potentially unused indexes");
            foreach ($unusedIndexes as $index) {
                $this->line("  - {$index->table_name}.{$index->index_name} ({$index->column_name})");
            }
        }
    }
}

Step 2: Create Optimal Indexes

Design and implement strategic database indexes:

# Generate migration for indexes
php artisan make:migration optimize_database_indexes

// Migration for strategic indexing
public function up()
{
    Schema::table('posts', function (Blueprint $table) {
        // Single column indexes for frequent WHERE clauses
        $table->index('published_at');
        $table->index('status');
        $table->index('featured');
        
        // Composite indexes for complex queries
        $table->index(['status', 'published_at', 'featured'], 'posts_status_published_featured');
        $table->index(['user_id', 'status', 'published_at'], 'posts_user_status_published');
        $table->index(['category_id', 'published_at'], 'posts_category_published');
        
        // Covering indexes (include frequently selected columns)
        $table->index(['status', 'published_at', 'title', 'slug'], 'posts_covering_index');
        
        // Full-text search indexes
        $table->fullText(['title', 'content', 'excerpt'], 'posts_fulltext');
    });

    Schema::table('users', function (Blueprint $table) {
        // Unique indexes for lookups
        $table->unique('email');
        $table->unique('username');
        
        // Regular indexes
        $table->index('email_verified_at');
        $table->index('created_at');
        $table->index(['status', 'created_at']);
    });

    Schema::table('comments', function (Blueprint $table) {
        // Foreign key indexes (if not automatically created)
        $table->index('post_id');
        $table->index('user_id');
        $table->index('parent_id');
        
        // Composite indexes for comment threads
        $table->index(['post_id', 'approved', 'created_at'], 'comments_post_approved_created');
        $table->index(['parent_id', 'approved'], 'comments_parent_approved');
    });

    Schema::table('tags', function (Blueprint $table) {
        $table->index('slug');
        $table->index('name');
    });

    Schema::table('post_tag', function (Blueprint $table) {
        // Pivot table optimization
        $table->index(['post_id', 'tag_id']);
        $table->index(['tag_id', 'post_id']);
    });
}

public function down()
{
    Schema::table('posts', function (Blueprint $table) {
        $table->dropIndex(['published_at']);
        $table->dropIndex(['status']);
        $table->dropIndex(['featured']);
        $table->dropIndex('posts_status_published_featured');
        $table->dropIndex('posts_user_status_published');
        $table->dropIndex('posts_category_published');
        $table->dropIndex('posts_covering_index');
        $table->dropFullText('posts_fulltext');
    });
    
    // Drop other indexes...
}

Step 3: Index Maintenance Strategy

Create automated index maintenance:

# Create index maintenance command
php artisan make:command MaintainIndexes

// app/Console/Commands/MaintainIndexes.php
class MaintainIndexes extends Command
{
    protected $signature = 'db:maintain-indexes {--analyze} {--rebuild}';
    
    public function handle()
    {
        if ($this->option('analyze')) {
            $this->analyzeIndexEffectiveness();
        }
        
        if ($this->option('rebuild')) {
            $this->rebuildIndexes();
        }
    }

    private function analyzeIndexEffectiveness()
    {
        $this->info('Analyzing index effectiveness...');
        
        // Check index usage statistics
        $indexStats = DB::select("
            SELECT 
                i.table_name,
                i.index_name,
                i.column_name,
                COALESCE(p.count_read, 0) as reads,
                COALESCE(p.count_write, 0) as writes,
                COALESCE(p.sum_timer_read, 0) as read_time
            FROM information_schema.statistics i
            LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
                ON i.table_schema = p.object_schema
                AND i.table_name = p.object_name
                AND i.index_name = p.index_name
            WHERE i.table_schema = DATABASE()
            ORDER BY reads DESC
        ");
        
        // Report most and least used indexes
        $mostUsed = collect($indexStats)->sortByDesc('reads')->take(10);
        $leastUsed = collect($indexStats)->where('reads', 0)->take(10);
        
        $this->info("Most used indexes:");
        foreach ($mostUsed as $index) {
            $this->line("  {$index->table_name}.{$index->index_name}: {$index->reads} reads");
        }
        
        if ($leastUsed->count() > 0) {
            $this->warn("Unused indexes (consider removal):");
            foreach ($leastUsed as $index) {
                $this->line("  {$index->table_name}.{$index->index_name}");
            }
        }
    }

    private function rebuildIndexes()
    {
        $this->info('Rebuilding fragmented indexes...');
        
        // Get tables with significant fragmentation
        $fragmentedTables = DB::select("
            SELECT 
                table_name,
                ROUND(data_free / 1024 / 1024, 2) as fragmentation_mb
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
            AND data_free > 10485760  -- 10MB
            ORDER BY data_free DESC
        ");
        
        foreach ($fragmentedTables as $table) {
            $this->line("Optimizing {$table->table_name} ({$table->fragmentation_mb}MB fragmentation)");
            DB::statement("OPTIMIZE TABLE {$table->table_name}");
        }
    }
}

Query Optimization

Step 1: Eliminate N+1 Query Problems

Implement efficient eager loading strategies:

// Bad: N+1 Query Problem
public function index()
{
    $posts = Post::all(); // 1 query
    
    foreach ($posts as $post) {
        echo $post->user->name;     // N queries
        echo $post->category->name; // N more queries
    }
}

// Good: Eager Loading
public function index()
{
    $posts = Post::with(['user', 'category'])->get(); // 3 queries total
    
    foreach ($posts as $post) {
        echo $post->user->name;
        echo $post->category->name;
    }
}

// Better: Selective Eager Loading
public function index()
{
    $posts = Post::with([
        'user:id,name,email',           // Only load needed columns
        'category:id,name,slug',
        'tags:id,name'                  // Load related tags
    ])->select('id', 'title', 'slug', 'user_id', 'category_id', 'created_at')
      ->get();
}

// Advanced: Conditional Eager Loading
public function index(Request $request)
{
    $query = Post::query();
    
    // Conditionally eager load based on needs
    if ($request->has('include_author')) {
        $query->with('user:id,name,avatar');
    }
    
    if ($request->has('include_comments')) {
        $query->with(['comments' => function ($query) {
            $query->approved()->with('user:id,name')->latest()->limit(5);
        }]);
    }
    
    return $query->get();
}

Step 2: Optimize Complex Queries

Write efficient database queries:

// Optimize EXISTS queries
// Bad: Using count()
$usersWithPosts = User::whereHas('posts', function ($query) {
    $query->where('published', true);
})->get();

// Better: Using exists()
$usersWithPosts = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('posts')
          ->whereRaw('posts.user_id = users.id')
          ->where('published', true);
})->get();

// Optimize aggregate queries
// Bad: Multiple queries
$stats = [
    'total_posts' => Post::count(),
    'published_posts' => Post::where('published', true)->count(),
    'draft_posts' => Post::where('published', false)->count(),
];

// Good: Single aggregation query
$stats = Post::selectRaw('
    COUNT(*) as total_posts,
    SUM(CASE WHEN published = 1 THEN 1 ELSE 0 END) as published_posts,
    SUM(CASE WHEN published = 0 THEN 1 ELSE 0 END) as draft_posts
')->first();

// Optimize pagination for large datasets
// Bad: Regular pagination with OFFSET
$posts = Post::orderBy('created_at', 'desc')->paginate(20); // Gets slower with higher pages

// Good: Cursor-based pagination
$posts = Post::orderBy('id', 'desc')
    ->when($request->cursor, function ($query, $cursor) {
        return $query->where('id', '<', $cursor);
    })
    ->limit(20)
    ->get();

// Advanced: Window functions (MySQL 8.0+)
// Get top 3 posts per category
$topPosts = DB::select("
    SELECT id, title, category_id, views, category_rank
    FROM (
        SELECT 
            id, title, category_id, views,
            ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY views DESC) as category_rank
        FROM posts 
        WHERE published = 1
    ) ranked_posts 
    WHERE category_rank <= 3
");

Step 3: Query Performance Monitoring

Implement query performance tracking:

// Create query performance middleware
class QueryPerformanceMiddleware
{
    public function handle($request, Closure $next)
    {
        $queryCount = 0;
        $queryTime = 0;
        $queries = [];

        DB::listen(function ($query) use (&$queryCount, &$queryTime, &$queries) {
            $queryCount++;
            $queryTime += $query->time;
            
            if ($query->time > 50) { // Log slow queries
                $queries[] = [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time' => $query->time
                ];
            }
        });

        $response = $next($request);

        // Log performance metrics
        if ($queryTime > 100 || $queryCount > 10) {
            Log::warning('High Database Usage', [
                'route' => $request->route()->getName(),
                'query_count' => $queryCount,
                'total_time' => $queryTime,
                'slow_queries' => $queries,
                'memory_usage' => memory_get_peak_usage(true) / 1024 / 1024,
            ]);
        }

        // Add headers for debugging (development only)
        if (app()->environment('local')) {
            $response->headers->set('X-Query-Count', $queryCount);
            $response->headers->set('X-Query-Time', $queryTime . 'ms');
        }

        return $response;
    }
}

// Query optimization service
class QueryOptimizer
{
    public static function explainQuery($sql, $bindings = [])
    {
        $explanation = DB::select("EXPLAIN FORMAT=JSON " . $sql, $bindings);
        
        return json_decode($explanation[0]->{'EXPLAIN'}, true);
    }
    
    public static function analyzeQueryPerformance($model, $query)
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        $startTime = microtime(true);
        $result = $query->get();
        $endTime = microtime(true);
        
        $executionTime = ($endTime - $startTime) * 1000;
        
        Log::info('Query Performance Analysis', [
            'model' => get_class($model),
            'sql' => $sql,
            'bindings' => $bindings,
            'execution_time_ms' => round($executionTime, 2),
            'result_count' => $result->count(),
            'memory_usage_mb' => round(memory_get_usage(true) / 1024 / 1024, 2),
        ]);
        
        return $result;
    }
}

Connection Management

Step 1: Connection Pool Configuration

Optimize database connections:

// config/database.php - Optimized MySQL configuration
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => 'InnoDB',
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        PDO::ATTR_PERSISTENT => env('DB_PERSISTENT', true), // Connection pooling
        PDO::ATTR_TIMEOUT => 5,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    ]) : [],
    
    // Connection pool settings
    'pool' => [
        'min_connections' => 5,
        'max_connections' => 20,
        'max_idle_time' => 300, // 5 minutes
        'validation_query' => 'SELECT 1',
    ],
    
    // Read/Write splitting
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', '127.0.0.1'),
            env('DB_READ_HOST_2', '127.0.0.1'),
        ],
    ],
    'write' => [
        'host' => [
            env('DB_WRITE_HOST', '127.0.0.1'),
        ],
    ],
    'sticky' => true, // Sticky sessions for read/write
],

Step 2: Connection Health Monitoring

Monitor database connection health:

# Create connection health check command
php artisan make:command CheckDatabaseHealth

// app/Console/Commands/CheckDatabaseHealth.php
class CheckDatabaseHealth extends Command
{
    protected $signature = 'db:health-check';
    
    public function handle()
    {
        $this->info('Database Health Check');
        $this->info('====================');
        
        // Check connection status
        $this->checkConnectionStatus();
        
        // Check connection pool
        $this->checkConnectionPool();
        
        // Check replication lag (if using read replicas)
        $this->checkReplicationLag();
        
        // Check database performance metrics
        $this->checkPerformanceMetrics();
    }

    private function checkConnectionStatus()
    {
        try {
            $start = microtime(true);
            DB::select('SELECT 1');
            $time = (microtime(true) - $start) * 1000;
            
            $this->info("✓ Database connection: {$time}ms");
            
            // Check each configured connection
            foreach (config('database.connections') as $name => $config) {
                if ($name === 'sqlite') continue; // Skip SQLite
                
                try {
                    $start = microtime(true);
                    DB::connection($name)->select('SELECT 1');
                    $time = (microtime(true) - $start) * 1000;
                    
                    $this->info("✓ Connection '{$name}': {$time}ms");
                } catch (Exception $e) {
                    $this->error("✗ Connection '{$name}': {$e->getMessage()}");
                }
            }
            
        } catch (Exception $e) {
            $this->error("✗ Database connection failed: {$e->getMessage()}");
        }
    }

    private function checkConnectionPool()
    {
        try {
            // MySQL specific - check connection count
            $connections = DB::select('SHOW STATUS LIKE "Threads_connected"');
            $maxConnections = DB::select('SHOW VARIABLES LIKE "max_connections"');
            
            $current = $connections[0]->Value;
            $max = $maxConnections[0]->Value;
            $percentage = round(($current / $max) * 100, 1);
            
            if ($percentage > 80) {
                $this->warn("⚠ High connection usage: {$current}/{$max} ({$percentage}%)");
            } else {
                $this->info("✓ Connection pool: {$current}/{$max} ({$percentage}%)");
            }
            
        } catch (Exception $e) {
            $this->warn("Could not check connection pool: {$e->getMessage()}");
        }
    }

    private function checkReplicationLag()
    {
        try {
            // Check if read replicas are configured
            $readHosts = config('database.connections.mysql.read.host', []);
            
            if (empty($readHosts)) {
                $this->info("No read replicas configured");
                return;
            }
            
            // Check replication lag (MySQL specific)
            $replicationInfo = DB::select('SHOW SLAVE STATUS');
            
            if (empty($replicationInfo)) {
                $this->info("No replication status available");
                return;
            }
            
            $lag = $replicationInfo[0]->Seconds_Behind_Master;
            
            if ($lag === null) {
                $this->error("✗ Replication not running");
            } elseif ($lag > 5) {
                $this->warn("⚠ Replication lag: {$lag} seconds");
            } else {
                $this->info("✓ Replication lag: {$lag} seconds");
            }
            
        } catch (Exception $e) {
            $this->warn("Could not check replication status: {$e->getMessage()}");
        }
    }

    private function checkPerformanceMetrics()
    {
        try {
            // Key MySQL performance metrics
            $metrics = DB::select("
                SHOW STATUS WHERE Variable_name IN (
                    'Innodb_buffer_pool_read_requests',
                    'Innodb_buffer_pool_reads',
                    'Qcache_hits',
                    'Qcache_inserts',
                    'Created_tmp_disk_tables',
                    'Created_tmp_tables',
                    'Slow_queries'
                )
            ");
            
            $metricData = [];
            foreach ($metrics as $metric) {
                $metricData[$metric->Variable_name] = $metric->Value;
            }
            
            // Calculate buffer pool hit ratio
            $bufferReads = $metricData['Innodb_buffer_pool_reads'] ?? 0;
            $bufferRequests = $metricData['Innodb_buffer_pool_read_requests'] ?? 1;
            $bufferHitRatio = (1 - ($bufferReads / $bufferRequests)) * 100;
            
            if ($bufferHitRatio > 99) {
                $this->info("✓ Buffer pool hit ratio: " . round($bufferHitRatio, 2) . "%");
            } else {
                $this->warn("⚠ Low buffer pool hit ratio: " . round($bufferHitRatio, 2) . "%");
            }
            
            // Check for excessive temporary tables
            $tmpTables = $metricData['Created_tmp_tables'] ?? 0;
            $tmpDiskTables = $metricData['Created_tmp_disk_tables'] ?? 0;
            
            if ($tmpTables > 0) {
                $diskTableRatio = ($tmpDiskTables / $tmpTables) * 100;
                if ($diskTableRatio > 25) {
                    $this->warn("⚠ High disk temp table ratio: " . round($diskTableRatio, 1) . "%");
                } else {
                    $this->info("✓ Temp table performance: " . round($diskTableRatio, 1) . "% on disk");
                }
            }
            
            // Check slow queries
            $slowQueries = $metricData['Slow_queries'] ?? 0;
            if ($slowQueries > 100) {
                $this->warn("⚠ High slow query count: {$slowQueries}");
            } else {
                $this->info("✓ Slow queries: {$slowQueries}");
            }
            
        } catch (Exception $e) {
            $this->warn("Could not retrieve performance metrics: {$e->getMessage()}");
        }
    }
}

Step 3: Read/Write Splitting

Implement read/write database splitting:

// Create read/write connection manager
class DatabaseConnectionManager
{
    public static function useReadConnection()
    {
        return DB::connection('mysql::read');
    }
    
    public static function useWriteConnection()
    {
        return DB::connection('mysql::write');
    }
    
    public static function withReadConnection(callable $callback)
    {
        $originalConnection = DB::getDefaultConnection();
        DB::setDefaultConnection('mysql::read');
        
        try {
            return $callback();
        } finally {
            DB::setDefaultConnection($originalConnection);
        }
    }
}

// Repository pattern with read/write splitting
class PostRepository
{
    public function find($id)
    {
        // Read operations use read replicas
        return DatabaseConnectionManager::withReadConnection(function () use ($id) {
            return Post::find($id);
        });
    }
    
    public function findForDashboard()
    {
        // Analytics queries use read replicas
        return DatabaseConnectionManager::withReadConnection(function () {
            return Post::with(['user', 'category'])
                ->where('published', true)
                ->orderBy('views', 'desc')
                ->limit(10)
                ->get();
        });
    }
    
    public function create(array $data)
    {
        // Write operations use master database
        return DatabaseConnectionManager::useWriteConnection()
            ->table('posts')
            ->insert($data);
    }
    
    public function update($id, array $data)
    {
        // Updates use master database
        return DatabaseConnectionManager::useWriteConnection()
            ->table('posts')
            ->where('id', $id)
            ->update($data);
    }
}

Database Caching Strategies

Step 1: Query Result Caching

Cache expensive database queries:

// Query result caching with automatic invalidation
class CacheableQuery
{
    public static function remember($key, $ttl, $query, $tags = [])
    {
        return Cache::tags($tags)->remember($key, $ttl, function () use ($query) {
            $start = microtime(true);
            $result = $query();
            $time = (microtime(true) - $start) * 1000;
            
            Log::info('Query cached', [
                'key' => $key,
                'execution_time' => round($time, 2) . 'ms',
                'result_count' => is_countable($result) ? count($result) : 1,
            ]);
            
            return $result;
        });
    }
}

// Model with built-in caching
class Post extends Model
{
    // Cache popular posts
    public static function getPopular($limit = 10)
    {
        return CacheableQuery::remember(
            "posts.popular.{$limit}",
            3600, // 1 hour
            function () use ($limit) {
                return static::with(['user', 'category'])
                    ->where('published', true)
                    ->orderBy('views', 'desc')
                    ->limit($limit)
                    ->get();
            },
            ['posts', 'popular']
        );
    }
    
    // Cache category posts
    public static function getByCategorySlug($slug, $limit = 20)
    {
        return CacheableQuery::remember(
            "posts.category.{$slug}.{$limit}",
            1800, // 30 minutes
            function () use ($slug, $limit) {
                return static::with(['user'])
                    ->whereHas('category', function ($query) use ($slug) {
                        $query->where('slug', $slug);
                    })
                    ->where('published', true)
                    ->orderBy('published_at', 'desc')
                    ->paginate($limit);
            },
            ['posts', 'categories', "category.{$slug}"]
        );
    }
    
    // Cache single post with relationships
    public static function getCachedBySlug($slug)
    {
        return CacheableQuery::remember(
            "posts.single.{$slug}",
            7200, // 2 hours
            function () use ($slug) {
                return static::with(['user', 'category', 'tags', 'comments.user'])
                    ->where('slug', $slug)
                    ->where('published', true)
                    ->firstOrFail();
            },
            ['posts', "post.{$slug}"]
        );
    }
    
    // Invalidate cache on model changes
    protected static function boot()
    {
        parent::boot();
        
        static::saved(function ($post) {
            // Clear related caches
            Cache::tags(['posts', 'popular'])->flush();
            Cache::tags(["post.{$post->slug}"])->flush();
            Cache::tags(["category.{$post->category->slug}"])->flush();
        });
        
        static::deleted(function ($post) {
            Cache::tags(['posts', 'popular'])->flush();
            Cache::tags(["post.{$post->slug}"])->flush();
        });
    }
}

Step 2: Database Query Caching

Implement MySQL query cache optimization:

# MySQL query cache configuration (my.cnf)
# Note: Query cache is deprecated in MySQL 8.0+
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

# For MySQL 8.0+, use application-level caching instead
# or implement Redis-based query result caching

// Redis-based query result caching
class QueryCache
{
    private $redis;
    private $ttl;

    public function __construct($ttl = 3600)
    {
        $this->redis = Redis::connection('cache');
        $this->ttl = $ttl;
    }

    public function remember($query, $bindings = [])
    {
        $key = $this->getCacheKey($query, $bindings);
        
        $cached = $this->redis->get($key);
        if ($cached !== null) {
            return json_decode($cached, true);
        }

        $result = DB::select($query, $bindings);
        
        $this->redis->setex($key, $this->ttl, json_encode($result));
        
        return $result;
    }

    private function getCacheKey($query, $bindings)
    {
        return 'query_cache:' . md5($query . serialize($bindings));
    }
    
    public function invalidatePattern($pattern)
    {
        $keys = $this->redis->keys("query_cache:{$pattern}*");
        if (!empty($keys)) {
            $this->redis->del($keys);
        }
    }
}

// Usage in repositories
class AnalyticsRepository
{
    private $queryCache;

    public function __construct()
    {
        $this->queryCache = new QueryCache(1800); // 30 minutes
    }

    public function getTopPosts($days = 30)
    {
        return $this->queryCache->remember("
            SELECT p.id, p.title, p.slug, COUNT(v.id) as views
            FROM posts p
            LEFT JOIN post_views v ON p.id = v.post_id
            WHERE p.published = 1 
            AND v.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            GROUP BY p.id
            ORDER BY views DESC
            LIMIT 20
        ", [$days]);
    }

    public function getUserEngagement($userId, $months = 6)
    {
        return $this->queryCache->remember("
            SELECT 
                DATE_FORMAT(created_at, '%Y-%m') as month,
                COUNT(*) as post_count,
                SUM(views) as total_views,
                AVG(views) as avg_views
            FROM posts 
            WHERE user_id = ? 
            AND created_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
            GROUP BY DATE_FORMAT(created_at, '%Y-%m')
            ORDER BY month DESC
        ", [$userId, $months]);
    }
}

Performance Monitoring

Step 1: Real-time Performance Monitoring

Set up comprehensive database monitoring:

# Create database monitoring command
php artisan make:command MonitorDatabase

// app/Console/Commands/MonitorDatabase.php
class MonitorDatabase extends Command
{
    protected $signature = 'db:monitor {--interval=30} {--alert-threshold=5}';
    
    public function handle()
    {
        $interval = $this->option('interval');
        $threshold = $this->option('alert-threshold');
        
        $this->info("Starting database monitoring (interval: {$interval}s, threshold: {$threshold}s)");
        
        while (true) {
            $this->performHealthCheck($threshold);
            sleep($interval);
        }
    }

    private function performHealthCheck($threshold)
    {
        $metrics = $this->collectMetrics();
        $this->analyzeMetrics($metrics, $threshold);
        $this->logMetrics($metrics);
    }

    private function collectMetrics()
    {
        $start = microtime(true);
        
        // Test query performance
        DB::select('SELECT 1');
        $queryTime = (microtime(true) - $start) * 1000;
        
        // Get system metrics
        $processlist = DB::select('SHOW PROCESSLIST');
        $status = DB::select("SHOW STATUS WHERE Variable_name IN (
            'Threads_connected',
            'Threads_running',
            'Innodb_buffer_pool_read_requests',
            'Innodb_buffer_pool_reads',
            'Slow_queries',
            'Questions'
        )");
        
        $statusData = [];
        foreach ($status as $stat) {
            $statusData[$stat->Variable_name] = $stat->Value;
        }
        
        return [
            'query_time' => $queryTime,
            'connections' => count($processlist),
            'running_threads' => $statusData['Threads_running'] ?? 0,
            'total_connections' => $statusData['Threads_connected'] ?? 0,
            'buffer_pool_hits' => $this->calculateBufferPoolHitRatio($statusData),
            'slow_queries' => $statusData['Slow_queries'] ?? 0,
            'total_queries' => $statusData['Questions'] ?? 0,
            'timestamp' => now(),
        ];
    }

    private function calculateBufferPoolHitRatio($statusData)
    {
        $reads = $statusData['Innodb_buffer_pool_reads'] ?? 0;
        $requests = $statusData['Innodb_buffer_pool_read_requests'] ?? 1;
        
        return round((1 - ($reads / $requests)) * 100, 2);
    }

    private function analyzeMetrics($metrics, $threshold)
    {
        // Check query performance
        if ($metrics['query_time'] > $threshold * 1000) {
            $this->warn("⚠ Slow query response: {$metrics['query_time']}ms");
            $this->sendAlert('slow_query', $metrics);
        }
        
        // Check connection count
        if ($metrics['total_connections'] > 80) {
            $this->warn("⚠ High connection count: {$metrics['total_connections']}");
            $this->sendAlert('high_connections', $metrics);
        }
        
        // Check buffer pool performance
        if ($metrics['buffer_pool_hits'] < 95) {
            $this->warn("⚠ Low buffer pool hit ratio: {$metrics['buffer_pool_hits']}%");
            $this->sendAlert('low_buffer_hits', $metrics);
        }
        
        // All good
        if ($metrics['query_time'] <= $threshold * 1000) {
            $this->info("✓ Database healthy - {$metrics['query_time']}ms response");
        }
    }

    private function logMetrics($metrics)
    {
        Log::info('Database Performance Metrics', $metrics);
        
        // Store in metrics table for historical analysis
        DB::table('database_metrics')->insert([
            'query_time' => $metrics['query_time'],
            'connections' => $metrics['connections'],
            'running_threads' => $metrics['running_threads'],
            'buffer_pool_hits' => $metrics['buffer_pool_hits'],
            'slow_queries' => $metrics['slow_queries'],
            'created_at' => $metrics['timestamp'],
        ]);
    }

    private function sendAlert($type, $metrics)
    {
        // Send notification to administrators
        $admins = User::role('admin')->get();
        
        Notification::send($admins, new DatabaseAlertNotification($type, $metrics));
    }
}

Step 2: Query Performance Dashboard

Create a performance monitoring dashboard:

// Controller for database dashboard
class DatabaseDashboardController extends Controller
{
    public function index()
    {
        $metrics = $this->getPerformanceMetrics();
        $slowQueries = $this->getSlowQueries();
        $connections = $this->getConnectionStats();
        
        return view('admin.database-dashboard', compact(
            'metrics', 'slowQueries', 'connections'
        ));
    }

    private function getPerformanceMetrics()
    {
        // Get metrics from the last 24 hours
        return DB::table('database_metrics')
            ->where('created_at', '>=', now()->subDay())
            ->orderBy('created_at')
            ->get()
            ->groupBy(function ($metric) {
                return $metric->created_at->format('H:i');
            })
            ->map(function ($group) {
                return [
                    'avg_query_time' => $group->avg('query_time'),
                    'max_connections' => $group->max('connections'),
                    'avg_buffer_hits' => $group->avg('buffer_pool_hits'),
                    'total_slow_queries' => $group->sum('slow_queries'),
                ];
            });
    }

    private function getSlowQueries()
    {
        // Get recent slow queries from logs
        return DB::table('slow_query_log')
            ->where('start_time', '>=', now()->subHour())
            ->orderBy('query_time', 'desc')
            ->limit(10)
            ->get();
    }

    private function getConnectionStats()
    {
        $status = DB::select("SHOW STATUS WHERE Variable_name IN (
            'Threads_connected',
            'Threads_running',
            'Max_used_connections',
            'Connection_errors_max_connections'
        )");
        
        $stats = [];
        foreach ($status as $stat) {
            $stats[$stat->Variable_name] = $stat->Value;
        }
        
        return $stats;
    }

    public function queryAnalysis()
    {
        // Real-time query analysis
        $processlist = DB::select('SHOW FULL PROCESSLIST');
        
        return response()->json([
            'active_queries' => count($processlist),
            'processes' => collect($processlist)->map(function ($process) {
                return [
                    'id' => $process->Id,
                    'user' => $process->User,
                    'host' => $process->Host,
                    'db' => $process->db,
                    'command' => $process->Command,
                    'time' => $process->Time,
                    'state' => $process->State,
                    'info' => Str::limit($process->Info, 100),
                ];
            }),
        ]);
    }
}

Database Scaling

Step 1: Horizontal Scaling Strategy

Plan for database scaling:

// Database sharding configuration
// config/database.php
'connections' => [
    'mysql_shard_1' => [
        'driver' => 'mysql',
        'host' => env('DB_SHARD_1_HOST', '127.0.0.1'),
        'database' => env('DB_SHARD_1_DATABASE', 'app_shard_1'),
        // ... other config
    ],
    'mysql_shard_2' => [
        'driver' => 'mysql',
        'host' => env('DB_SHARD_2_HOST', '127.0.0.1'),
        'database' => env('DB_SHARD_2_DATABASE', 'app_shard_2'),
        // ... other config
    ],
    // ... more shards
],

// Sharding service
class DatabaseSharding
{
    public static function getShardForUser($userId)
    {
        $shardCount = config('database.shard_count', 4);
        $shardNumber = ($userId % $shardCount) + 1;
        
        return "mysql_shard_{$shardNumber}";
    }
    
    public static function getShardForData($key)
    {
        $shardCount = config('database.shard_count', 4);
        $shardNumber = (crc32($key) % $shardCount) + 1;
        
        return "mysql_shard_{$shardNumber}";
    }
}

// Sharded model
class ShardedPost extends Model
{
    protected $table = 'posts';
    
    public function getConnectionName()
    {
        if ($this->user_id) {
            return DatabaseSharding::getShardForUser($this->user_id);
        }
        
        return parent::getConnectionName();
    }
    
    public static function findByUserId($userId, $postId)
    {
        $connection = DatabaseSharding::getShardForUser($userId);
        
        return static::on($connection)->find($postId);
    }
    
    public static function getAllUserPosts($userId)
    {
        $connection = DatabaseSharding::getShardForUser($userId);
        
        return static::on($connection)
            ->where('user_id', $userId)
            ->get();
    }
}

Step 2: Database Partitioning

Implement table partitioning for large datasets:

# Create partitioned table migration
php artisan make:migration create_partitioned_analytics_table

// Migration for partitioned table
public function up()
{
    // Create partitioned table (MySQL 8.0+)
    DB::statement("
        CREATE TABLE analytics_events (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED,
            event_type VARCHAR(50),
            event_data JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id, created_at),
            INDEX idx_user_id (user_id),
            INDEX idx_event_type (event_type)
        )
        PARTITION BY RANGE (YEAR(created_at)) (
            PARTITION p2023 VALUES LESS THAN (2024),
            PARTITION p2024 VALUES LESS THAN (2025),
            PARTITION p2025 VALUES LESS THAN (2026),
            PARTITION p_future VALUES LESS THAN MAXVALUE
        )
    ");
}

// Automatic partition management
class PartitionManager
{
    public static function createYearlyPartitions($tableName, $years = 3)
    {
        $currentYear = now()->year;
        
        for ($i = 1; $i <= $years; $i++) {
            $year = $currentYear + $i;
            $partitionName = "p{$year}";
            $maxValue = $year + 1;
            
            try {
                DB::statement("
                    ALTER TABLE {$tableName} 
                    ADD PARTITION (
                        PARTITION {$partitionName} VALUES LESS THAN ({$maxValue})
                    )
                ");
                
                Log::info("Created partition {$partitionName} for table {$tableName}");
            } catch (Exception $e) {
                Log::warning("Could not create partition {$partitionName}: {$e->getMessage()}");
            }
        }
    }
    
    public static function dropOldPartitions($tableName, $keepYears = 2)
    {
        $cutoffYear = now()->subYears($keepYears)->year;
        
        $partitions = DB::select("
            SELECT PARTITION_NAME 
            FROM INFORMATION_SCHEMA.PARTITIONS 
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = ?
            AND PARTITION_NAME IS NOT NULL
            AND PARTITION_NAME REGEXP '^p[0-9]+$'
            AND CAST(SUBSTRING(PARTITION_NAME, 2) AS UNSIGNED) < ?
        ", [$tableName, $cutoffYear]);
        
        foreach ($partitions as $partition) {
            try {
                DB::statement("ALTER TABLE {$tableName} DROP PARTITION {$partition->PARTITION_NAME}");
                Log::info("Dropped old partition {$partition->PARTITION_NAME} from {$tableName}");
            } catch (Exception $e) {
                Log::error("Could not drop partition {$partition->PARTITION_NAME}: {$e->getMessage()}");
            }
        }
    }
}

Performance Troubleshooting

Common Database Performance Issues

Diagnose and resolve database performance problems:

# Create database troubleshooting command
php artisan make:command TroubleshootDatabase

// app/Console/Commands/TroubleshootDatabase.php
class TroubleshootDatabase extends Command
{
    protected $signature = 'db:troubleshoot {--fix}';
    
    public function handle()
    {
        $this->info('Database Performance Troubleshooting');
        $this->info('====================================');
        
        $issues = [];
        
        // Check for common issues
        $issues = array_merge($issues, $this->checkSlowQueries());
        $issues = array_merge($issues, $this->checkMissingIndexes());
        $issues = array_merge($issues, $this->checkTableFragmentation());
        $issues = array_merge($issues, $this->checkBufferPoolSize());
        $issues = array_merge($issues, $this->checkConnectionLimits());
        
        if (empty($issues)) {
            $this->info('✓ No performance issues detected');
            return;
        }
        
        $this->warn('Found ' . count($issues) . ' potential issues:');
        foreach ($issues as $issue) {
            $this->line("  - {$issue['description']}");
            if ($this->option('fix') && isset($issue['fix'])) {
                $this->line("    Applying fix...");
                $issue['fix']();
                $this->info("    ✓ Fixed");
            }
        }
        
        if (!$this->option('fix')) {
            $this->info("
Run with --fix to automatically resolve fixable issues");
        }
    }

    private function checkSlowQueries()
    {
        $issues = [];
        
        try {
            $slowQueryCount = DB::select("SHOW STATUS LIKE 'Slow_queries'")[0]->Value;
            $totalQueries = DB::select("SHOW STATUS LIKE 'Questions'")[0]->Value;
            
            $slowQueryRatio = ($slowQueryCount / max($totalQueries, 1)) * 100;
            
            if ($slowQueryRatio > 1) {
                $issues[] = [
                    'type' => 'slow_queries',
                    'description' => "High slow query ratio: {$slowQueryRatio}% ({$slowQueryCount}/{$totalQueries})",
                    'severity' => 'high',
                ];
            }
            
            // Check current long-running queries
            $longRunning = DB::select("
                SELECT id, user, host, db, command, time, state, 
                       LEFT(info, 50) as query_snippet
                FROM information_schema.processlist 
                WHERE command != 'Sleep' 
                AND time > 30
                ORDER BY time DESC
            ");
            
            foreach ($longRunning as $query) {
                $issues[] = [
                    'type' => 'long_running_query',
                    'description' => "Long-running query (ID: {$query->id}, {$query->time}s): {$query->query_snippet}",
                    'severity' => 'medium',
                    'fix' => function() use ($query) {
                        // Option to kill long-running queries
                        if ($this->confirm("Kill query {$query->id} ({$query->time}s)?")) {
                            DB::statement("KILL {$query->id}");
                        }
                    }
                ];
            }
            
        } catch (Exception $e) {
            $issues[] = [
                'type' => 'query_analysis_failed',
                'description' => "Could not analyze slow queries: {$e->getMessage()}",
                'severity' => 'low',
            ];
        }
        
        return $issues;
    }

    private function checkMissingIndexes()
    {
        $issues = [];
        
        try {
            // Check for tables with full table scans
            $fullScans = DB::select("
                SELECT 
                    object_schema,
                    object_name,
                    count_read as full_scans
                FROM performance_schema.table_io_waits_summary_by_table 
                WHERE object_schema = DATABASE()
                AND count_read > 1000
                ORDER BY count_read DESC
                LIMIT 10
            ");
            
            foreach ($fullScans as $scan) {
                $issues[] = [
                    'type' => 'potential_missing_index',
                    'description' => "Table {$scan->object_name} has {$scan->full_scans} full table scans",
                    'severity' => 'medium',
                ];
            }
            
        } catch (Exception $e) {
            // Performance schema might not be available
        }
        
        return $issues;
    }

    private function checkTableFragmentation()
    {
        $issues = [];
        
        try {
            $fragmented = DB::select("
                SELECT 
                    table_name,
                    ROUND(data_free / 1024 / 1024, 2) as fragmentation_mb,
                    ROUND((data_free / (data_length + index_length)) * 100, 1) as fragmentation_pct
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND data_free > 50 * 1024 * 1024  -- 50MB
                ORDER BY data_free DESC
            ");
            
            foreach ($fragmented as $table) {
                $issues[] = [
                    'type' => 'table_fragmentation',
                    'description' => "Table {$table->table_name} is {$table->fragmentation_pct}% fragmented ({$table->fragmentation_mb}MB)",
                    'severity' => $table->fragmentation_pct > 30 ? 'high' : 'medium',
                    'fix' => function() use ($table) {
                        $this->line("    Optimizing table {$table->table_name}...");
                        DB::statement("OPTIMIZE TABLE {$table->table_name}");
                    }
                ];
            }
            
        } catch (Exception $e) {
            // Skip if can't check fragmentation
        }
        
        return $issues;
    }

    private function checkBufferPoolSize()
    {
        $issues = [];
        
        try {
            $bufferPoolSize = DB::select("SHOW VARIABLES LIKE 'innodb_buffer_pool_size'")[0]->Value;
            $totalRam = $this->getSystemMemory();
            
            $bufferPoolMB = $bufferPoolSize / 1024 / 1024;
            $ramMB = $totalRam / 1024 / 1024;
            $bufferPoolRatio = ($bufferPoolMB / $ramMB) * 100;
            
            if ($bufferPoolRatio < 50) {
                $issues[] = [
                    'type' => 'small_buffer_pool',
                    'description' => "InnoDB buffer pool is small: {$bufferPoolMB}MB ({$bufferPoolRatio}% of RAM)",
                    'severity' => 'medium',
                ];
            }
            
        } catch (Exception $e) {
            // Skip if can't check buffer pool
        }
        
        return $issues;
    }

    private function checkConnectionLimits()
    {
        $issues = [];
        
        try {
            $maxConnections = DB::select("SHOW VARIABLES LIKE 'max_connections'")[0]->Value;
            $currentConnections = DB::select("SHOW STATUS LIKE 'Threads_connected'")[0]->Value;
            $maxUsedConnections = DB::select("SHOW STATUS LIKE 'Max_used_connections'")[0]->Value;
            
            $connectionUtilization = ($currentConnections / $maxConnections) * 100;
            $peakUtilization = ($maxUsedConnections / $maxConnections) * 100;
            
            if ($connectionUtilization > 80) {
                $issues[] = [
                    'type' => 'high_connection_usage',
                    'description' => "High connection usage: {$currentConnections}/{$maxConnections} ({$connectionUtilization}%)",
                    'severity' => 'high',
                ];
            }
            
            if ($peakUtilization > 90) {
                $issues[] = [
                    'type' => 'connection_limit_reached',
                    'description' => "Connection limit nearly reached: peak {$maxUsedConnections}/{$maxConnections} ({$peakUtilization}%)",
                    'severity' => 'medium',
                ];
            }
            
        } catch (Exception $e) {
            // Skip if can't check connections
        }
        
        return $issues;
    }

    private function getSystemMemory()
    {
        // Try to get system memory (Linux)
        try {
            $meminfo = file_get_contents('/proc/meminfo');
            preg_match('/MemTotal:s+(d+)s+kB/', $meminfo, $matches);
            return isset($matches[1]) ? $matches[1] * 1024 : 0;
        } catch (Exception $e) {
            return 0;
        }
    }
}

CloudPloy Database Optimization

CloudPloy provides advanced database optimization for Laravel applications:

🗄️ Optimized Database Infrastructure

• High-performance SSD storage with optimized I/O
• Automated database tuning and configuration
• Connection pooling and query optimization
• Built-in read replica management

📊 Advanced Monitoring & Analytics

• Real-time database performance monitoring
• Slow query identification and optimization
• Index usage analysis and recommendations
• Automated performance alerts

🔄 Automated Optimization

• Dynamic query optimization
• Automatic index recommendations
• Connection pool auto-scaling
• Database maintenance automation

Performance Improvement Results

Typical improvements after database optimization:

• Query Performance: 80-95% faster execution times
• Connection Efficiency: 60% better connection utilization
• Memory Usage: 40% reduction in database memory consumption
• Concurrent Capacity: 5-10x more concurrent users
• Response Time: 70-90% improvement in page load times

Next Steps

After optimizing Laravel database performance:

1. Application Performance Optimization
2. Database Security Hardening
3. Advanced Database Monitoring
4. Redis Caching Integration

Professional Database Support

Need expert help with Laravel database optimization?

• 💬 24/7 Database Experts: Available in your dashboard
• 🔧 Database Audit Service: Complete performance analysis
• 📈 Custom Optimization Plans: Tailored to your data patterns
• 🛡️ Ongoing Maintenance: Continuous database optimization

---

Get Optimized Laravel Database Hosting

Experience high-performance database hosting with CloudPloy:

• 🗄️ High-performance SSD database servers
• 📊 Advanced database monitoring and optimization
• 🔄 Automated performance tuning
• 💬 Database experts available 24/7
• 🎁 Free database optimization audit

View Plans

The Free plan covers one server and one app. Compute is billed separately at the provider rate.

---

Last updated: 2025-08-30