Database Optimization: From N+1 to Sub-Millisecond Queries

Database optimization separates production-grade applications from prototypes. When your application handles thousands of requests per minute, every millisecond counts. Here are battle-tested techniques from real enterprise systems.

Conquering the N+1 Problem

The N+1 query problem is the most common performance bottleneck in Laravel applications. It occurs when you fetch a collection of models and then lazy-load relationships in a loop, resulting in 1 + N queries.

// Bad: N+1 queries
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name;
}

// Good: Eager loading
$posts = Post::with('author')->get();

Strategic Database Indexing

Indexing is the single most impactful optimization. Focus on columns used in WHERE clauses, JOIN conditions, and ORDER BY statements. Use compound indexes carefully — column order matters significantly.

Schema::table('invoices', function (Blueprint $table) {
    $table->index('status');
    $table->index(['tenant_id', 'created_at']);
});

Query Profiling in Production

Laravel's query logging is invaluable, but for production, use dedicated tools. Monitor slow queries, identify repetitive patterns, and use EXPLAIN to understand query execution plans. A well-optimized query should execute in under 5ms for simple lookups and under 50ms for complex aggregations.