The N+1 query problem: why a page that asks the database 501 questions is slow

You build a page that lists authors and the books each one wrote. It works. With 3 authors it feels instant. Then the site grows to 500 authors and the page crawls, yet the code did not change. What happened?

The usual cause has a name: the N+1 query problem. It is one of the most common reasons a database-backed page is slow, and once you can see it, you will spot it everywhere.

Two tables, one question

A database keeps data in tables, and you ask it questions with queries written in SQL. Our example has two tables: authors (an id and a name) and books (a title, plus an author_id that says which author wrote it). The question is: for every author, what are their titles?

The natural way to write it

Most beginners (and many experienced developers) write this: first get the list of authors, then loop over it and ask for each author's books.

for aid, name in q("SELECT id, name FROM authors"):
    titles = [t for (t,) in q("SELECT title FROM books WHERE author_id = ?", (aid,))]
    print(name, titles)

Here q is a small helper that runs one query and counts it. We ran this with Python's built-in sqlite3 module on 3 authors and 5 books. The output:

Ada ['Notes', 'Engines']
Grace ['Compilers']
Linus ['Kernels', 'Git']
queries: 4

Four queries: 1 to fetch the authors, plus 1 for each of the 3 authors. That is "N+1", where N is the number of authors. With 500 authors it is 501 queries to show one page.

Why more queries means slower

Each query is a round trip: your program sends text to the database, the database works, and the answer comes back. In a real app the database usually runs on another machine, so every round trip pays a fixed travel cost (the network latency) no matter how little data moves. The database itself may answer in a fraction of a millisecond; the waiting is mostly travel. Many small trips cost far more than one trip with the same data.

Try it

The fix: ask once

A JOIN glues rows from two tables together using the matching column, so the database does the pairing and returns everything in one answer.

SELECT a.name, b.title
FROM authors a
JOIN books b ON b.author_id = a.id
ORDER BY a.id, b.id

Run through the same helper, it printed:

[('Ada', 'Notes'), ('Ada', 'Engines'), ('Grace', 'Compilers'), ('Linus', 'Kernels'), ('Linus', 'Git')]
queries: 1

One query, same information. The rows come back flat (Ada appears twice), so your code groups them by name if it wants one line per author. If an author might have no books, use LEFT JOIN to keep them in the result. Another option is two queries total: fetch the authors, then fetch all their books at once with WHERE author_id IN (...). Many frameworks call this "eager loading" or "preloading".

How to spot it in your own code

Look for a query inside a loop. That includes hidden loops: an ORM (a library that turns database rows into objects) can run a query silently when you touch something like author.books. Symptoms are a page that slows as data grows and a query log full of near-identical lines. Most databases and frameworks can log every query; turning that on and counting is the quickest check.

What about speed inside the database?

One JOIN is not magic: the database still has to find matching rows. If books.author_id has no index (a sorted lookup structure, like a book's index), the database may scan the whole books table to match each author. Adding an index on the column you join or filter by usually makes the single query fast. So the two fixes work together: send fewer queries, and make each query cheap to answer.

A rule of thumb

Count queries per page, not just the time of each one. If the count grows with the amount of data on screen, something is looping. If it stays the same at 5 rows and at 500 rows, you are in good shape.

Is the loop ever fine?

Yes. With 3 rows nobody will notice, and a loop can be clearer to read. The trouble is that data grows quietly. A good habit: when you write a query inside a loop, ask how big the loop can get.