What is the meaning of the N + 1 Problem?

Published 3 years ago by jhosep412

What is the meaning of the N + 1 Problem?

Mo7sin
Mo7sin
3 years ago (132,975 XP)
rikh
rikh
3 years ago (9,495 XP)

It's the problem caused when you need to make N + 1 SQL Queries, where N is the number of items.

For example, lets say you have a table of Blog posts, called posts. You can fetch all the posts in 1 SQL query (eg SELECT * FROM posts). Lets say this returns 100 rows of data. Now, for each post, you want to find the author of the post, so you might choose to loop over all the posts you got back from the first query and make a separate SQL query for each one (eg SELECT * FROM users WHERE user_id = ?). You'd end up making 100 queries to get the authors, plus 1 query to get the posts.

Obviously my example above could be trivially solved with a join, fetching both bits of data at the same time and making the overall query much more efficient.

When you are using an ORM like Eloquent, it can sometimes be easy to create code that produces the a result similar to the above example though, as it isn't always obvious what queries will be made when. If you are aware of the problem and understand how the ORM is working, it can makes it simpler to avoid the problem in the first place. Eloquent has a feature called Eager Loading (see link from @Mo7sin) that reduces this problem down to something more manageable.

jhosep412

@rikh thank you very much

JarekTkaczyk

@jhosep412 In short be careful with loops (speaking of Eloquent):

$cities = City::get(); // here's the N - arbitrary number of City models will be fetched

foreach ($cities as $city) {
    $city->country->name; // and here's the N queries - for each city a query fetching Country is executed
}

This happens because of the automatic (lazy) loading of the relations by Eloquent for your convenience.

To avoid this problem you need to do only this:

$cities = City::with('country') // eager loading Country models using only 1 query
            ->get();

// or
$cities = City::get();
$cities->load('country'); // lazy eager loading

The above code executes known number of queries - 2, no matter how many cities there are.

Please sign in or create an account to participate in this conversation.