Laravel Eloquent: 15 Database Indexing Strategies for Lightning-Fast Queries

Table Of Contents

• Introduction
• Understanding Database Index Fundamentals
• Strategy 1: Composite Index Optimization
• Strategy 2: Partial Index Implementation
• Strategy 3: Covering Index Strategies
• Strategy 4: Indexing JSON Columns
• Strategy 5: Indexing for Pagination Performance
• Strategy 6: Indexing for Eloquent Relationships
• Strategy 7: Index Maintenance Strategies
• Strategy 8: Indexing for Full-Text Search
• Strategy 9: Indexing for Time-Series Data
• Strategy 10: Indexing for Geospatial Queries
• Strategy 11: Indexing for Enum and Boolean Columns
• Strategy 12: Indexing for Soft Deletes
• Strategy 13: Index Monitoring and Analysis
• Strategy 14: Indexing for Complex Eloquent Queries
• Strategy 15: Automated Index Recommendation System
• Indexing Strategy Checklist
• FAQ Section
• Conclusion

Introduction

Slow database queries are the silent killers of Laravel application performance, but most developers never look beyond basic primary keys and foreign keys. While Eloquent makes database interactions elegant, poorly optimized queries can bring even the most robust application to its knees under load. In this comprehensive guide, you'll discover advanced database indexing techniques specifically tailored for Laravel applications that transform sluggish queries into lightning-fast operations. Whether you're dealing with million-row datasets or optimizing for Black Friday traffic, these battle-tested indexing strategies will ensure your Eloquent queries perform at their absolute peak. We'll dive deep into composite indexes, partial indexes, and advanced optimization techniques that most Laravel developers never explore.

Understanding Database Index Fundamentals

Before diving into implementation, it's crucial to understand how database indexes work in the context of Laravel:

• B-Tree Indexes: Default for most columns (excellent for range queries)
• Hash Indexes: Best for equality comparisons (limited to MySQL MEMORY tables)
• Full-Text Indexes: For text search operations
• Spatial Indexes: For geographic data

Key Indexing Principles for Laravel:

• Indexes speed up WHERE, JOIN, ORDER BY, and GROUP BY operations
• Each index adds overhead to INSERT, UPDATE, and DELETE operations
• The query optimizer may ignore poorly designed indexes
• Index selectivity matters (high-cardinality columns make better indexes)

Strategy 1: Composite Index Optimization

Composite indexes are your most powerful weapon for optimizing complex Eloquent queries.

Basic Implementation:

// database/migrations/2025_07_27_create_orders_table.php
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->decimal('total', 10, 2);
    $table->timestamp('created_at')->useCurrent();
    
    // Composite index for common query pattern
    $table->index(['user_id', 'created_at']);
});

Advanced Composite Indexing:

• Order columns based on selectivity (most selective first)
• Include columns used in WHERE clauses first
• Place range query columns last in the index
• Consider index-only scans by including all needed columns

Real-World Example:

// For this query:
Order::where('status', 'completed')
     ->whereBetween('created_at', [now()->subMonth(), now()])
     ->orderBy('total', 'desc')
     ->get();

// Optimal composite index:
$table->index(['status', 'created_at', 'total']);

Strategy 2: Partial Index Implementation

Partial indexes (also called filtered indexes) index only a subset of your data, reducing index size and improving performance.

MySQL Implementation:

// Only index active users (50% of table)
DB::statement('CREATE INDEX idx_users_active ON users (email) WHERE active = 1');

Laravel Migration Approach:

// database/migrations/2025_07_27_create_partial_indexes.php
public function up()
{
    DB::statement('CREATE INDEX idx_orders_high_value ON orders (total) WHERE total > 1000');
    DB::statement('CREATE INDEX idx_products_featured ON products (category_id) WHERE featured = true');
}

When to Use Partial Indexes:

• When querying a specific subset of data repeatedly
• For boolean flags with skewed distributions (e.g., 95% false)
• To optimize for your most common query patterns
• When full indexes are too large for memory

Strategy 3: Covering Index Strategies

Covering indexes include all columns needed for a query, eliminating the need to access the actual table data.

Implementation Example:

// For this query:
Order::select('id', 'total')
     ->where('user_id', 123)
     ->where('status', 'completed')
     ->get();

// Optimal covering index:
$table->index(['user_id', 'status', 'total', 'id']);

Covering Index Best Practices:

