N+1 query in SQL

What is an n+1 query problem ?

In reality this problem is quite simple to understand. It is when your code makes N queries to get the same information that could have been collected by executing the first query

To take a simple example, let’s imagine that you have 2 tables in base.

The first one users and the second one profiles being linked to the user thanks to the column user_id . When you iterate on the users to display their first names, you want to avoid making a query by users to retrieve their profiles. Like here:

SELECT * FROM users LIMIT 5;

SELECT * FROM profiles WHERE user_id = 1
SELECT * FROM profiles WHERE user_id = 2
SELECT * FROM profiles WHERE user_id = 3
SELECT * FROM profiles WHERE user_id = 4
SELECT * FROM profiles WHERE user_id = 5

Indeed when your database returns the users you already know which profiles you will display.

What is the solution ?

To fix this kind of problem it is necessary to understand already when it arises. So you need to know when you will need your tables. By planning what you need you will know in advance if you will post a list of something you will get back later. At this point you will be able to reduce the number of calls to your database by requesting only what you need.

SELECT users.* FROM users LIMIT 5
SELECT profiles.* FROM profiles WHERE profiles.user_id IN (%user_ids%)