Solved

Laravel list page runs 101 queries for 100 posts

Asked by Oskar Brandt · 557 views · 3 replies

Oskar Brandt5 messages

Our blog index got slow, so I turned on the query log. One query for the posts, then one more for every single post. This is the controller and the loop in the view:

$posts = Post::latest()->take(100)->get();

// in the Blade view
@foreach ($posts as $post)
    {{ $post->title }} by {{ $post->author->name }}
    ({{ $post->comments->count() }} comments)
@endforeach

Where are the extra queries coming from? I only call get() once.

3 replies

Priya NairSolution

Classic N+1. $post->author is a relationship, and the first time you touch it on each post Eloquent lazy-loads it with its own query. One query for the list, plus one per post.

Load the relationship up front instead, so Eloquent fetches all the authors in a single WHERE id IN (...) query:

$posts = Post::with('author')
    ->withCount('comments')
    ->latest()
    ->take(100)
    ->get();

And in the view use {{ $post->comments_count }}. $post->comments->count() loads every comment row just to count them; withCount asks the database for the number. That takes you from 101 queries (or 201 with the comments) down to three.

To stop it coming back, add this to AppServiceProvider::boot():

use Illuminate\Database\Eloquent\Model;

Model::preventLazyLoading(! $this->app->isProduction());

Locally, any lazy load now throws an exception, so the next N+1 fails loudly in development instead of quietly in production.

Link
Oskar Brandt

Three queries. I did not know withCount existed. Turned on preventLazyLoading as well and it immediately caught two more pages doing the same thing.

Link
Hannah Lindqvist

One more for later: if you only show the author name, with('author:id,name') selects only those columns. Keep id in the list, though. Eloquent needs it to match authors back to posts, and without it every author comes back empty.

Link