Skip to content
Quantoros

What is the N+1 query problem?

SQLLevel: IntermediateDatabase Design

What is the N+1 query problem?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

N+1 is when you run 1 query to get N rows, then run N more queries to fetch related data for each row. Total: N+1 queries.

Classic example: fetch 100 orders (1 query), then for each order fetch the customer details (100 more queries). This is one of the most common performance problems in applications using ORMs.

-- N+1 approach (in application code loop):
SELECT * FROM orders LIMIT 100;
-- Then for each order: SELECT * FROM customers WHERE id = ?;  (100 times)

-- Fix: JOIN everything up front
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
LIMIT 100;

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions