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.
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.
SELECT * FROM Articles;
SELECT * FROM Articles;
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
SELECT * FROM Comments WHERE article_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 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;
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;
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 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:
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.
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!