The Query Layer: N+1, Indexes, and Eloquent Reality

The Query Layer is where a Laravel application that grew usually turns out to be spending its time, and the reason is structural rather than careless. Eloquent will happily issue a query inside a template loop, and nothing in the language marks the moment it happens.

Key takeaways

• Laravel's relationships documentation shows the shape exactly: retrieving twenty-five books and accessing each book's author executes twenty-six queries, and with('author') reduces it to two.
• Aggregates belong in the query. withCount, withSum, withMin, withMax, withAvg and withExists add a subquery to the parent select instead of loading a relationship to count it.
• Make lazy loading an exception rather than a habit. Model::preventLazyLoading, guarded so it is active everywhere except production, throws Illuminate\Database\LazyLoadingViolationException on a lazy load.
• MySQL's manual states that any leftmost prefix of a multi-column index can be used, so an index on three columns serves lookups on the first, the first two, and all three, and nothing else.
• PostgreSQL's manual warns that the rows value in a plan is the number of rows emitted by the node rather than the number processed or scanned, which is the reading that finds a bad index.

Read this after Chapter 2, because a query problem is a service-time problem and service time is one of the two inputs to utilisation. Chapter 4 handles what happens when the result set itself is large, and Chapter 5 handles what happens when two of these queries want the same row.

A page renders a list of orders with the customer name next to each. Nobody thinks about it, because in Blade it is one property access.

The route is not slow in staging, where the table holds a few hundred rows and the database runs on the same machine. It is slow in production, where the database is a network hop away and the page shows fifty rows.

Fifty-one queries, each of them fast, each of them a round trip. The slow query log is empty and the page takes a second to render.

N+1 is a loop nobody wrote

The failure is named after its arithmetic: one query to fetch the parents, then one more per parent. Laravel's own relationships documentation gives the canonical example, and it is worth quoting because the number is theirs rather than mine.

Retrieving twenty-five books and accessing each book's author executes twenty-six queries: one for the books, then one per book. Adding with('author') reduces it to two, one for the books and one for all the authors matching the collected foreign keys.

What makes it hard to catch is that no individual query is slow. Each is an indexed primary key lookup that a database serves in well under a millisecond, so every tool that alarms on slow queries stays quiet. The cost is round trips, connection time and model hydration, and it scales with rows on the page rather than with data volume.

That is why Chapter 1 put query count per request at the top of the instrument list. Count is the signal. Duration is not.

Eager loading is a second query, not the absence of one

Eager loading is often described as removing the queries. It does not. It replaces N of them with one, and the replacement has its own shape you should understand.

The with method loads relationships alongside the parent query. The load method does the same after the fact, for a collection you already hold. Both accept nested relationships through dot notation. Both accept a closure to constrain the loaded rows, which is how you avoid pulling ten thousand related rows in order to display three.

The failure mode of eager loading is over-fetching. A route that eagerly loads five relationships to render one of them has traded fifty small queries for five large ones, and the large ones hydrate models nothing reads. Load what the view uses. The illustrative shape, not runnable as written:

Order::with(['customer:id,name', 'lines' => fn ($q) => $q->limit(3)])->paginate(25);

Constrained eager loads are the underused half of this feature. Most N+1 fixes that made a route slower were unconstrained ones.

Aggregates belong in the query, not in the collection

One whole class of N+1 gets worse when you eager load it. It is the class where the view needs a number rather than the rows themselves.

Laravel documents the aggregate helpers for exactly this: withCount, withSum, withMin, withMax, withAvg and withExists, with loadCount for a collection in hand. Each adds a subquery to the parent select, so the count arrives with the parents and no relationship rows are loaded at all.

withExists deserves separate attention because it encodes an intent people express badly. A view that only needs to know whether any related row exists does not need a count, and a count on a large relationship is a scan the existence check can skip.

Eager loading a relationship in order to call count() on the resulting collection is the most common version of this mistake. It is invisible in the query log, because the query count looks correct. The row count is what is wrong.

Make the framework throw instead of hoping

Code review does not reliably catch lazy loading, because the offending line looks like a property access and usually is one. Laravel gives you a switch that turns the problem into a test failure.

Model::preventLazyLoading makes Eloquent throw Illuminate\Database\LazyLoadingViolationException when a relationship is lazily loaded. Laravel's documentation passes it a boolean guarded on the environment, so that it stays off in production. That guard is deliberate. You would rather serve a slow page than a five hundred. Put the call in a service provider, run the test suite, and expect the first run to fail in places you did not expect.

Laravel also ships automatic eager loading. Model::automaticallyEagerLoadRelationships() enables it globally, and withRelationshipAutoloading enables it for a single collection. Both are genuinely useful and neither is a substitute for the exception.

The distinction matters. Autoloading removes the round trips and leaves the design unexamined, so a view that touches nine relationships still touches nine relationships. The exception tells you it does.

Turn the exception on first. Turn autoloading on second, deliberately.

An index is a sorted structure with a leftmost rule