• Include all columns in SELECT, WHERE, and ORDER BY
• Order columns: equality columns → range columns → selected columns
• Be mindful of index size (don't include unnecessary columns)
• Test with EXPLAIN to confirm index-only scan

Verification with EXPLAIN:

EXPLAIN SELECT id, total 
FROM orders 
WHERE user_id = 123 AND status = 'completed';

-- Look for "Using index" in Extra column

Strategy 4: Indexing JSON Columns

Modern Laravel applications frequently use JSON columns, but indexing them requires special techniques.

MySQL JSON Indexing:

// Create virtual column for indexing
$table->unsignedInteger('shipping_country')->virtualAs('JSON_EXTRACT(shipping_info, "$.country")');
$table->index('shipping_country');

// Or direct JSON index (MySQL 5.7+)
DB::statement('ALTER TABLE users ADD INDEX idx_preferences_theme ((preferences->"$.theme"))');

PostgreSQL JSONB Indexing:

// GIN index for JSONB columns
DB::statement('CREATE INDEX idx_users_preferences ON users USING GIN (preferences)');

Laravel Query Optimization:

// Instead of:
User::whereRaw("JSON_EXTRACT(preferences, '$.theme') = ?", ['dark'])->get();

// Use indexed approach:
User::where('preferences->theme', 'dark')->get();

Strategy 5: Indexing for Pagination Performance

Pagination can become extremely slow with large datasets without proper indexing.

Cursor-Based Pagination Index:

// For cursor pagination on created_at
$table->index(['created_at', 'id']);

// Usage:
Post::where('published', true)
    ->orderBy('created_at', 'desc')
    ->orderBy('id', 'desc')
    ->cursorPaginate(15);

Seek Method Optimization:

// For traditional pagination with offset
$table->index(['category_id', 'created_at']);

// Query:
Product::where('category_id', 5)
        ->orderBy('created_at', 'desc')
        ->offset(50000)
        ->limit(25)
        ->get();

Advanced Pagination Techniques:

• Use keyset pagination instead of offset
• Implement index covering for pagination columns
• Consider materialized views for extremely large datasets
• Use separate indexes for different sort orders

Strategy 6: Indexing for Eloquent Relationships

Optimize common relationship patterns with strategic indexing.

Belongs To Relationships:

// Always index foreign keys
$table->foreignId('category_id')->constrained()->index();

Has Many Through Relationships:

// For has-many-through (e.g., Country → Users → Orders)
$table->index(['country_id', 'created_at']);
$table->index(['user_id', 'country_id']); // For reverse lookups

Polymorphic Relationships:

// For polymorphic relationships
$table->string('commentable_type');
$table->unsignedBigInteger('commentable_id');
$table->index(['commentable_type', 'commentable_id']);

Many-to-Many Pivot Tables:

// Optimal pivot table indexing
Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained()->index();
    $table->foreignId('role_id')->constrained()->index();
    
    // Composite index for unique constraints
    $table->unique(['user_id', 'role_id']);
    
    // Additional index for reverse lookups
    $table->index(['role_id', 'user_id']);
});

Strategy 7: Index Maintenance Strategies

Indexes require ongoing maintenance to remain effective.

Automated Index Analysis:

// app/Console/Commands/AnalyzeIndexes.php
public function handle()
{
    $tables = DB::select('SHOW TABLES');
    
    foreach ($tables as $table) {
        $tableName = reset((array)$table);
        $this->analyzeTableIndexes($tableName);
    }
}

protected function analyzeTableIndexes(string $table)
{
    $indexes = DB::select("SHOW INDEX FROM {$table}");
    
    foreach ($indexes as $index) {
        // Check for unused indexes
        if ($index->Seq_in_index == 1 && $index->Cardinality < 100) {
            $this->warn("Low cardinality index on {$table}.{$index->Column_name}");
        }
        
        // Check for duplicate indexes
        // (Implementation would compare index definitions)
    }
}

Index Maintenance Best Practices:

• Schedule regular ANALYZE TABLE operations
• Rebuild fragmented indexes with OPTIMIZE TABLE
• Monitor index usage with database-specific tools
• Remove unused indexes (they slow down writes)

Strategy 8: Indexing for Full-Text Search

Optimize Laravel Scout and native full-text search with proper indexing.

MySQL Full-Text Index:

// In migration
$table->text('content');
$table->fullText('content');

// Usage with whereFullText
Post::whereFullText('content', 'Laravel')->get();

PostgreSQL Full-Text Index:

// Create GIN index
DB::statement('CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector(\'english\', content))');

// Query using Laravel's whereRaw
Post::whereRaw("to_tsvector('english', content) @@ to_tsquery('english', ?)", ['laravel'])->get();

Advanced Full-Text Techniques:

