N+1 query in databases

Table of content

What is N+1 query
Analogy: The library
Technical example
Inefficient and Efficient query
Why you should care about the N+1 query problem

---

What is N+1 query

The N+1 query problem is a database anti-performance pattern, where an application executes one query to fetch a list of items (N items in 1 query) and then makes N additional individual queries to fetch related data for each item in that list(N number of queries). So, in total you are making 101 queries, 1 query to get the list and 100 query to get names of authors.

This results in N+1 queries for what should ideally be a single efficient or a few queries.

Analogy: The Library

Imagine you go to a library and you ask the librarian to give you a list 100 books of your favorite genre. The librarian gives you the list of 100 books(this is 1 query).

Now you have your list, but you don't know author's name of those 100 books. To know the name of those authors you again go to the librarian and ask him, Hey Librarian can you tell me please what is the name of the author of this book?. You ask this question for each books on that list. In the end guess what, you end up asking the librarian 100 more questions.

The gist is that you are making 1 query to get the list and then you are making N number of queries to get the authors name.

Technical Example

Assume that you own a e-commerce platform, where the sellers can list products and users can buy them. In your admin dashboard you want to display all the sellers and their products. There are two tables you have to query 1.seller, 2.product.

Let's see both inefficient and efficient ways of querying-

Inefficient:

Here you first get the sellers by querying the seller table:

const sellers = await db.select().from(seller);

And then for each seller you get the products

const allProducts = [];
for (const s of sellers) {
  const products = await db
    .select()
    .from(product)
    .where(eq(product.createdBy, s.id));
  allProducts.push(...products);
}

You are making a api call from your client and then the api do the following:

• First it query every seller from seller table
• Then it fetches products for each seller. So, if there were 100 seller you will make the first1 query and then 100 more queries in total 101 queries to get what you want.

Efficient:

The efficient version of the above is you tell the db hey please give me all the sellers and their products by joining the seller and product tables, this way you are going to the db only once and making a single query.

const sellersAndProducts = await db
  .select()
  .from(seller)
  .innerJoin(product, eq(seller.id, product.createdBy));

Here, instead of fetching products one by one for each seller, you made a single query by joining tables. This one is more simple compare to the first one and also good for database performance.

Why you should care about the N+1 query problem?

The answer always will be the performance.

• The N+1 query consumes lot lot more resources.
• Lot lot more queries.
• Each individual db query adds to the overall latency.

If the size of your db is small you might not face any performance issue but as your db grows, your user base grows the increased number of queries places a higher load on the database and on the server leading to performance bottleneck.

Comments

[Vlastimil Pospíchal]

SELECT seller.name, product.product_name
    FROM seller INNER JOIN product ON seller.id=product.createdBy;