Most index arguments in a Laravel team are settled by intuition, and intuition gets column order wrong. MySQL's manual states the rule plainly.

Any leftmost prefix of a multiple-column index can be used, so an index on three columns serves lookups on the first column, on the first two, and on all three. It does not serve a lookup on the second column alone. Column order in the index definition is therefore a decision about which queries the index exists for.

The same page lists five more uses beyond equality matching. Eliminating rows. Driving joins between columns of the same type. Finding the minimum or maximum of an indexed column. Sorting and grouping, when done on a leftmost prefix. And resolving a query entirely from the index without touching the rows at all.

That last row is the one worth designing for. An index carrying every column a query reads is a covering index, and it turns a lookup plus a row fetch into a lookup.

Read the plan, not the query

An index you added is not an index the planner used, and the only way to know is to ask. This is also where most performance conversations become falsifiable.

PostgreSQL's documentation is precise about what you get. Plain EXPLAIN prints the planner's estimated cost, row count and width per node. Adding ANALYZE executes the query and reports actual times, actual row counts and loop counts, implicitly enabling BUFFERS. The page also carries a warning worth internalising. The rows value is not the number of rows processed or scanned by the node. It is the number the node emitted.

Two operational cautions. Because EXPLAIN ANALYZE runs the query, side effects happen, so a data-modifying statement must be wrapped: begin, explain analyze, roll back. And a plan is specific to the statistics and parameters it was produced with, so a plan captured against a staging dataset predicts staging.

MySQL's equivalent reading is the same discipline with different output. In both engines the question is identical. How many rows did the node emit, and how many did it have to look at to emit them.

Capture the plan into the pull request that changes the query. A plan pasted into a description survives review, survives the next person, and costs the author one command. A claim that the query is faster now survives nothing. It is a sentence about a feeling.

A worked measurement, composited and labelled

The details here are composited from ordinary production shapes rather than one system, and the shape is exact.

An orders listing filtered by status and sorted by date is slow. The first reading captures the plan with actual row counts and finds a node whose emitted row count is a small fraction of the rows it examined. That ratio is the whole finding: the filter is being applied after the read rather than by the index.

The second reading, after adding an index whose leftmost column is the filtered one and whose second column is the sort column, captures the same plan. The node now emits close to what it examines, and the sort node has disappeared because the ordering came from the index.

Neither reading is a latency claim, and that is deliberate. What changed is the ratio of examined to emitted rows and the presence of a sort, both of which are structural facts about the plan. They also predict how the query behaves as the table grows, which a stopwatch reading on today's data does not.

The objection: just index every column in the WHERE clause

This is the folk remedy, and it is popular because it works often enough to feel true. It is also how a write-heavy table ends up with eleven indexes and a mysterious insert cost.

Three costs are real. Every index must be maintained on every insert, update and delete touching its columns, so write amplification is proportional to index count. Every index consumes memory in the buffer pool, competing with the data pages you wanted cached. And the planner has to choose between them, which it does from statistics that can be stale.

There is a subtler cost specific to write contention, and Chapter 5 depends on it. MySQL's manual states that InnoDB always locks index records, and that if the searched column is not indexed or has a non-unique index, the statement locks the preceding gap. Indexing changes which rows a write locks, so index design is lock design.

The replacement rule is narrower and duller. Add an index for a query shape you can name, with its columns in the order that query needs, and delete indexes no plan uses. Then check the plan again.

Chapter summary

Eloquent issues a query whenever code touches a relationship it has not already loaded, and Laravel's own documentation puts the arithmetic beyond argument: twenty-five books plus an author access each is twenty-six queries, where with('author') is two. No individual query in that pattern is slow, so slow-query alarms stay quiet and the only reliable signal is query count per request, which is why Chapter 1 puts it first. Eager loading replaces N queries with one rather than removing them, and its own failure mode is over-fetching, which constrained eager loads through a closure and nested dot-notation loads exist to control. Where a view needs a number rather than rows, the aggregate helpers withCount, withSum, withMin, withMax, withAvg and withExists put a subquery on the parent select. Eager loading a relationship in order to count it is the version of this mistake a query log cannot show you. Make the failure loud outside production with Model::preventLazyLoading, which throws LazyLoadingViolationException. Treat automaticallyEagerLoadRelationships and withRelationshipAutoloading as a second step rather than a replacement, since autoloading removes the round trips and leaves the design unexamined. MySQL's manual gives the leftmost prefix rule that makes column order a design decision and describes covering indexes read without touching rows. PostgreSQL's manual gives the reading that settles arguments, where rows is what a node emitted rather than what it scanned, and requires a rollback wrapper for EXPLAIN ANALYZE on a write. And indexing every filtered column costs write amplification, buffer pool space and, because InnoDB locks index records, a change in what your writes lock.

Fixing the query count leaves the harder half of reading untouched, which is what happens when the result set is genuinely large. Chapter 4 is Reading at Scale, and it is about pagination, projections and not hydrating a million models.