Laravel Database Optimization

Laravel Database Optimization

Comprehensive database optimization guide for Laravel 13 applications. Contains 33 rules across 9 categories for writing performant database queries, proper indexing, efficient caching, naming conventions, and debugging slow queries in Laravel 13.

Metadata

• Version: 1.1.0
• Framework: Laravel 13.x
• PHP: 8.3+

When to Apply

Reference these guidelines when:

• Writing Eloquent queries or using the query builder
• Diagnosing and fixing N+1 query problems
• Adding database indexes to migrations
• Implementing Redis or cache-based optimizations
• Paginating or processing large datasets
• Wrapping operations in database transactions
• Creating or modifying migrations for production databases
• Debugging slow queries with EXPLAIN or Laravel Debugbar

Rule Categories by Priority

| Priority | Category | Impact | Prefix | |----------|----------|--------|--------| | 1 | Query Performance & N+1 | CRITICAL | query- | | 2 | Indexing Strategies | CRITICAL | index- | | 3 | Eloquent Optimization | HIGH | eloquent- | | 4 | Caching with Redis | HIGH | cache- | | 5 | Pagination & Large Datasets | HIGH | data- | | 6 | Transactions & Locking | HIGH | lock- | | 7 | Migrations | HIGH | migrate- | | 8 | Query Debugging | MEDIUM | debug- | | 9 | Naming & Structure | HIGH | naming- |

Quick Reference

1. Query Performance & N+1 (CRITICAL)

• query-eager-loading - Use eager loading to eliminate N+1 queries
• query-prevent-lazy-loading - Prevent lazy loading in development
• query-auto-eager-loading - Configure automatic eager loading on models
• query-select-columns - Select only needed columns instead of SELECT *

2. Indexing Strategies (CRITICAL)

• index-foreign-keys - Index all foreign key columns
• index-composite-indexes - Create composite indexes for multi-column queries
• index-covering-indexes - Use covering indexes for read-heavy queries
• index-full-text - Use full-text indexes for search functionality

3. Eloquent Optimization (HIGH)

• eloquent-query-builder-hot-paths - Use query builder for performance-critical paths
• eloquent-with-count-aggregates - Use withCount instead of loading relations to count
• eloquent-subquery-selects - Use subquery selects to avoid extra queries
• eloquent-where-has-optimization - Optimize whereHas with whereIn subqueries

4. Caching with Redis (HIGH)

• cache-remember - Use Cache::remember for expensive queries
• cache-invalidation - Invalidate cache on model changes
• cache-tags - Use cache tags for group invalidation
• cache-ttl - Set appropriate TTL values for cached data

5. Pagination & Large Datasets (HIGH)

• data-cursor-pagination - Use cursor pagination for large datasets
• data-chunk-by-id - Process large datasets with chunkById
• data-cursor-iteration - Use lazy cursors for memory-efficient iteration
• data-avoid-unbounded - Never use unbounded queries on large tables

6. Transactions & Locking (HIGH)

• lock-short-transactions - Keep transactions short and focused
• lock-deadlock-retry - Implement deadlock retry logic
• lock-pessimistic-locking - Use pessimistic locking for critical updates

7. Migrations (HIGH)

• migrate-zero-downtime - Write zero-downtime migrations
• migrate-concurrent-indexes - Create indexes concurrently in production
• migrate-safe-column-additions - Add columns safely without locking tables

8. Query Debugging (MEDIUM)

• debug-explain-analyze - Use EXPLAIN ANALYZE to understand query plans
• debug-laravel-debugbar - Use Laravel Debugbar to find query bottlenecks
• debug-slow-query-log - Enable and monitor slow query logs

9. Naming & Structure (HIGH)

• naming-tables - Table naming conventions (plural snake_case, pivot alphabetical)
• naming-columns - Column naming conventions (FKs, booleans, timestamps, polymorphic)
• naming-relationships - Relationship method naming (singular/plural matching)
• naming-migrations - Migration and index naming conventions

Essential Patterns

Prevent Lazy Loading in Development

isProduction());
    }
}

Cache Expensive Queries with Redis

    Post::query()
        ->withCount('comments')
        ->orderByDesc('comments_count')
        ->take(10)
        ->get()
);

Cursor Pagination for Large Datasets

where('published_at', 'orderByDesc('published_at')
    ->cursorPaginate(15);

Aggregate Counts Without Loading Relations

get();

foreach ($users as $user) {
    echo "{$user->name} has {$user->posts_count} posts";
}

Process Large Datasets with chunkById

where('last_login_at', 'subYear())
    ->chunkById(1000, function ($users) {
        foreach ($users as $user) {
            $user->update(['status' => 'inactive']);
        }
    });

Short Database Transactions

 auth()->id(),
        'total' => $this->calculateTotal(),
    ]);

    $order->items()->createMany($this->cartItems());

    $order->user->decrement('credits', $order->total);
});

How to Use

Read individual rule files for detailed explanations and code examples:

rules/query-eager-loading.md
rules/index-composite-indexes.md
rules/cache-remember.md
rules/_sections.md

Each rule file contains:

• YAML frontmatter with metadata (title, impact, tags)
• Brief explanation of why it matters
• Bad Example with explanation
• Good Example with explanation
• Laravel 13 and PHP 8.3 specific context and references

References

• Laravel Eloquent
• Laravel Queries
• Laravel Cache
• Laravel Pagination
• Laravel Migrations
• Laravel Redis

Full Compiled Document

For the complete guide with all rules expanded: AGENTS.md

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: AsyrafHussin
• Source: AsyrafHussin/agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.