Scopes chained on top of scopes, relationships loaded in loops, casts running on columns you never read, global scopes you forgot exist — a deep dive into what Eloquent is actually sending to your database and the query-level optimisations that cut response times in half.
Eloquent is one of the best ORMs in any language. Its expressiveness, its relationship handling, its casting system — all genuinely good. But elegance at the PHP level can hide expense at the SQL level. The more Eloquent does for you, the easier it is to not notice what it’s actually doing.
This post is about making Eloquent’s SQL visible — reading the queries it generates, finding the expensive patterns, and applying the optimisations that actually matter. Not theoretical micro-optimisations. Patterns from real applications where query time was the bottleneck.
Step 0: See What Eloquent Is Actually Sending
You can’t optimise what you can’t see. Two tools for making Eloquent’s SQL visible:
Laravel Telescope
composer require laravel/telescope --dev
php artisan telescope:install
php artisan migrate
Open /telescope/queries — every query logged with execution time, bindings, and the file and line that triggered it. Filter by slow (set your threshold to 50ms). Find the N+1 patterns immediately.
DB::enableQueryLog()
For a targeted investigation in a specific code path:
DB::enableQueryLog();
// Run the code you're investigating
$orders = Order::with('user', 'items')->where('status', 'pending')->get();
dd(DB::getQueryLog());
// Shows: [{query, bindings, time}, ...]
The toSql() / dd() Pattern
Before running a query, inspect the SQL:
// See the raw SQL (with ? placeholders)
dd(Order::with('user')->where('status', 'pending')->toSql());
// See SQL + bindings
$query = Order::with('user')->where('status', 'pending');
dd([
'sql' => $query->toSql(),
'bindings' => $query->getBindings(),
]);
The explain() Insight
For queries that run but seem slow:
$results = DB::select(
DB::raw('EXPLAIN ' . Order::where('status', 'pending')->toSql()),
Order::where('status', 'pending')->getBindings()
);
dd($results);
// Shows: type, possible_keys, key, rows, Extra
// 'type' = 'ALL' and 'rows' = large number = missing index
The N+1 Problem: Still the Biggest Killer
The N+1 query problem is the most common, most impactful database performance issue in Laravel applications. It’s called N+1 because you execute 1 query to get N records, then N more queries to load each record’s relationship — for a total of N+1 queries.
// ✗ N+1 — 1 query for orders + 1 query per order for user
$orders = Order::where('status', 'pending')->get();
foreach ($orders as $order) {
echo $order->user->name; // Triggers SELECT * FROM users WHERE id = ? for each order
}
// If there are 100 pending orders: 101 queries
// If there are 1,000 pending orders: 1,001 queries
Eager Loading: The Standard Fix
// ✓ Eager loading — 2 queries total regardless of order count
$orders = Order::with('user')->where('status', 'pending')->get();
// Query 1: SELECT * FROM orders WHERE status = 'pending'
// Query 2: SELECT * FROM users WHERE id IN (1, 2, 3, ...)
foreach ($orders as $order) {
echo $order->user->name; // No additional queries
}
Nested Eager Loading
// Load relationships at multiple levels in one call
$orders = Order::with([
'user',
'user.address', // load user's address too
'items',
'items.product', // load each item's product
'items.product.category', // and each product's category
])->get();
Conditional Eager Loading
// Load specific columns from the relationship — don't load everything
$orders = Order::with([
'user:id,name,email', // only these columns
'items:id,order_id,product_id,quantity', // only these columns
])->get();
// Constrained eager loading — only load active products
$orders = Order::with([
'items' => fn($q) => $q->with([
'product' => fn($q) => $q->where('active', true)->select('id', 'name', 'price')
])
])->get();
Detecting N+1 in Development
// Throw an exception when an N+1 is detected — in AppServiceProvider
use Illuminate\Database\Eloquent\Model;
public function boot(): void
{
Model::preventLazyLoading(!app()->isProduction());
}
With preventLazyLoading() enabled, any lazy-loaded relationship throws a LazyLoadingViolationException in development. This makes N+1 problems impossible to miss during development.
Selecting Only What You Need
The most common query inefficiency after N+1 is SELECT *. Eloquent fetches all columns by default — including columns you never read, never cast, never display.
// ✗ Fetches all columns — including large text, JSON blobs, binary data
$users = User::all();
// ✓ Select only what you actually use
$users = User::select('id', 'name', 'email', 'created_at')->get();
// Or via query scoping
$users = User::query()
->select(['id', 'name', 'email'])
->where('active', true)
->get();
The Impact on Casts
Eloquent’s $casts array runs transformations when you access a cast column. If you never access the column, the cast never runs. But if you always fetch it:
class User extends Model
{
protected $casts = [
'preferences' => 'array', // JSON decode on every access
'metadata' => 'collection', // Collection::make on every access
'permissions' => 'array',
'settings' => 'array',
];
}
// ✗ Fetches all columns — preferences, metadata, permissions, settings all decoded
$user = User::find($id);
// ✓ Select only what the current context needs
// If you only need the user's name and email, don't fetch the JSON columns
$user = User::select('id', 'name', 'email')->find($id);
// preferences, metadata, permissions, settings: never fetched, never decoded
Global Scopes: The Silent Query Modifier
Global scopes are applied to every query on a model automatically. They’re excellent for soft deletes and tenant scoping. They become a performance problem when they’re forgotten, stacked, or unnecessarily complex.
// Where is the extra WHERE coming from?
// Check your model for HasFactory, SoftDeletes, and custom scopes
class Product extends Model
{
use SoftDeletes; // adds WHERE deleted_at IS NULL to every query
use BelongsToTenant; // adds WHERE tenant_id = ? to every query
}
// Plus a global scope registered in a service provider:
Product::addGlobalScope('active', fn($q) => $q->where('active', true));
// The actual query for Product::all():
// SELECT * FROM products
// WHERE deleted_at IS NULL
// AND tenant_id = 42
// AND active = 1
// Three WHERE conditions you might not have realised were there
Finding All Active Global Scopes
// In Tinker — list all global scopes on a model
app(\App\Models\Product::class)->getGlobalScopes();
Removing Global Scopes When You Don’t Need Them
// Remove soft delete scope for a specific query
$allProducts = Product::withTrashed()->get();
// Remove the active scope for an admin query
$allProducts = Product::withoutGlobalScope('active')->get();
// Remove all global scopes
$allProducts = Product::withoutGlobalScopes()->get();
// Remove a specific class-based scope
$allProducts = Product::withoutGlobalScope(ActiveScope::class)->get();
Scopes Chained on Scopes: The Compound Query Problem
Local scopes are elegant. They’re also easy to chain into queries that are expensive because each scope adds WHERE conditions that can prevent index usage.
// These scopes look clean individually:
class Order extends Model
{
public function scopeForUser(Builder $q, int $userId): Builder
{
return $q->where('user_id', $userId);
}
public function scopePending(Builder $q): Builder
{
return $q->where('status', 'pending');
}
public function scopeRecent(Builder $q): Builder
{
return $q->where('created_at', '>=', now()->subDays(30));
}
public function scopeHighValue(Builder $q): Builder
{
return $q->where('total', '>', 10000);
}
}
// ✗ Chained scopes generate a query MySQL struggles with
$orders = Order::forUser($userId)
->pending()
->recent()
->highValue()
->get();
// WHERE user_id = ? AND status = ? AND created_at >= ? AND total > ?
// Four conditions — does a composite index cover this query path?
Checking Index Coverage
// Run EXPLAIN to see if MySQL is using an index
$sql = Order::forUser($userId)->pending()->recent()->highValue()->toSql();
$bindings = Order::forUser($userId)->pending()->recent()->highValue()->getBindings();
$explain = DB::select('EXPLAIN ' . $sql, $bindings);
// type = 'ref' or 'range' = index used
// type = 'ALL' = full table scan — you need an index
Adding Composite Indexes for Common Query Paths
// Migration — composite index covering the most common query
Schema::table('orders', function (Blueprint $table) {
// Covers: WHERE user_id = ? AND status = ? ORDER BY created_at
$table->index(['user_id', 'status', 'created_at'], 'orders_user_status_date');
});
Chunking: Processing Large Datasets Without Memory Explosions
// ✗ Loads the entire table into memory
$products = Product::all();
foreach ($products as $product) {
$this->processProduct($product);
}
// For 100,000 products: ~500MB memory
// ✓ chunk() — processes 500 at a time
Product::chunk(500, function ($products) {
foreach ($products as $product) {
$this->processProduct($product);
}
});
// Peak memory: ~2.5MB (500 products at a time)
// ✓ chunkById() — more reliable for large datasets
// chunk() with an ORDER BY clause can miss or duplicate rows if data changes mid-chunk
Product::chunkById(500, function ($products) {
foreach ($products as $product) {
$this->processProduct($product);
}
});
// ✓ lazy() — lazy cursor, even more memory efficient
Product::lazy()->each(function ($product) {
$this->processProduct($product);
});
// Fetches one row at a time from the database cursor
When to Use Each
all() → Small datasets (< 1,000 rows), results needed all at once
chunk() → Large datasets, no data changes during processing
chunkById() → Large datasets, data may change during processing
lazy() → Very large datasets, sequential processing, minimum memory
lazyById() → Very large datasets, data may change during processing
The exists() vs count() vs first() Performance Difference
A common pattern: check if a record exists before doing something.
// ✗ count() loads row counts — more expensive than needed
if (Order::where('user_id', $userId)->where('status', 'pending')->count() > 0) {
// handle pending orders
}
// ✗ get() loads full rows — way more expensive
if (Order::where('user_id', $userId)->where('status', 'pending')->get()->isNotEmpty()) {
// handle pending orders
}
// ✓ exists() — generates SELECT EXISTS(...) — stops at first match
if (Order::where('user_id', $userId)->where('status', 'pending')->exists()) {
// handle pending orders
}
// SQL: SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = ? AND status = ? LIMIT 1)
// Database stops as soon as one row is found — never scans the full result set
// ✓ doesntExist() for the inverse
if (Order::where('user_id', $userId)->doesntExist()) {
// first order for this user
}
// ✓ when you need the record, use first() not get()
$order = Order::where('user_id', $userId)->where('status', 'pending')->first();
// Adds LIMIT 1 — stops at the first match
if ($order) { ... }
Relationship Counts Without Loading Relationships
A common need: display the number of related records without loading them.
// ✗ Loads all comments to count them — expensive for popular posts
$posts = Post::all();
foreach ($posts as $post) {
echo $post->comments->count();
// SELECT * FROM comments WHERE post_id = ? — loads all comment data just to count
}
// ✓ withCount() — adds a _count column using a subquery
$posts = Post::withCount('comments')->get();
foreach ($posts as $post) {
echo $post->comments_count;
// Subquery: SELECT COUNT(*) FROM comments WHERE post_id = posts.id
// No comment data loaded — just the count
}
// With conditions on the count
$posts = Post::withCount([
'comments',
'comments as approved_comments_count' => fn($q) => $q->where('approved', true),
])->get();
echo $post->approved_comments_count;
withExists() — Check If Related Records Exist
// ✓ withExists() — adds a boolean column using EXISTS subquery
$posts = Post::withExists('comments')->get();
foreach ($posts as $post) {
if ($post->comments_exists) {
// Show "View Comments" link
}
}
has() and whereHas(): Filtering by Relationship Existence
// ✓ Only posts that have at least one comment
$posts = Post::has('comments')->get();
// ✓ Posts with more than 10 comments
$posts = Post::has('comments', '>=', 10)->get();
// ✓ Filter based on related record conditions
$posts = Post::whereHas('comments', fn($q) =>
$q->where('approved', true)->where('created_at', '>=', now()->subDays(7))
)->get();
// ✗ Avoid whereHas for simple existence checks — has() is faster
$posts = Post::whereHas('comments')->get(); // slightly slower
$posts = Post::has('comments')->get(); // preferred
The whereHas Performance Warning
whereHas() generates a correlated subquery — for each row in the outer query, the subquery is evaluated. For large datasets, this can be slow:
-- What whereHas generates:
SELECT * FROM posts
WHERE EXISTS (
SELECT 1 FROM comments
WHERE comments.post_id = posts.id
AND comments.approved = 1
)
-- For 100,000 posts, this subquery evaluates 100,000 times
For high-performance scenarios with large tables, consider a join instead:
// ✓ For large tables, a join can be faster than a subquery
$posts = Post::join('comments', 'posts.id', '=', 'comments.post_id')
->where('comments.approved', true)
->distinct()
->select('posts.*')
->get();
The Cursor Pattern for Streaming Results
For very large datasets that need to be processed but not all kept in memory:
// cursor() — uses a PHP generator, fetches one row at a time
foreach (Order::where('status', 'pending')->cursor() as $order) {
ProcessOrder::dispatch($order);
// Only one Order instance in memory at any time
}
The difference between cursor() and lazy():
cursor()— keeps the database connection open, fetches rows one at a time. Uses almost no memory but holds a DB connection for the duration.lazy()— fetches in chunks (1000 by default), then yields each item. More efficient for the DB, slightly more memory.
Aggregates Without Loading Models
When you need aggregate values, use the query builder directly rather than loading Eloquent models:
// ✗ Loads all orders into memory to sum them
$totalRevenue = Order::where('status', 'completed')->get()->sum('total');
// ✓ Aggregate at the database level
$totalRevenue = Order::where('status', 'completed')->sum('total');
// SQL: SELECT SUM(total) FROM orders WHERE status = 'completed'
// Returns a scalar value — no model hydration, no memory
// Multiple aggregates in one query
$stats = Order::where('status', 'completed')
->selectRaw('COUNT(*) as count, SUM(total) as revenue, AVG(total) as avg_order')
->first();
echo $stats->count;
echo $stats->revenue;
echo $stats->avg_order;
Model Hydration Cost: Use toBase() for Read-Only Data
Every Eloquent get() call hydrates PHP model objects. Each model tracks:
- Its original attributes (for dirty checking)
- Its current attributes
- Its relations
- Its casts
For read-only operations where you just need the data, toBase() skips model hydration entirely:
// ✗ Full Eloquent hydration — model tracking overhead
$orders = Order::select('id', 'total', 'status')->get();
// Returns Collection of Order model instances
// ✓ toBase() — returns plain stdClass objects
$orders = Order::select('id', 'total', 'status')->toBase()->get();
// Returns Collection of stdClass — no model overhead, no dirty tracking
// For display-only data, plain objects are sufficient
// Cannot call model methods, relationships, or mutators
Indexes: The Optimisation That Does More Than Any Code Change
All the Eloquent optimisations in this post are marginal compared to missing indexes. A missing index on a high-traffic query turns a 2ms query into a 2,000ms query.
// Find queries that aren't using indexes
// Run in Tinker or a command:
$slowQueries = DB::select("
SELECT query, exec_count, avg_timer_wait/1000000000 as avg_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE avg_timer_wait > 100000000000 -- queries averaging > 100ms
ORDER BY avg_timer_wait DESC
LIMIT 20
");
Common Index Patterns
// Migration patterns for common query types
// WHERE user_id = ? ORDER BY created_at DESC
$table->index(['user_id', 'created_at']);
// WHERE status = ? AND created_at >= ?
$table->index(['status', 'created_at']);
// WHERE email = ? (login query)
$table->unique('email'); // unique index is also a lookup index
// Full-text search on a text column
$table->fullText('content');
// Partial index for soft deletes (PostgreSQL only)
// Dramatically smaller index for queries that filter by deleted_at IS NULL
DB::statement("CREATE INDEX orders_pending_idx ON orders (user_id, created_at) WHERE deleted_at IS NULL");
The Production Query Audit Workflow
A systematic approach to finding and fixing Eloquent performance issues:
Week 1: Enable logging and measure
→ Install Telescope (or use DB::enableQueryLog())
→ Enable Model::preventLazyLoading() in development
→ Run key user flows and look at Telescope's Queries tab
→ Sort by slowest queries
→ Identify the top 5 slow or repeated queries
Week 2: Fix N+1 problems
→ For every repeated query with the same structure, add ->with()
→ Verify fix with DB::enableQueryLog()
→ Expected result: 100+ queries per request reduced to < 10
Week 3: Fix slow single queries
→ Run EXPLAIN on each slow query
→ If type = 'ALL' and rows = large number: add index
→ If query fetches unused columns: add select()
→ If query chains unnecessary scopes: investigate and simplify
Week 4: Measure again
→ Compare Telescope query times before vs after
→ Measure response times on key endpoints
→ Look for remaining slow queries
Quick Reference: The Performance Checklist
N+1 prevention:
✓ Model::preventLazyLoading() enabled in development
✓ Every relationship accessed in a loop is eager-loaded with with()
✓ Nested relationships eager-loaded together: with(['user', 'user.address'])
✓ Only needed columns selected in with(): with('user:id,name,email')
Query efficiency:
✓ select() used when not all columns are needed
✓ exists() used for existence checks, not count() > 0 or get()->isNotEmpty()
✓ first() used when only one record is needed, not get()[0]
✓ withCount() used for relationship counts, not ->comments->count()
✓ sum(), count(), avg() used for aggregates, not get()->sum()
Memory management:
✓ chunk() or lazy() used for datasets > 1,000 records
✓ chunkById() used when data may change during processing
✓ cursor() used for streaming very large result sets
Global scopes:
✓ All global scopes on major models identified
✓ withoutGlobalScope() applied in admin/cross-tenant queries
✓ SoftDeletes scope explicitly removed when including trashed records
Indexes:
✓ EXPLAIN run on every slow query
✓ Composite indexes cover common WHERE + ORDER BY combinations
✓ No 'type = ALL' on tables with more than 1,000 rows
Final Thoughts
Eloquent is not the problem. Eloquent doing more than you realise, on columns you don’t need, with relationships you didn’t intend to load, through scopes you forgot were applied — that’s the problem.
The fix is visibility first. Run Telescope. Enable preventLazyLoading. Read the queries. Then fix the three most expensive patterns: N+1 relationships, missing indexes, and unnecessary SELECT *. These three account for the vast majority of Eloquent-related performance problems in production Laravel applications.
The query that takes 2ms with the right index takes 2,000ms without it. No amount of PHP-level optimisation compensates for a full table scan. Start with the database. Make the queries efficient. Then come back to the PHP layer if response times still aren’t where they need to be.
Measure, fix, measure again. In that order, always.