• Use separate columns for stemmed and unstemmed text
• Implement trigram indexes for fuzzy matching
• Combine full-text with regular indexes for filtering
• Consider dedicated search engines (Meilisearch, Algolia) for complex needs

Strategy 9: Indexing for Time-Series Data

Optimize queries on timestamp-heavy tables like analytics and logs.

Time-Partitioned Indexing:

// For daily partitions
DB::statement('CREATE INDEX idx_analytics_date ON analytics (date)');

// For hourly rollups
DB::statement('CREATE INDEX idx_analytics_hour ON analytics ((date_trunc(\'hour\', created_at)))');

Time-Series Specific Strategies:

• Use BRIN indexes for PostgreSQL time-series data
• Implement time-based sharding
• Create covering indexes for common time-range queries
• Consider specialized time-series databases for extreme scale

Laravel Implementation:

// Scope for time-based queries
public function scopeLastWeek(Builder $query)
{
    return $query->whereBetween('created_at', [now()->subWeek(), now()]);
}

// Requires index on created_at

Strategy 10: Indexing for Geospatial Queries

Optimize location-based queries with spatial indexes.

MySQL Spatial Index:

// In migration
$table->point('location')->spatialIndex();

// Query
Location::whereRaw('ST_Distance(location, POINT(?, ?)) < ?', [
    $lat, $lng, 5000 // 5km
])->get();

PostgreSQL PostGIS Index:

// Create GIST index
DB::statement('CREATE INDEX idx_locations_gix ON locations USING GIST (location)');

// Query using Laravel's whereRaw
Location::whereRaw('ST_DWithin(location, ST_MakePoint(?, ?)::geography, 5000)', [
    $lng, $lat
])->get();

Geospatial Optimization Tips:

