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