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 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;
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 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!
Do you need help, have a feature request or just need someone to rubber duck with? Get in touch with one of our engineers.
AppSignal is located in beautiful the Netherlands. We love stroopwafels. If you do too, let us know. We might send you some!