Introduction
Eloquent ORM makes database interactions elegant, but poorly optimized queries can cripple your Laravel application's performance. While most developers recognize basic N+1 issues, true performance mastery comes from systematic query profiling, strategic eager loading, and advanced optimization techniques that most Laravel developers never explore. In this comprehensive guide, you'll discover battle-tested methods to transform sluggish Eloquent queries into lightning-fast operations. Whether you're handling million-row datasets or optimizing for Black Friday traffic spikes, these advanced profiling and tuning strategies will ensure your database layer performs at its absolute peak.
Understanding Query Performance Fundamentals
Before diving into optimization, it's crucial to understand the key metrics that define query performance:
Query Execution Time: The ultimate measure of performance
Database Load: Impact on server resources
Network Roundtrips: Critical for distributed systems
Memory Consumption: Especially important for large result sets
Lock Contention: Affects concurrent operations
Common Query Performance Killers:
- N+1 query problems
- Unoptimized joins and relationships
- Missing or improper indexes
- Large result sets without pagination
- Complex subqueries without optimization
Technique 1: Advanced N+1 Detection and Elimination
Beyond basic detection, implement systematic approaches to identify and eliminate N+1 queries.
Automated Detection System:
// app/Providers/AppServiceProvider.php
public function boot()
{
if ($this->app->isLocal() || $this->app->runningInConsole()) {
return;
}
QueryDetector::enable(
threshold: 50, // Queries per request threshold
callback: function ($count, $queries) {
Log::warning("Potential N+1 detected: {$count} queries", [
'url' => request()->url(),
'method' => request()->method(),
'sample_queries' => array_slice($queries, 0, 5)
]);
// Send to monitoring system
app(MonitoringService::class)->trackNPlusOne($count, $queries);
}
);
}
// app/Support/QueryDetector.php
class QueryDetector
{
public static function enable(int $threshold, callable $callback)
{
DB::listen(function ($query) use ($threshold, $callback) {
static $count = 0;
static $queries = [];
$count++;
$queries[] = [
'sql' => $query->sql,
'bindings' => $query->bindings,
'time' => $query->time
];
if ($count > $threshold) {
$callback($count, $queries);
// Reset to avoid multiple triggers
$count = 0;
$queries = [];
}
});
}
}
Advanced Eager Loading Patterns:
// Conditional eager loading
User::with([
'posts' => function ($query) {
$query->where('published', true)
->withCount('comments')
->latest()
->limit(5);
},
'profile' => function ($query) {
if (auth()->check()) {
$query->with('socialMedia');
}
}
])->get();
// Nested eager loading with constraints
Product::with([
'category' => function ($query) {
$query->with([
'parent' => function ($query) {
$query->with('ancestors');
}
]);
},
'variants' => function ($query) {
$query->where('in_stock', true)
->with('images');
}
])->get();
Technique 2: Query Profiling with Precision
Implement granular profiling to identify specific bottlenecks.
Advanced Profiling Middleware:
// app/Http/Middleware/QueryProfiler.php
public function handle($request, Closure $next)
{
if (! $this->shouldProfile()) {
return $next($request);
}
$startTime = microtime(true);
$originalHandler = DB::getEventDispatcher();
// Track query execution details
$queries = [];
$totalTime = 0;
DB::listen(function ($query) use (&$queries, &$totalTime) {
$queries[] = [
'sql' => $query->sql,
'bindings' => $query->bindings,
'time' => $query->time,
'connection' => $query->connectionName,
'trace' => $this->getCallingTrace(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10))
];
$totalTime += $query->time;
});
$response = $next($request);
$duration = microtime(true) - $startTime;
$this->recordProfileData($request, $response, $queries, $totalTime, $duration);
DB::setEventDispatcher($originalHandler);
return $response;
}
protected function recordProfileData($request, $response, $queries, $totalQueryTime, $totalDuration)
{
$profile = [
'url' => $request->fullUrl(),
'method' => $request->method(),
'status' => $response->status(),
'total_time' => $totalDuration,
'query_time' => $totalQueryTime,
'query_count' => count($queries),
'queries' => $queries,
'memory_usage' => memory_get_peak_usage(true),
'user_id' => auth()->id(),
'ip' => $request->ip(),
];
// Store in database or send to monitoring service
QueryProfile::create($profile);
// Log slow profiles
if ($totalQueryTime > 500 || count($queries) > 100) {
Log::warning('Slow query profile', $profile);
}
}
Profiling Analysis Techniques:
- Identify queries with high execution time relative to row count
- Detect duplicate queries that could be cached
- Find queries missing proper indexes
- Analyze query patterns by route/controller
Technique 3: Strategic Eager Loading Optimization
Move beyond basic with() to implement intelligent eager loading.
Dynamic Eager Loading Based on Context:
// app/Http/Controllers/UserController.php
public function show(User $user)
{
$relations = $this->determineEagerLoads();
$user = User::with($relations)->findOrFail($user->id);
return new UserResource($user);
}
protected function determineEagerLoads(): array
{
$relations = ['profile', 'roles'];
if (request()->has('with_posts')) {
$relations['posts'] = function ($query) {
$query->withCount('comments')
->latest()
->limit(10);
};
}
if (auth()->check() && auth()->user()->isAdmin()) {
$relations['sensitiveData'];
}
return $relations;
}
Lazy Eager Loading with Constraints:
// Only load relationships when needed
$users = User::where('active', true)->get();
// Later in the code, when needed
$users->load([
'posts' => function ($query) {
$query->where('published', true)
->withCount('comments')
->latest();
}
]);
// Conditional lazy loading
if ($condition) {
$users->load('comments');
}
Technique 4: Advanced Join Optimization
Optimize complex joins that Eloquent generates behind the scenes.
Manual Join Optimization:
// Instead of nested whereHas
User::whereHas('posts', function ($query) {
$query->whereHas('comments', function ($query) {
$query->where('approved', true);
});
})->get();
// Optimized with manual joins
User::select('users.*')
->join('posts', 'posts.user_id', '=', 'users.id')
->join('comments', 'comments.post_id', '=', 'posts.id')
->where('comments.approved', true)
->distinct()
->get();
Join Type Selection:
- Use
join()for required relationships - Use
leftJoin()for optional relationships - Consider
crossJoin()for Cartesian products (use with caution) - Implement
advancedJoin()for complex conditions
Join Indexing Strategy:
// For this join pattern
Order::join('users', 'users.id', '=', 'orders.user_id')
->join('addresses', 'addresses.id', '=', 'users.address_id')
->get();
// Required indexes:
// users: [id, address_id]
// addresses: [id]
// orders: [user_id]
Technique 5: Subquery Optimization
Transform inefficient subqueries into performant alternatives.
Subquery to Join Conversion:
// Inefficient subquery
Product::whereHas('orders', function ($query) {
$query->where('status', 'completed');
})->get();
// Optimized as join
Product::select('products.*')
->join('orders', 'orders.product_id', '=', 'products.id')
->where('orders.status', 'completed')
->distinct()
->get();
Advanced Subquery Patterns:
// Using subquery for aggregation
User::select('users.*')
->addSelect([
'total_spent' => Order::selectRaw('SUM(total)')
->whereColumn('user_id', 'users.id')
->where('status', 'completed'),
'recent_order_date' => Order::selectRaw('MAX(created_at)')
->whereColumn('user_id', 'users.id')
])
->get();
// Optimized with joins
User::select('users.*',
DB::raw('SUM(orders.total) as total_spent'),
DB::raw('MAX(orders.created_at) as recent_order_date'))
->leftJoin('orders', function ($join) {
$join->on('orders.user_id', '=', 'users.id')
->where('orders.status', '=', 'completed');
})
->groupBy('users.id')
->get();
Technique 6: Pagination Optimization for Large Datasets
Implement efficient pagination strategies for million+ row tables.
Keyset Pagination Implementation:
// Controller
public function index(Request $request)
{
$cursor = $request->input('cursor');
$query = Post::where('published', true)
->orderBy('created_at', 'desc')
->orderBy('id', 'desc');
if ($cursor) {
$decoded = Cursor::decode($cursor);
$query->where('created_at', '<', $decoded['created_at'])
->orWhere(function ($q) use ($decoded) {
$q->where('created_at', $decoded['created_at'])
->where('id', '<', $decoded['id']);
});
}
$posts = $query->limit(25)->get();
return response()->json([
'data' => $posts,
'next_cursor' => $posts->count() === 25 ?
Cursor::encode([
'created_at' => $posts->last()->created_at,
'id' => $posts->last()->id
]) : null
]);
}
// Custom Cursor class
class Cursor
{
public static function encode(array $values): string
{
return base64_encode(json_encode($values));
}
public static function decode(string $cursor): array
{
return json_decode(base64_decode($cursor), true);
}
}
Seek Method Implementation:
// For traditional offset pagination on large datasets
public function index(Request $request)
{
$page = $request->input('page', 1);
$perPage = 25;
$offset = ($page - 1) * $perPage;
if ($offset > 50000) {
// Use seek method for large offsets
$lastId = Post::where('published', true)
->orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->skip($offset - 1)
->value('id');
$posts = Post::where('published', true)
->where('id', '<', $lastId)
->orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->take($perPage)
->get();
} else {
// Standard pagination for small offsets
$posts = Post::where('published', true)
->orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->skip($offset)
->take($perPage)
->get();
}
return new PostCollection($posts);
}
Technique 7: Query Caching Strategies
Implement strategic caching for expensive queries.
Advanced Query Caching:
// app/Models/Concerns/CachedQueries.php
trait CachedQueries
{
public static function bootCachedQueries()
{
static::addGlobalScope('cached', function (Builder $builder) {
$builder->macro('cached', function (Builder $query, $seconds = 3600, $key = null) {
$key = $key ?: 'query:' . md5($query->toSql() . json_encode($query->getBindings()));
return Cache::remember($key, $seconds, function () use ($query) {
return $query->get();
});
});
$builder->macro('cachedForever', function (Builder $query, $key = null) {
$key = $key ?: 'query:' . md5($query->toSql() . json_encode($query->getBindings()));
return Cache::rememberForever($key, function () use ($query) {
return $query->get();
});
});
});
}
}
// Usage
$products = Product::where('active', true)
->cached(600, 'active_products_v2');
// With cache warming
Cache::remember('popular_products', 3600, function () {
return Product::popular()->take(100)->get();
});
Cache Invalidation Patterns:
// app/Observers/ProductObserver.php
public function updated(Product $product)
{
// Invalidate related caches
Cache::forget("product:{$product->id}");
Cache::forget('active_products_v2');
// Version the category cache
$version = Cache::get("category_version:{$product->category_id}", 1);
Cache::put("category_version:{$product->category_id}", $version + 1, 3600);
}
// In controller
public function show(Product $product)
{
$version = Cache::get("category_version:{$product->category_id}", 1);
$key = "product:{$product->id}:v{$version}";
return Cache::remember($key, 3600, function () use ($product) {
return new ProductResource($product);
});
}
Technique 8: Raw Query Optimization
Strategically use raw queries when Eloquent becomes inefficient.
When to Use Raw Queries:
- Complex aggregations
- Window functions
- Bulk operations
- Advanced joins that Eloquent struggles with
Optimized Raw Query Example:
// Instead of multiple Eloquent queries
$monthlySales = [];
for ($i = 0; $i < 12; $i++) {
$date = now()->subMonths($i);
$sales = Order::whereYear('created_at', $date->year)
->whereMonth('created_at', $date->month)
->where('status', 'completed')
->sum('total');
$monthlySales[$date->format('Y-m')] = $sales;
}
// Optimized single query
$monthlySales = Order::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as month, SUM(total) as total_sales')
->where('status', 'completed')
->where('created_at', '>=', now()->subYear())
->groupBy('month')
->orderBy('month', 'desc')
->pluck('total_sales', 'month')
->toArray();
// Fill missing months
for ($i = 0; $i < 12; $i++) {
$date = now()->subMonths($i)->format('Y-m');
if (! isset($monthlySales[$date])) {
$monthlySales[$date] = 0;
}
}
Raw Query Best Practices:
- Always use parameter binding
- Keep raw queries as simple as possible
- Document why raw query was necessary
- Test performance against Eloquent equivalent
Technique 9: Database-Specific Optimization
Leverage database-specific features for maximum performance.
MySQL Specific Optimizations:
// Use FORCE INDEX for known optimal paths
Product::where('category_id', 5)
->where('price', '>', 100)
->orderBy('created_at', 'desc')
->toSql(); // Add FORCE INDEX (idx_category_created) manually
// Using MySQL JSON functions with indexes
User::whereRaw("JSON_EXTRACT(preferences, '$.theme') = ?", ['dark'])
->get();
// Optimized with generated column and index
// In migration:
// $table->string('theme')->virtualAs("JSON_EXTRACT(preferences, '$.theme')");
// $table->index('theme');
User::where('theme', 'dark')->get();
PostgreSQL Specific Optimizations:
// Use PostgreSQL-specific JSONB operators
User::where('preferences->>theme', 'dark')->get();
// Window functions for complex calculations
$sales = DB::select(DB::raw(<<<'SQL'
SELECT
product_id,
total,
created_at,
AVG(total) OVER (
PARTITION BY product_id
ORDER BY created_at
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) as rolling_avg
FROM orders
WHERE status = 'completed'
SQL
));
Technique 10: Query Optimization for Relationships
Optimize complex relationship patterns that cause performance issues.
Has Many Through Optimization:
// Instead of:
$country->users()->with('orders')->get();
// Optimized query:
User::select('users.*', 'orders.*')
->join('orders', 'orders.user_id', '=', 'users.id')
->where('users.country_id', $country->id)
->get()
->groupBy('id');
Polymorphic Relationship Optimization:
// Instead of multiple queries for each type
$comments = Comment::with('commentable')->get();
// Optimized with manual joins
$comments = DB::table('comments')
->select('comments.*', 'posts.*', 'videos.*')
->leftJoin('posts', function ($join) {
$join->on('posts.id', '=', 'comments.commentable_id')
->where('comments.commentable_type', Post::class);
})
->leftJoin('videos', function ($join) {
$join->on('videos.id', '=', 'comments.commentable_id')
->where('comments.commentable_type', Video::class);
})
->get();
Many-to-Many Pivot Table Optimization:
// Instead of:
$user->roles()->withPivot('expires_at')->get();
// Optimized query with direct pivot access
DB::table('role_user')
->where('user_id', $user->id)
->join('roles', 'roles.id', '=', 'role_user.role_id')
->select('roles.*', 'role_user.expires_at')
->get();
Technique 11: Memory Optimization for Large Result Sets
Handle large datasets without exhausting memory.
Chunked Processing:
// Process users in chunks
User::where('active', true)->chunk(500, function ($users) {
foreach ($users as $user) {
// Process user
$this->processUser($user);
}
// Clear memory between chunks
unset($users);
gc_collect_cycles();
});
// Using cursor for even better memory efficiency
foreach (User::where('active', true)->cursor() as $user) {
// Process user
$this->processUser($user);
}
Lazy Collections:
// Process large datasets with lazy collections
User::where('active', true)
->cursor()
->filter(function ($user) {
return $user->orders()->where('total', '>', 1000)->exists();
})
->map(function ($user) {
return [
'id' => $user->id,
'name' => $user->name,
'high_value_orders' => $user->orders()->where('total', '>', 1000)->count()
];
})
->chunk(100)
->each(function ($chunk) {
// Process chunk of transformed data
$this->storeResults($chunk);
});
Technique 12: Query Optimization for API Responses
Optimize queries specifically for API resource transformation.
Eager Loading Based on Requested Fields:
// app/Http/Controllers/ApiController.php
protected function determineEagerLoads(array $fields): array
{
$relations = [];
if (in_array('posts', $fields)) {
$relations['posts'] = function ($query) {
$query->select('id', 'user_id', 'title', 'created_at')
->withCount('comments')
->latest()
->limit(5);
};
}
if (in_array('profile', $fields)) {
$relations['profile'] = function ($query) {
$query->select('id', 'user_id', 'bio', 'location');
};
}
return $relations;
}
// Usage
$relations = $this->determineEagerLoads($request->input('fields', []));
$user = User::with($relations)->findOrFail($id);
return new UserResource($user);
Selective Field Loading:
// Only select necessary fields
User::select('id', 'name', 'email', 'created_at')
->with([
'posts' => function ($query) {
$query->select('id', 'user_id', 'title', 'created_at')
->withCount('comments');
}
])
->findOrFail($id);
Technique 13: Advanced Indexing for Query Optimization
Strategic indexing specifically for your Eloquent query patterns.
Covering Indexes for Common Queries:
// 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']);
Partial Indexes for Filtered Queries:
// Only index active users (50% of table)
DB::statement('CREATE INDEX idx_users_active ON users (email) WHERE active = 1');
// For high-value orders
DB::statement('CREATE INDEX idx_orders_high_value ON orders (total) WHERE total > 1000');
Technique 14: Query Optimization Pipeline
Implement a systematic approach to query optimization.
Optimization Workflow:
- Identify slow queries through profiling
- Analyze with EXPLAIN to understand execution plan
- Determine if the issue is:
- Missing index
- Inefficient join
- N+1 problem
- Large result set
- Apply targeted optimization technique
- Measure performance impact
- Document the optimization for future reference
EXPLAIN Analysis Guide:
- Look for "Using filesort" or "Using temporary" - indicates inefficient sorting
- Check "type" column: "ref" or "range" is good, "ALL" means full table scan
- Verify "key" column shows the expected index
- Check "rows" column for estimated rows examined
Technique 15: Automated Query Optimization System
Build a system that continuously identifies and optimizes queries.
Implementation:
// app/Services/QueryOptimizer.php
class QueryOptimizer
{
protected $slowQueryThreshold = 100; // ms
protected $nPlusOneThreshold = 50; // queries per request
public function analyzeQuery($sql, $time, $bindings = [])
{
if ($time > $this->slowQueryThreshold) {
$this->analyzeSlowQuery($sql, $time, $bindings);
}
}
public function analyzeRequest($queries)
{
if (count($queries) > $this->nPlusOneThreshold) {
$this->detectNPlusOne($queries);
}
}
protected function analyzeSlowQuery($sql, $time, $bindings)
{
// Parse query to identify tables and conditions
$analysis = $this->parseQuery($sql);
// Check for missing indexes
$missingIndexes = $this->checkMissingIndexes($analysis);
if (! empty($missingIndexes)) {
Log::info('Missing indexes detected', [
'query' => $sql,
'bindings' => $bindings,
'time' => $time,
'suggested_indexes' => $missingIndexes
]);
}
// Check for inefficient patterns
$inefficiencies = $this->checkInefficientPatterns($analysis);
if (! empty($inefficiencies)) {
Log::info('Inefficient query patterns detected', [
'query' => $sql,
'bindings' => $bindings,
'time' => $time,
'issues' => $inefficiencies
]);
}
}
protected function detectNPlusOne($queries)
{
// Group queries by pattern
$patterns = [];
foreach ($queries as $query) {
$normalized = $this->normalizeQuery($query['sql']);
$patterns[$normalized][] = $query;
}
// Find patterns with high repetition
foreach ($patterns as $pattern => $instances) {
if (count($instances) > 10) {
Log::warning('Potential N+1 pattern detected', [
'pattern' => $pattern,
'count' => count($instances),
'sample_queries' => array_slice($instances, 0, 3)
]);
}
}
}
}
// Register with query listener
DB::listen(function ($query) {
app(QueryOptimizer::class)->analyzeQuery(
$query->sql,
$query->time,
$query->bindings
);
});
Query Optimization Checklist
Before deploying to production:
- [ ] Implement query profiling system
- [ ] Analyze slow queries with EXPLAIN
- [ ] Eliminate all N+1 queries
- [ ] Implement strategic eager loading
- [ ] Add appropriate indexes
- [ ] Optimize pagination for large datasets
- [ ] Test memory usage with large result sets
- [ ] Monitor query performance in production
FAQ Section
How do I identify N+1 queries in Laravel?
Use these methods:
- Laravel Debugbar extension
- Clockwork browser extension
- Custom query logging middleware
- Telescope package
- Database slow query logs
- The QueryDetector class shown in Technique 1
When should I use chunk() vs cursor() for large datasets?
Use chunk() when:
- You need to process records in batches
- You need to modify records during processing
- Memory usage is a concern but not critical
Use cursor() when:
- You only need to read records (not modify)
- Memory efficiency is critical
- You're processing extremely large datasets
- You don't need the full Eloquent collection features
How can I optimize queries with multiple whereHas conditions?
Implement these strategies:
- Convert to manual joins where possible
- Use crossJoin with conditional filtering
- Implement subquery constraints
- Create database views for complex patterns
- Use EXISTS/NOT EXISTS instead of whereHas where appropriate
What's the best way to handle complex sorting and filtering in APIs?
Implement these patterns:
- Use database indexes for common sort/filter combinations
- Implement cursor-based pagination for large datasets
- Use query caching for common filter combinations
- Implement field selection to reduce data transfer
- Use database-specific features like PostgreSQL's JSONB for flexible filtering
Conclusion
Mastering Eloquent query optimization 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 query optimization. Remember that optimal query performance is highly specific to your application's data access patterns and traffic characteristics—there's no universal "best" approach.
The journey to peak database performance requires continuous monitoring, iterative tuning, and careful measurement of changes. Start with your slowest queries, implement one optimization technique this week, measure the impact, and build from there.
Ready to supercharge your Laravel application's database performance? Implement one query optimization technique from this guide and monitor the results. Share your optimization journey in the comments below, and subscribe for more Laravel performance guides!
SOCIAL SHARE CARD GENERATOR