- Book: Decoupled PHP — Clean and Hexagonal Architecture for Applications That Outlive the Framework
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
You've seen this one. A nightly Laravel export takes 40+ minutes. The DBA says the database is fine. The Laravel logs say the queries are fast. The CPU graph on the worker is pinned at 100% the whole time. Nobody is looking at PHP. Someone rewrites the export with DB::table() and it finishes in a fraction of the time. Same SQL. Same indexes. The difference is Eloquent hydration.
This is the trade nobody puts on the README. Eloquent is great. It's also expensive. Five places where that cost shows up, with the fix for each, and a 50-line logger that tells you exactly where your queries are bleeding.
The default trade: ergonomics vs hydration cost
Eloquent does four things for every row it loads. It runs the query, parses the result set, hydrates a model object per row (calling the constructor, applying casts, setting original attributes for change tracking), and registers the model with Laravel's event dispatcher in case anything fires retrieved. Multiply that by 100,000 rows and you've added 100,000 object allocations, 100,000 cast resolutions, and 100,000 event-dispatcher round-trips to a query the database returned in 80ms.
The query builder skips all of that. You get a stdClass (or array, with ->toArray()) and the JSON encoder is happy. Most of the time you don't need anything else.
The rule that holds in practice: if the rows are going through your domain layer, hydrate them. If they're going to a CSV, a report, an aggregate, or an update statement, don't.
Case 1: Batch updates of 100k rows
This one shows up everywhere. The intent is "mark every order older than 90 days as archived." The Eloquent way:
Order::where('created_at', '<', now()->subDays(90))
->where('status', 'completed')
->chunk(500, function ($orders) {
foreach ($orders as $order) {
$order->status = 'archived';
$order->save();
}
});
That code loads every order into a model, mutates it, and fires an UPDATE per row. For 100k rows you get 100k UPDATE statements, 100k model hydrations, and saving/saved/updating/updated events firing every time. On a small EC2 box talking to a remote RDS, that's enough to push a batch into the tens of minutes.
The query-builder version:
DB::table('orders')
->where('created_at', '<', now()->subDays(90))
->where('status', 'completed')
->update(['status' => 'archived']);
One statement. The database does the work. Same workload finishes in seconds. The catch: no model events fire. If OrderArchived listeners are how you notify downstream systems, you've quietly skipped them.
The fix is to be explicit. Do the update in SQL, then dispatch the events you actually need:
$ids = DB::table('orders')
->where('created_at', '<', now()->subDays(90))
->where('status', 'completed')
->pluck('id');
DB::table('orders')->whereIn('id', $ids)->update(['status' => 'archived']);
OrderArchived::dispatch($ids->all());
One event for the batch instead of 100k. Your downstream consumer probably wanted that anyway.
Case 2: Aggregation queries with relationships
You want a count of products per category, with the category name and its parent's slug.
$rows = Category::with('parent')
->withCount('products')
->get()
->map(fn ($c) => [
'name' => $c->name,
'parent' => $c->parent?->slug,
'count' => $c->products_count,
]);
Looks clean. Hydrates every Category model, hydrates every parent Category model, runs the subquery for products_count. If you have 800 categories, that's 800 hydrations for a result you're about to flatten into an array anyway.
Query-builder version:
$rows = DB::table('categories as c')
->leftJoin('categories as p', 'c.parent_id', '=', 'p.id')
->leftJoin('products', 'products.category_id', '=', 'c.id')
->select('c.name', 'p.slug as parent', DB::raw('COUNT(products.id) as count'))
->groupBy('c.id', 'c.name', 'p.slug')
->get();
One query, no hydration, and the database does the aggregation in SQL where it belongs. On the dataset above, this drops the response from hundreds of milliseconds to tens.
The honest answer here isn't "always use the query builder for aggregates." It's "stop forcing aggregates through models." If a report query has no place in the domain, write it as a query.
Case 3: Read-heavy listings where withCount lies
withCount is a convenience that papers over an N+1 by issuing one correlated subquery per relationship. That's still better than N+1. It's not free.
$posts = Post::with(['author', 'tags'])
->withCount(['comments', 'likes', 'shares'])
->latest()
->paginate(25);
Three subqueries, each running once per page. Look at the generated SQL:
select
`posts`.*,
(select count(*) from `comments` where `posts`.`id` = `comments`.`post_id`) as `comments_count`,
(select count(*) from `likes` where `posts`.`id` = `likes`.`post_id`) as `likes_count`,
(select count(*) from `shares` where `posts`.`id` = `shares`.`post_id`) as `shares_count`
from `posts`
order by `posts`.`created_at` desc
limit 25 offset 0;
On a table with 10M comments and no covering index on (post_id), those three correlated subqueries dominate the query. Worse, withCount runs them in the SELECT list, so MySQL can't always use an index on post_id the way it would for a separate GROUP BY query.
The fix is to materialise these counts. A comments_count column on posts, updated by a trigger or an event listener, makes the listing query a flat SELECT *. For data that changes less often than it's read, denormalisation is the right call. Eloquent and the query builder are both wrong here. The table layout is the bug.
When you can't denormalise, run the counts in a separate query and merge them in PHP:
$posts = Post::with(['author', 'tags'])->latest()->paginate(25);
$ids = $posts->pluck('id');
$counts = DB::table('comments')
->whereIn('post_id', $ids)
->groupBy('post_id')
->select('post_id', DB::raw('count(*) as c'))
->pluck('c', 'post_id');
$posts->each(fn ($p) => $p->comments_count = $counts[$p->id] ?? 0);
Two queries, both index-friendly, no correlated subqueries.
Case 4: Polymorphic relations and the IN-clause that blows up
Polymorphic relations are where Eloquent's ergonomics hide the most query cost. Comment belongs to a commentable (Post, Video, Photo) via commentable_type and commentable_id. Loading 1000 comments with with('commentable') looks like one query plus three. What it actually does:
select * from `comments` limit 1000;
select * from `posts` where `posts`.`id` in (1, 4, 7, ...); -- 600 ids
select * from `videos` where `videos`.`id` in (2, 9, 14, ...); -- 250 ids
select * from `photos` where `photos`.`id` in (3, 8, 11, ...); -- 150 ids
That looks fine at 1000 rows. At 100k comments, the IN clauses each have ~30-60k IDs. MySQL silently degrades to a full table scan on each child table because the optimiser bails out when the IN list crosses range_optimizer_max_mem_size. The plan changes from range to ALL. You won't see this in your dev environment.
The error you might see in production logs:
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders
That happens at 65,535 placeholders. Below that, the query "succeeds" but reads the entire table. You won't get a clear failure. Just a slow query.
The fix is to batch the polymorphic load yourself:
$comments = Comment::orderBy('id')->cursor();
foreach ($comments->chunk(2000) as $chunk) {
$byType = $chunk->groupBy('commentable_type');
foreach ($byType as $type => $rows) {
$ids = $rows->pluck('commentable_id');
$parents = DB::table((new $type)->getTable())->whereIn('id', $ids)->get()->keyBy('id');
$rows->each(fn ($c) => $c->setRelation('commentable', $parents[$c->commentable_id] ?? null));
}
// process the chunk
}
Same shape as with('commentable'), but you control the chunk size and you never blow past the placeholder limit. For reports, drop the model layer entirely and join comments against each child table in a UNION.
Case 5: Soft deletes and global scopes hiding the cost
Soft deletes are an Eloquent feature. The query builder doesn't know about them. That cuts both ways.
You write Post::all() and get a sensible result with deleted_at IS NULL appended. You write DB::table('posts')->get() and get every soft-deleted row too, which is a silent bug if the caller assumed the scope was applied.
The cost side: global scopes run for every Eloquent query in the app. A TenantScope that adds WHERE tenant_id = ? runs 800 times for 800 queries in a request. That's fine. But a WithLatestRevisionScope that adds a correlated subquery runs 800 times too, and now your request budget is gone.
The way to see this is to log every scope's effect on the query. A global scope that adds a single WHERE is cheap. A global scope that adds a JOIN or a subquery is the kind of thing that quietly eats your p99.
class WithLatestRevisionScope implements Scope
{
public function apply(Builder $builder, Model $model): void
{
$builder->addSelect([
'latest_revision_id' => DB::table('revisions')
->whereColumn('post_id', 'posts.id')
->orderByDesc('created_at')
->limit(1)
->select('id'),
]);
}
}
That subquery runs once per row in the result set, every time, on every query. If your Post model has this scope and you load 10k posts for a report, you've added 10k subqueries. The fix is to drop the scope on report queries: Post::withoutGlobalScope(WithLatestRevisionScope::class)->....
How to measure: a 50-line slow-query logger
Stop guessing. DB::listen() gives you every query and its time. Wire it up in a service provider and you'll know exactly which calls are slow within an hour.
namespace App\Providers;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\ServiceProvider;
class SlowQueryLoggerServiceProvider extends ServiceProvider
{
private const THRESHOLD_MS = 100;
public function boot(): void
{
if (! $this->shouldLog()) {
return;
}
DB::listen(function (QueryExecuted $query) {
if ($query->time < self::THRESHOLD_MS) {
return;
}
$sql = $this->bindValues($query->sql, $query->bindings);
Log::channel('slow_queries')->warning('slow query', [
'time_ms' => $query->time,
'connection' => $query->connectionName,
'sql' => $sql,
'trace' => $this->callerFrame(),
]);
});
}
private function shouldLog(): bool
{
return app()->environment(['staging', 'production'])
&& config('database.slow_query_log', true);
}
private function bindValues(string $sql, array $bindings): string
{
foreach ($bindings as $binding) {
$value = is_string($binding) ? "'" . addslashes($binding) . "'" : $binding;
$sql = preg_replace('/\?/', (string) $value, $sql, 1);
}
return $sql;
}
private function callerFrame(): ?string
{
// skip framework frames so the log points at YOUR code
foreach (debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 25) as $frame) {
$file = $frame['file'] ?? '';
if (str_contains($file, '/app/') && ! str_contains($file, '/vendor/')) {
return ($frame['file'] ?? '') . ':' . ($frame['line'] ?? '');
}
}
return null;
}
}
Ship that to staging for a day. The trace field tells you which controller, job, or command produced the slow query. Sort by time_ms descending and the top 20 entries are your hit list.
If you want this in dev too, drop the shouldLog() gate and tail storage/logs/slow_queries.log. You'll find your N+1s in 15 minutes.
The hybrid that works
The pattern that holds up across Laravel projects that ship and keep shipping:
- Eloquent for the domain layer. Models with relations, business rules, validation, events. Reads where you need a hydrated object to do something interesting with it.
- Query builder for batch writes. Updates, deletes, and inserts at scale go through
DB::table(). Dispatch one event for the batch. - Query builder for reports. Anything that ends in
->toArray()or a CSV download. The model layer adds no value when the rows aren't going through domain logic. - Raw SQL for analytics and migrations. Window functions, CTEs, and bulk reshapes belong in SQL where the query planner can do its job.
Eloquent isn't slow. It's expensive per row. The fix isn't to abandon it. It's to know when you're paying the per-row cost for rows that don't need it.
What's the slowest query in your codebase right now, and is it slow because of SQL or because of hydration?
If this was useful
Most "slow Laravel" stories are really "we let the ORM own work that didn't belong to it" stories. The framework gives you the same database access at every layer; what changes is the cost per row and what you do with it. Decoupled PHP walks through the architectural layer your codebase reaches for after it outgrows the framework defaults, where Eloquent stops at the boundary, query builders own reports and migrations, and the domain stays free of database concerns.
Available on Kindle, Paperback, and Hardcover. English, German, and Japanese editions out now — Portuguese and Spanish coming soon.
Top comments (0)