The N+1 query problem and how to fix it

The N+1 problem happens when code runs 1 query to fetch a list, then N more queries, one for each item, to fetch related data.

An example

Show 50 articles with their author names:

const articles = await db.query("SELECT * FROM articles LIMIT 50");   // 1 query
for (const a of articles) {
  a.author = await db.query("SELECT name FROM authors WHERE id = ?", a.author_id);  // 50 queries
}

That is 51 queries for one page. With 500 articles it is 501. Each query is fast, but the network round trips and overhead add up, and the database does a lot of repeated work.

Why it hides

ORMs make it easy. Accessing article.author in a loop looks like a property read, but lazily triggers a query each time. On a developer laptop with 5 rows it is invisible. In production with thousands of rows, the page crawls.

How to spot it

• Query logs: many identical queries differing only in an ID.
• Tools that count queries per request (Django Debug Toolbar, Rails Bullet, APM tracing).
• A page whose time grows with the number of rows.
• Assert the query count in tests for key endpoints.

Fixes

1. JOIN in one query

SELECT a.*, au.name AS author_name
FROM articles a JOIN authors au ON au.id = a.author_id
LIMIT 50;

2. Batch with IN: 2 queries total

SELECT * FROM articles LIMIT 50;
SELECT * FROM authors WHERE id IN (3, 7, 9, 12);   -- all needed ids at once

Then match them in code. Most ORMs do this for you:

• Django: select_related (join) and prefetch_related (IN query)
• Rails: includes
• Sequelize / Prisma / TypeORM: include / relations
• SQLAlchemy: joinedload, selectinload

3. DataLoader (GraphQL): collects all IDs requested during one tick and loads them together, with caching per request.

4. Cache rarely changing related data (like author names).

Watch the other direction

Joining many "one-to-many" tables in one query can multiply rows and return a huge result. Sometimes two or three well-chosen queries beat one giant join.

Rule of thumb

If a loop contains a database call, stop and ask whether it can be done before the loop for all items at once.

Takeaway: count queries per request, load related data in bulk (join or IN), and use your ORM's eager loading deliberately.

---

Backend & Database series, Part 2 (performance and operations): 2 of 6. Previous: EXPLAIN: how to read a query plan and find slow queries.