Short version for the impatient: the N+1 query problem is almost never a mystery, it’s just invisible until you count. Add a query counter to one test and you’ll find it in an afternoon.<\/p>\n
Now the longer version, which involves me looking slightly stupid.<\/p>\n
A while back I had an orders dashboard that felt fine locally and took about four seconds in production. I assumed it was the database being slow, so I spent an evening adding indexes to a table that already had every index it needed. Then I finally turned on query logging and watched 1,903 queries scroll past for a single page load. Fifty orders, each one fetching its customer, each one counting its line items. The database wasn’t slow. I was asking it the same question four hundred times and being surprised it took a while to answer.<\/p>\n
That’s N+1. One query to get a list, then N more queries because you touched a relationship inside a loop. Every ORM has a version of this. Eloquent’s version is friendlier than most, which is exactly why it’s so easy to ship.<\/p>\n
Here’s the controller. Nothing wrong with it at a glance:<\/p>\n
public function index()\n{\n $orders = Order::latest()->paginate(50);\n\n return view('orders.index', compact('orders'));\n}\n<\/code><\/pre>\nAnd here’s the Blade template that quietly ruins it:<\/p>\n
@foreach ($orders as $order)\n <tr>\n <td>{{ $order->reference }}<\/td>\n <td>{{ $order->customer->name }}<\/td>\n <td>{{ $order->items->count() }} items<\/td>\n <\/tr>\n@endforeach\n<\/code><\/pre>\nOne query for the page of orders. Then fifty for $order->customer<\/code>. Then fifty more for $order->items<\/code>. That’s 101 round trips to render a table.<\/p>\nWhat makes this hard to spot is that it’s split across two files. The controller looks clean. The template looks clean. The bug lives in the gap between them, and it doesn’t exist until someone writes a @foreach<\/code>. I’ve reviewed pull requests that introduced this and approved them, because the diff was three lines of Blade and none of those lines said “run fifty queries”.<\/p>\nThe fix is the boring one everyone already knows:<\/p>\n
$orders = Order::query()\n ->with('customer:id,name')\n ->withCount('items')\n ->latest()\n ->paginate(50);\n<\/code><\/pre>\nThree queries now. One for the orders, one for the customers, one for the item counts. In the template, $order->items->count()<\/code> becomes $order->items_count<\/code>, which is a column on the result rather than a fresh query. Laravel’s eager loading docs<\/a> cover the syntax properly if you want the full list of what with()<\/code> accepts.<\/p>\nEager loading fixes the query count, not necessarily the page<\/h2>\n
This is the part I got wrong for a good two weeks, and it cost me a lot of confused staring.<\/p>\n
The first time I fixed an N+1 by adding with()<\/code>, the query count dropped from 1,903 to 4 and the page got slower<\/em>. Not much slower. But it didn’t get faster, which was the entire point of the exercise.<\/p>\nThe reason: I’d eager loaded a relationship with a text<\/code> column in it. Something like with('customer')<\/code> on a customers table that had a notes<\/code> column holding a few kilobytes of free text per row. Fifty small queries became one query that dragged half a megabyte across the wire and then hydrated fifty full Eloquent models out of it. Fewer queries, more bytes, more object construction.<\/p>\nSo two habits I picked up:<\/p>\n
Select only what you need. with('customer:id,name')<\/code> instead of with('customer')<\/code>. The id<\/code> is mandatory, by the way, because Eloquent needs it to match children back to parents. Leave it out and you get an empty relation and a very confusing ten minutes.<\/p>\nUse withCount()<\/code> when you only want a number. Loading four thousand line items to call count()<\/code> on them in PHP is a waste of memory that the database will happily do for you in one pass.<\/p>\nThe general principle is that query count is a proxy metric. It’s a good proxy, and I still use it as my first signal, but the thing you actually care about is time and memory. Sometimes those move together. Sometimes they don’t. I hit a similar version of this when I was benchmarking search, which I wrote up in my post on Postgres full-text search<\/a>. The fastest option on paper lost once real row sizes showed up.<\/p>\nThe one setting that stops this at the source<\/h2>\n
If you take one thing from this post, take this. Laravel can throw an exception the moment you lazy load a relationship, so the bug fails loudly in development instead of quietly in production.<\/p>\n
In AppServiceProvider<\/code>:<\/p>\nuse Illuminate\\Database\\Eloquent\\Model;\n\npublic function boot(): void\n{\n Model::preventLazyLoading(! $this->app->isProduction());\n}\n<\/code><\/pre>\nThat’s it. Touch $order->customer<\/code> without eager loading it and you get a LazyLoadingViolationException<\/code> with the model and relation named in the message. The Eloquent strictness docs<\/a> explain the related toggles too, including preventSilentlyDiscardingAttributes<\/code>, which has caught its own share of my mistakes.<\/p>\nThe ! $this->app->isProduction()<\/code> bit matters. You want the exception in local and CI. You do not want your checkout page throwing 500s at 2am because someone missed a with()<\/code> in a code path nobody tested. Fail loud where it’s cheap, degrade quietly where it isn’t.<\/p>\nI resisted this setting for longer than I should have, because the first time I switched it on it broke about nine pages at once and I assumed the setting was too aggressive. It wasn’t. Those nine pages were all doing N+1. It was just an honest bill arriving all at once.<\/p>\n
Two things to know before you flip it. Turning it on in an existing app is a day of work, not a five minute change, so budget for that. And if you have a genuinely intentional lazy load, Model::preventLazyLoading(false)<\/code> inside a narrow scope beats disabling the whole thing.<\/p>\nCatching it in CI, not in code review<\/h2>\n
Humans are bad at counting queries by reading diffs. I’ve proven this repeatedly. So I let the test suite do it.<\/p>\n
The trick is that DB::listen<\/code> gives you a hook on every query, and a counter around a request is about six lines:<\/p>\nuse Illuminate\\Support\\Facades\\DB;\n\ntest('orders index stays under a query budget', function () {\n Order::factory()->count(30)->hasItems(4)->create();\n\n $queries = 0;\n DB::listen(function () use (&$queries) {\n $queries++;\n });\n\n $this->get('\/orders')->assertOk();\n\n expect($queries)->toBeLessThan(10);\n});\n<\/code><\/pre>\nThe number 10 is arbitrary and that’s fine. The point isn’t precision, it’s the direction of the alarm. If someone adds a relationship access to that Blade template, this test goes from 4 queries to 34 and fails. Nobody has to notice anything during review.<\/p>\n
I put one of these on every page that renders a list. It’s maybe fifteen minutes of work per route and it has caught more real regressions than any linter I’ve installed.<\/p>\n
For local development there’s also beyondcode\/laravel-query-detector<\/a>, which watches requests as you browse and warns you when a relationship gets hit repeatedly. It can output to the log, the browser console, or Debugbar. I keep it on log output, because browser alerts during development make me want to throw my laptop into the sea.<\/p>\nWhen eager loading is the wrong answer entirely<\/h2>\n
Not every repeated query wants with()<\/code>.<\/p>\nLarge exports are the obvious one. If you’re iterating 200,000 rows, eager loading the relation means holding all of it in memory at once, and your job dies on a box with 512MB. lazyById()<\/code> keeps memory flat while still chunking sensibly:<\/p>\nOrder::with('items')->lazyById(500)->each(function (Order $order) {\n \/\/ one chunk of 500 in memory at a time\n});\n<\/code><\/pre>\nAggregates are the other one I see people get wrong. If the answer is a single number, don’t hydrate models to get it. This:<\/p>\n
$total = Order::with('items')->get()\n ->sum(fn ($order) => $order->items->sum('price'));\n<\/code><\/pre>\nis a great way to load your entire orders table into PHP so you can add up a column. The database will do it in one pass without constructing a single object.<\/p>\n
And sometimes the honest answer is that a page is asking for too much, and it needs a dedicated read query or a summary table rather than a cleverer ORM call. I’ve stopped treating “write raw SQL for this one endpoint” as a defeat.<\/p>\n
Background jobs deserve a mention here too, since moving heavy work off the request is often the real fix. I wrote about how I run that side of things in my Horizon setup post<\/a>.<\/p>\nWhat to do this week<\/h2>\n
Pick your slowest list page. Add Model::preventLazyLoading(! $this->app->isProduction())<\/code> to AppServiceProvider<\/code>, load the page locally, and see what explodes. Fix whatever the exception names, then write one query-budget test around that route so it stays fixed.<\/p>\nThat’s a couple of hours, most of it mechanical, and in my experience it finds more wall-clock time than any index you were about to add. Most of the Laravel work I do these days starts with exactly this kind of unglamorous measurement pass, and you can see some of what comes out of it in my project work<\/a>.<\/p>\nThe database was never the problem. The loop was.<\/p>\n","protected":false},"excerpt":{"rendered":"
Eloquent’s N+1 problem hid a four-second page load from me for weeks. Here is how I catch it now: strict lazy loading plus a query budget test in CI.<\/p>\n","protected":false},"author":2,"featured_media":538,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"Eloquent's N+1 problem hid a four-second page load from me for weeks. Here is how I catch it now: strict lazy loading plus a query budget test in CI.","rank_math_focus_keyword":"n+1 query problem","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[147,52],"tags":[49,606,607,260,56,605,53],"class_list":["post-539","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backend","category-php","tag-backend","tag-database-performance","tag-eager-loading","tag-eloquent","tag-laravel","tag-n-1-query","tag-php"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=539"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/539\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/538"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}