DEV Community

Sohail Khan
Sohail Khan

Posted on

Your List Endpoint Runs 101 Queries to Show 100 Orders

Your list endpoint runs 101 queries to show 100 orders.

A list I reviewed loaded 100 orders, then asked the database for each order's customer, one at a time.

1 query for the list.
100 more for the customers.

That is the N+1 problem. The page was slow, the database was busy, and the team's first idea was a bigger database.

What the code usually looks like

The endpoint fetches the orders, then loops over them to fetch each customer:

const orders = await db.query(
  `SELECT id, total, customer_id FROM orders
   ORDER BY created_at DESC LIMIT 100`
);

for (const order of orders) {
  const [customer] = await db.query(
    `SELECT name FROM customers WHERE id = ?`,
    [order.customer_id]
  );
  order.customerName = customer?.name;
}
Enter fullscreen mode Exit fullscreen mode

One query for the list, then one query per row inside the loop. With 100 orders that is 101 queries. With 1,000 orders it would be 1,001. The query count grows with the list, and that is the real problem.

Why it stays hidden for so long

The data is correct. Every order shows the right customer name, so no test fails and no user reports a bug. In development the list has ten rows, so eleven queries feel fast. The problem only shows up when real data arrives, and by then the slow page looks like a database problem, not a code problem.

The wrong fixes

A bigger database pays more for the same 101 queries. The queries are the problem, not the machine running them.

A cache hides the extra queries until the data changes, then the first request after the change pays the full cost again. It also adds a whole new thing to keep correct.

An index makes each of the 101 queries faster, without removing 100 of them. Faster queries that should not exist are still queries that should not exist.

The fix: ask for the data together

One query can return the orders with the customer name already in each row:

SELECT o.id, o.total, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC
LIMIT 100;
Enter fullscreen mode Exit fullscreen mode

One query, 100 rows, the customer name already in each row.

Two details in that query matter.

First, it is a LEFT JOIN, not a plain JOIN. If an order can exist without a customer, a plain JOIN would silently drop those orders from the list. LEFT JOIN keeps every order and leaves the customer name empty where there is no customer.

Second, it has an ORDER BY. Without it, LIMIT 100 returns whichever 100 rows the database happens to find first, and that set can change between requests, which breaks paging. With ORDER BY, the database sorts first and then takes the newest 100 orders.

When a join is the wrong fix

The join above works cleanly because each order has exactly one customer. Joining a many-to-one relation keeps one row per order.

A one-to-many relation behaves differently. Join orders to order_items, and an order with five items becomes five rows. LIMIT 100 now counts rows, not orders, so you might get 30 orders, and the last one can be cut off halfway through its items.

For that case, use two queries instead. Page the orders first, then fetch all their items in one go:

SELECT id, total FROM orders
ORDER BY created_at DESC
LIMIT 100;

SELECT order_id, sku, qty FROM order_items
WHERE order_id IN (/* the 100 order ids */);
Enter fullscreen mode Exit fullscreen mode

Then group the items by order_id in your code. That is still two queries for any list size, which is the whole point.

If you use an ORM

Use its eager loading, like include in Prisma or Sequelize. It may run 2 queries instead of 1, and that is fine. What matters is that the query count stays the same as the list grows:

const orders = await prisma.order.findMany({
  take: 100,
  orderBy: { createdAt: 'desc' },
  include: { customer: true },
});
Enter fullscreen mode Exit fullscreen mode

The ORM fetches the orders, then fetches the customers for those orders in one more query. Two queries for any list size beats one query per row. This two-query approach is also why ORMs handle one-to-many relations like order items without the row multiplication problem.

How to spot it in your own code

Count the queries per request, not the time per query. Turn on query logging in development, open a list page, and look at the count. Then change the page size from 10 rows to 100 rows. If the query count jumps with the row count, you have an N+1 hiding in that endpoint.

Fix the shape of the query first. Speed comes after, and only where it is still needed.


How many queries does your list endpoint run today? Drop the number in the comments.


I'm Sohail Khan, a Senior Full Stack Developer building AI agents and automation. I write about backend engineering, AI integration and practical system design.

This article was written with AI assistance and reviewed by me.

Top comments (0)