Menu

The N+1 Problem

What is the N+1 problem?

The N+1 problem is common mistake, what developers should never-ever make.

So let’s assume We’ve got some kind of social application, where the users can follow eachother, which means everyone is following and followed by someone.

So our database should look like this:

  • users | user_id, name, email, password
  • user_relationships | user_id, followed_by

Obviously the users table contains all the user data, and our pivot table, the user_relationships table contains the relationship bewteen the users.

So We want to load the users, then let’s get the followers associated with the users. In PHP it should look something like this:

$users = get_users();
foreach($users as $user) {
  $followers = get_followers($user);
}
// ...

Let’s say our get_users() function runs an SQL query like this:

SELECT * FROM users

It is returns with a collection of users, which has N items. In our We’ve got the get_followers() function, which runs N times. Let’s assume it looks like this:

SELECT * FROM user_relationships WHERE user_id = $user->id

What does that mean? It means We have one query when We collected the users, and then extra N queries when We loaded the followers for each (N) users. That’s alltogether N + 1, to be more precise it’s 1 + N.

So it is clear now, this way of loading and handling data, is a very bad antipattern, with a bad affect to the performance.

 

Any solution?

Fortunately there is a solution for that. Let’s assume, the SQL in get_followers($users) function looks like this:

SELECT * FROM user_relationships WHERE user_id IN (1, 2, 3, 4, 5, ... N )

This means We’ve got all the followers associated with the previously loaded users, by two SQL queries, and no more. We successfully reducated the number of queries to two for every case, and increased the performance of out app.

 

Laravel and Eager Loading

Modern ORMs (Object-relational mapping) like Laravel’s Eloquent, have it’s own way to avoid this issue, right out of the box. For Laravel that means relationships and eager loading. I have to say it’s brilliant! More info and documentaions at the links.

 

Useful links:

Have something on your mind?