What's the N+1 problem and how do you avoid it?

Sep 3, 2022

Picture this, your page is taking too long to load, you use Laravel debugbar, Telescope, or another debugging tool to figure out what's happening.

Then you see it, the same query repeated multiple times, making your website slow. What gives?

That is the famous N+1 problem in action.

What's the "N+1 problem"

I think the best way to explain what is by using an example.

Let's say that we are building an e-commerce site. On this site, we have products that belong to brands and we need to display the brand that each product belongs to. We would have a brand relationship in our Product model like this:

public function brand()
{
    return $this->belongsTo(Brand::class);
}

Now, we need to display the brand name alongside the product information. To do that we create a controller method and send our products to our blade view:

public function index()
{
    return view('products', [             
        'products' => Products::query()->paginate()
                       // paginated of course 😉
    ]);
}

And render them in our blade template:

@foreach ($products as $product)
    //...
    <p>{{ $product->brand->name }}</p>
    //...
@endforeach

This looks fine. I have written code like this countless times in the past. However, there is a big issue here that might not be obvious.

While we loaded all of our products into memory using one SQL query in our controller, the brands are retrieved from the database one by one in our forloop.

If your relationship isn't loaded, Laravel will automatically execute an SQL query to retrieve it from the database when you try to access it. So every time we call $product->brand a new query to the database is made.

SELECT * FROM `brands` where id = ?;

And here is where our "N+1" problem appears.

It's called that because we execute 1 SQL query to fetch our products and then N SQL queries to fetch the brands, where N is the number of products we are listing. So in total, N+1 queries are executed.

While the example above is only one of the reasons an N+1 problem can appear, I find that it's the most common way it happens.

How to avoid it

It is so common, that Laravel offers a built-in way to fix this issue using. This fix is called eager loading.

Eager loading means that we want to retrieve all the related models before using them. Under the hood, Laravel will make only one SQL query to retrieve every related brand. So instead of making N+1 queries, we only make 2.

To use eager loading, we can call the with method in our query

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->with('brand')
                        ->paginate()
    ]);
}

This way Laravel fetches our list of products:

SELECT * FROM `products` limit 15;

Then grabs all the brand ids retrieved from that query and makes a second query:

SELECT * FROM `brands` where id in (?, ?, ?, ...);

So now we have access to all the brands for the products we are displaying in memory without needing to make additional queries.

If you are having problems with Eloquent queries or other Laravel-related topics don't hesitate to send me a message on Twitter or send me an email at [email protected], I'll be happy to help.