What is an N+1 query?

An N+1 query is a common performance issue where additional database queries are executed unnecessarily.

The N in N+1 refers to any additional queries needed to retrieve related data, such as comments and author records and the 1 represents the initial database query - for example, retrieving a blog post.

N+1 queries can impact an application's performance by causing a significant increase in the number of queries a request or action makes, with slower processing times as a result. Therefore, it is important you identify and resolve N+1 queries to prevent them from becoming performance bottlenecks.

Example N+1 query

A blog application has three database tables: articles, comments, and users, with each comment belonging to one article and user.

If our application is set up so that it retrieves articles first and then makes a separate query to fetch the comments and corresponding user for each article, we get an N+1 query.

1. The first query retrieves all articles:

SELECT * FROM Articles;

2. We query each article for its author:

SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;

3. Then query each article for its comments:

SELECT * FROM Comments WHERE article_id = 1;

4. Finally, we query each comment for its user:

SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 1;

Because N=3 for our query, in an example where each article has one comment, when we retrieve the first 20 articles:

SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;

We are executing 61 queries instead of 1:

SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
SELECT * FROM Comments WHERE article_id = 1;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 1;
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 2;
SELECT * FROM Comments WHERE article_id = 2;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 2;
...
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 20;
SELECT * FROM Comments WHERE article_id = 20;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 20;

If left unchecked, this example N+1 query will cause significant performance issues as the application grows. To avoid N+1 bottlenecks, you must be aware of N+1 queries in your codebase. You can implement solutions such as eager loading to resolve N+1 queries before they impact your application's performance.

AppSignal's N+1 query detection

AppSignal will apply an N+1 query tag when it detects an N+1 query, visible in the Event Timeline and Performance Issue overview alongside a warning box to notify you that a query may need to be optimized:

From N+1 to N+Gone

If you're ready to rid your applications of inefficient database queries with AppSignal, be sure to sign up for a free trial, and don't be afraid to reach out to support if you need any help getting started.