The N+1 in the ORM you cannot see from the template

The orders listing had been through an N+1 audit six months earlier and every relation was eager-loaded. It was still slow, and the reason was the fix: eager loading lines to display the date of the most recent one hydrates every line of every order, which is forty thousand model objects to render twenty dates.

The symptom

$ curl -s -o /dev/null -w '%{time_total}n' https://shop.internal/orders
1.884

# and the middleware from the last audit, which reports it as fine
$ grep 'request.query_count' /var/log/app/app.json | jq -r .context.count | tail -1
4

$ php artisan tinker
>>> $before = memory_get_usage(true);
>>> $orders = Order::with('lines')->paginate(20);
>>> (memory_get_usage(true) - $before) / 1048576;
=> 84.0

Four queries, which is what the audit was looking for, and eighty-four megabytes to render a page of twenty rows. The query count metric says the page is healthy because query count was the thing being measured, and the actual cost moved somewhere nobody was looking.

Why it happens

// the controller
$orders = Order::with('lines')->paginate(20);

// the template, which is the only place the requirement is visible
@foreach ($orders as $order)
    {{ $order->lines->count() }} items
    {{ $order->lines->max('shipped_at') }}
    {{ $order->lines->sum('total') }}
@endforeach

// three aggregates. zero of them need the rows.

Eager loading is all-or-nothing per relation: with('lines') fetches every column of every line, because the ORM cannot know that the template only wants three numbers. On orders averaging thirty lines each, that is six hundred hydrated models per page to produce sixty values.

This is the second failure mode of the N+1 problem and it is considerably less discussed than the first, because the obvious metric — query count — improves while the page gets worse. A page that was doing 340 cheap queries and is now doing four expensive ones has traded round trips for memory, and whether that is an improvement depends entirely on the shape of the data.

The fix

withCount, for the common case

// a subselect in the existing query. no hydration at all.
$orders = Order::withCount('lines')->paginate(20);

// {{ $order->lines_count }}   — an integer, on the model

// and with a constraint, which is the version people miss
$orders = Order::withCount([
    'lines',
    'lines as unshipped_count' => function ($q) {
        $q->whereNull('shipped_at');
    },
])->paginate(20);

The aliased form is what makes this cover more than one counter per relation, and it produces one subselect per alias in the same query. Two counts and no hydration is a page that renders in constant memory regardless of how many lines an order has.

addSelect with a subquery, for anything that is not a count

$orders = Order::query()
    ->withCount('lines')
    ->addSelect(['last_shipped_at' => OrderLine::select('shipped_at')
        ->whereColumn('order_id', 'orders.id')
        ->latest('shipped_at')
        ->limit(1)
    ])
    ->addSelect(['line_total' => OrderLine::selectRaw('SUM(total)')
        ->whereColumn('order_id', 'orders.id')
    ])
    ->paginate(20);

The result is a plain attribute on the model, so the template is unchanged and nothing is hydrated. This is the addition in 6.0 that has the most immediate effect on a real application, because “the most recent one of a relation” is a shape that appears on almost every listing page.

The same subquery can be used in orderBy, which is the case that previously forced a join and a GROUP BY — and a join to get one column from a one-to-many relation is how a listing page starts returning duplicate rows.

$orders = Order::orderBy(
    OrderLine::select('shipped_at')
        ->whereColumn('order_id', 'orders.id')
        ->latest('shipped_at')
        ->limit(1),
    'desc'
)->paginate(20);

The index the subquery needs, which is not the join index

-- the index that served the eager load
KEY idx_order (order_id)

-- what the correlated subquery does with it
EXPLAIN SELECT ... ;
| id | select_type        | table | type | key       | rows  |
|  2 | DEPENDENT SUBQUERY | lines | ref  | idx_order |    31 |
-- 31 rows examined per order, then sorted. per row of the outer query.

-- the index it actually wants
ALTER TABLE order_lines ADD INDEX idx_recent (order_id, shipped_at DESC);

| id | select_type        | table | type | key        | rows |
|  2 | DEPENDENT SUBQUERY | lines | ref  | idx_recent |    1 |

This is the part that turns the technique from a rewrite into an improvement. A correlated subquery runs once per row of the outer query, so an index that returns thirty-one rows to sort is thirty-one times twenty rows examined and sorted per page — which is better than hydrating them and is not good.

The descending index is genuinely useful here and is one of the few places it is: the subquery is an equality on order_id followed by a descending sort on shipped_at, which is exactly the shape MySQL 8.0’s descending indexes exist for. Before 8.0 the same index without DESC still helps, because a single index can be read backwards.

Where this is worse than eager loading

// the template needs the actual lines. a subquery cannot help.
@foreach ($order->lines as $line)
    {{ $line->sku }} × {{ $line->quantity }}
@endforeach

// four subqueries reading four columns from the same relation:
// four correlated lookups where one eager load would do.
->addSelect(['a' => ...])->addSelect(['b' => ...])
->addSelect(['c' => ...])->addSelect(['d' => ...])

// the rule: aggregates and single values → subquery.
//           the rows themselves        → eager load.

Four subqueries against one relation is four correlated lookups per row, and at that point the eager load is doing less work. The boundary is around two or three, and it is worth measuring rather than guessing because it depends on the relation’s size.

The other case where the subquery loses is a relation with very few rows — an order with two lines costs almost nothing to hydrate, and the subquery machinery is not free. This technique is for wide relations read narrowly, which is a specific shape rather than a general improvement.

Verifying it worked

$ curl -s -o /dev/null -w '%{time_total}n' https://shop.internal/orders
0.148                       # was 1.884

>>> $before = memory_get_usage(true);
>>> $orders = Order::withCount('lines')->addSelect([...])->paginate(20);
>>> (memory_get_usage(true) - $before) / 1048576;
=> 2.0                      # was 84.0

$ mysql -e 'SHOW SESSION STATUS LIKE "Handler_read%"'
Handler_read_next   41       # was 604,112

$ vendor/bin/phpunit --filter OrderIndex
OK (4 tests, 9 assertions)

Handler_read_next is the counter worth using for this rather than query count — it reports rows actually read by the storage engine, which is the number the eager load was inflating and the query count was hiding. Six hundred thousand to forty-one is the whole story in one figure.

The test asserting on memory rather than on query count is the change to the audit: assertLessThan(8 * 1048576, memory_get_peak_usage(true)) catches this regression where a query-count assertion cannot. Both are worth having, because they catch different mistakes.

What this costs

SQL in a place people do not look for it. A controller with three addSelect blocks containing correlated subqueries is harder to read than with('lines'), and somebody new to the codebase will not know why it is written that way. A comment naming the index it depends on is the minimum, because the next person to add an index will not know this query exists.

The deeper cost is a coupling between the query and the index that nothing enforces. Dropping idx_recent during an index cleanup turns a 148-millisecond page into a several-second one with no error and no obvious cause, and the index looks unused to performance_schema if the page is not visited during the sampling window. Naming indexes after the query they serve, and keeping a comment in both directions, is the best available defence and it is weaker than a foreign key.