What is N+1 Problem

Classic example: List of blog posts. Each post has comments. Display page showing 10 posts with their comments:

Naive approach (N+1 queries): Query 1 SELECT * FROM posts LIMIT 10 gets 10 posts. Then Query 2-11 SELECT * FROM comments WHERE post_id = ? one query per post. Total: 11 queries for 10 posts. Scale to 100 posts = 101 queries. This is N+1 problem: 1 query fetch N items plus N queries fetch related data for each item.

Why insidious: With small N (5-10 items) each additional query takes only 2-5ms. Total overhead 10-50ms seems acceptable. But under load with concurrent users these delays compound into serious degradation. Database connection pool exhaustion common symptom.

Detection Methods

Query logging: Enable slow query log (set long_query_time=0 to log ALL queries temporarily). Look for repetitive similar queries with different parameter values in quick succession. ORM debug mode: Most ORMs (Hibernate/Django ORM/Sequelize/Eloquent) can log generated SQL. Enable during development to spot N+1 patterns. Profiling tools: Django Debug Toolbar, Chrome DevTools Network tab, specialized tools like QueryCount middleware.

Solution 1 Eager Loading JOIN

Fetch related data in same query using JOIN: SELECT posts.*, GROUP_CONCAT(comments.content) FROM posts LEFT JOIN comments ON posts.id=comments.post_id GROUP BY posts.id. Single query returns all data. Pros: Single round-trip. Cons: Data duplication, complex aggregation, does not scale well when related table has many rows per parent.

Solution 2 Batch Loading IN clause

Two-query approach: Query 1 gets 10 posts extract IDs [1,3,5,7,9,11,13,15,17,19]. Query 2 SELECT * FROM comments WHERE post_id IN (1,3,5,7,9,11,13,15,17,19) gets ALL comments in ONE query. Total: 2 queries regardless of N. Most commonly recommended approach.

ORM implementations: Django select_related()/prefetch_related(). Hibernate @BatchSize/EntityGraph. Sequelize findAll({include:[{model:Comment}]}). Eloquent with('comments'). Rails includes(:comments). Learn your ORM eager loading syntax - most important performance tool.

Solution 3 GraphQL/DataLoader Pattern

For API-backed frontends: DataLoader (originally Facebook) batches concurrent requests within single execution frame. Auto-deduplicates and batches. Particularly effective Node.js backend serving React/Vue frontend via GraphQL.

Real Impact Example

A 益阳 e-commerce client order listing page loaded in 4.2 seconds. Investigation revealed: 1 query for 20 orders plus 20 queries for order items plus 20 queries for product info plus 20 queries for customer addresses = 81 queries total. Refactored batch loading: 4 queries total (orders, items, products, addresses). Page load time: 0.38 seconds. That is 11x faster from changing ~10 lines of code.