← Learning center

What is an N+1 query?

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.

An example of a relational database schema

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:
sql
SELECT * FROM Articles;
sql
SELECT * FROM Articles;
  1. We query each article for its author:
sql
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
sql
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
  1. Then query each article for its comments:
sql
SELECT * FROM Comments WHERE article_id = 1;
sql
SELECT * FROM Comments WHERE article_id = 1;
  1. Finally, we query each comment for its user:
sql
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 1;
sql
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:

sql
SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;
sql
SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;

We are executing 61 queries instead of 1:

sql
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;
sql
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:

Screenshot of N+1 query detection warning in AppSignal

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.

Start your free trial

Don’t let the bad bugs bite. Try AppSignal for free.

AppSignal offers a 30-day free trial, no credit card is required. All features are available in all plans. Start monitoring your application in just a few clicks!