• Use appropriate SRID for your data
• Consider bounding box pre-filtering
• Implement geohashing for simpler queries
• Use spatial indexes only when necessary (they're expensive)

Strategy 11: Indexing for Enum and Boolean Columns

Special considerations for low-cardinality columns.

Boolean Column Indexing:

// Only index if distribution is skewed (e.g., 95% false)
DB::statement('CREATE INDEX idx_users_premium ON users (is_premium) WHERE is_premium = true');

Enum Column Strategies:

• Avoid enums in favor of foreign keys for large sets
• For small enums, consider partial indexes
• Use composite indexes with high-cardinality columns

When NOT to Index:

• Columns with very low cardinality (e.g., gender)
• Columns rarely used in WHERE clauses
• Columns with highly skewed distributions without partial indexes

Strategy 12: Indexing for Soft Deletes

Optimize queries with Laravel's soft delete functionality.

Basic Implementation:

// Always include deleted_at in relevant indexes
$table->index(['user_id', 'deleted_at']);

Advanced Soft Delete Indexing:

// Partial index for active records only
DB::statement('CREATE INDEX idx_orders_active ON orders (user_id) WHERE deleted_at IS NULL');

// Composite index for common query patterns
$table->index(['status', 'deleted_at', 'created_at']);

Soft Delete Query Optimization:

// Instead of:
Order::withTrashed()->where('user_id', 123)->get();

// Use:
Order::where('user_id', 123)->get(); // Automatically excludes trashed

// Requires index on user_id, deleted_at

Strategy 13: Index Monitoring and Analysis

Implement proactive index monitoring to identify optimization opportunities.

MySQL Index Monitoring:

-- Find unused indexes
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    STATS_TIMESTAMP
FROM 
    information_schema.STATISTICS
WHERE 
    TABLE_SCHEMA = 'your_database'
    AND INDEX_NAME != 'PRIMARY'
    AND NOT EXISTS (
        SELECT 1 
        FROM information_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE 
        WHERE OBJECT_SCHEMA = 'your_database'
        AND OBJECT_NAME = TABLE_NAME
        AND INDEX_NAME = STATISTICS.INDEX_NAME
    );

Laravel Integration:

// app/Providers/AppServiceProvider.php
public function boot()
{
    if ($this->app->isProduction()) {
        DB::listen(function ($query) {
            if (str_contains($query->sql, 'WHERE') && $query->time > 50) {
                Log::warning('Slow query detected', [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time' => $query->time
                ]);
                
                // Trigger index analysis for this table
                $this->analyzeQueryTable($query->sql);
            }
        });
    }
}

Key Metrics to Monitor:

• Index hit ratio (aim for >95%)
• Slow query frequency
• Index size growth
• Write performance impact

Strategy 14: Indexing for Complex Eloquent Queries

Optimize advanced query patterns like subqueries and unions.

Subquery Optimization:

// For this query:
Product::whereHas('orders', function ($query) {
    $query->where('status', 'completed');
})->get();

// Requires index on orders table:
// [product_id, status] for the subquery
// [id] for the join

Union Query Indexing:

// For union queries
$active = User::where('active', true);
$premium = User::where('is_premium', true);

$users = $active->union($premium)->get();

// Requires separate indexes:
// idx_users_active (active)
// idx_users_premium (is_premium)

Advanced Query Patterns:

• Use covering indexes for complex select statements
• Implement composite indexes for multi-column conditions
• Consider materialized views for extremely complex queries
• Break down complex queries into simpler indexed operations

Strategy 15: Automated Index Recommendation System

Build a system that recommends indexes based on query patterns.

Implementation:

// app/Services/IndexAdvisor.php
class IndexAdvisor
{
    protected $queryLog = [];
    
    public function registerQuery(string $sql, array $bindings, float $time)
    {
        $this->queryLog[] = [
            'sql' => $sql,
            'bindings' => $bindings,
            'time' => $time,
            'normalized' => $this->normalizeQuery($sql)
        ];
    }
    
    public function generateRecommendations(): array
    {
        $patterns = $this->analyzeQueryPatterns();
        $recommendations = [];
        
        foreach ($patterns as $pattern) {
            if ($pattern['avg_time'] > 50 && $pattern['count'] > 100) {
                $recommendations[] = $this->recommendIndexForPattern($pattern);
            }
        }
        
        return $recommendations;
    }
    
    protected function normalizeQuery(string $sql): string
    {
        // Remove values, standardize whitespace
        return preg_replace('/\'.*?\'|\d+/', '?', $sql);
    }
}

// Register with Laravel's query listener
DB::listen(function ($query) {
    app(IndexAdvisor::class)->registerQuery(
        $query->sql,
        $query->bindings,
        $query->time
    );
});

Integration with Deployment Pipeline:

# deploy.sh
php artisan index:analyze
php artisan index:recommend > index_recommendations.txt

# Review recommendations before applying
cat index_recommendations.txt
read -p "Apply index recommendations? (y/n) " response

if [ "$response" = "y" ]; then
    php artisan index:apply
fi

Indexing Strategy Checklist

Before implementing in production:

• Analyze slow query logs to identify candidates
• Verify index usage with EXPLAIN
• Test indexes in staging environment
• Monitor write performance impact
• Implement index monitoring system
• Schedule regular index maintenance
• Document all custom indexes

FAQ Section

How many indexes should I have per table?

There's no universal number, but a good guideline is:

• 1-2 indexes for small tables (<10k rows)
• 3-5 indexes for medium tables (10k-1M rows)
• 5-8 indexes for large tables (>1M rows) Focus on your most critical query patterns rather than indexing everything.

When should I use a composite index vs multiple single-column indexes?

Use composite indexes when:

• Queries consistently filter on multiple columns
• Columns have low individual selectivity but high combined selectivity
• You can leverage index-only scans Use single-column indexes when:
• Different queries filter on different columns
• Columns have very high individual selectivity
• Write performance is critical

How do I know if an index is actually being used?

Use database-specific tools:

• MySQL: EXPLAIN SELECT ... (look for "key" column)
• PostgreSQL: EXPLAIN ANALYZE SELECT ...
• Laravel: Enable query logging and check which indexes are referenced Also monitor index usage statistics in your database.

What's the performance impact of too many indexes?

Each additional index:

• Increases INSERT time by 1-5%
• Increases UPDATE time by 1-10% (depending on indexed columns)
• Consumes additional disk space (can be 20-100% of table size)
• May cause the query optimizer to make suboptimal choices Balance read and write performance based on your application's needs.

Conclusion

Mastering database indexing transforms your Laravel application from merely "working" to performing at its absolute peak. These 15 advanced techniques address the real-world challenges that emerge when moving beyond basic indexing. Remember that optimal indexing strategy is highly specific to your application's query patterns and data distribution—there's no universal "best" configuration.

The journey to peak database performance requires continuous monitoring, iterative tuning, and careful measurement of changes. Start with your slowest queries, implement one indexing strategy this week, measure the impact, and build from there.

Ready to supercharge your Laravel application's database performance? Implement one indexing technique from this guide and monitor the results. Share your indexing optimization journey in the comments below, and subscribe for more Laravel performance guides!

Further Reading:

• Laravel Query Optimization: Profiling and Tuning Eloquent
• Advanced Laravel Caching Strategies
• Laravel Queue Workers: Optimizing for Throughput