MySQL — ORM N+1 Queries Turn a Page Load into a Query Storm

Detect N+1 query patterns from ORMs with the general query log, confirm with query counts per request, and eliminate them with eager loading and joins.

intermediatedebuggingmysqlormperformance

Symptoms

One API request executes hundreds or thousands of SELECT statements differing only by primary key value
The query log shows a parent listing query followed by per-row lookups in a tight loop
Page latency scales with row count: 25 items is fast, 100 items is seconds

Possible causes

Lazy-loading relations: accessing a relation field inside a loop triggers one query per iteration
Serializing entities touches every lazy relationship (author, tags, cover) for every row
A template or serializer layer iterates and dereferences relations the ORM resolves lazily
Pagination on the parent hides the cost until row counts grow

Diagnosis steps

Count queries per request

SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE'; SELECT argument, COUNT(*) FROM mysql.general_log WHERE command_type = 'Query' GROUP BY argument ORDER BY COUNT(*) DESC LIMIT 10;

Interpretation

A single template query repeated hundreds of times with different IDs is the N+1 signature, visible in the grouped counts.

Correlate with the code loop

grep -Rn "for .* in .*:.*\.author\|\.tags\|\.cover" src/ | head -10

Interpretation

Attribute access inside iteration that maps to a lazy relation is the trigger site to fix with eager loading.

Verify with eager loading

SELECT SUM(count) FROM (SELECT COUNT(*) AS count FROM mysql.general_log WHERE thread_id = <id>) t;

Interpretation

After the fix, per-request query count collapses to a constant regardless of row count — measure it, do not assume it.

Root cause

Lazy loading resolves each relationship with a separate query at first access. Iterating N parents and touching a lazy relation per iteration produces 1 + N queries, so total database round-trips scale with data volume even though every individual query is fast and indexed.

Fix

Eager-load relations in bulk with the ORM facility (select_related/prefetch_related, Include/ThenInclude, with(), first: :associations), replace hot loops with explicit joins or IN-batch loading, and enforce a per-request query-count budget in tests so regressions fail CI.

Risk note

prefetch_related-style double-query loading fetches all rows for the whole set; on unbounded collections paginate the parent first, then eager-load the page relations.

Verify the fix

Instrument per-request query counts; assert the endpoint executes a fixed number of queries independent of result size, across several page sizes.

Symptom walkthrough

Turn on the general query log as a table and group by statement:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

SELECT argument, COUNT(*) c FROM mysql.general_log
WHERE command_type = 'Query' AND argument LIKE 'SELECT%'
GROUP BY argument ORDER BY c DESC LIMIT 5;

-- SELECT * FROM users WHERE id = 42         1
-- SELECT * FROM users WHERE id = 43         1
-- SELECT * FROM users WHERE id = 44         1
-- ...                                       98 more
-- SELECT * FROM posts;                       1

One parent SELECT plus N identical-shaped child lookups is the N+1 fingerprint. The latency math explains the scaling symptom: each query is sub-millisecond on the server, but a network round-trip each, so 100 rows at 1ms RTT is 100ms of pure waiting before any real work.

Fix in practice

Name the relation at query time, in your ORM dialect:

# Django Posts.objects.select_related("author").prefetch_related("tags")

# TypeORM
repo.find({ relations: { author: true, tags: true } })

# Prisma
prisma.post.findMany({ include: { author: true, tags: true } })

# Rails
Post.includes(:author, :tags).all

For hand-rolled paths, batch explicitly:

SELECT * FROM users WHERE id IN (41, 42, 43, ...);  -- one round-trip

And lock the invariant with a budget test:

def test_list_endpoint_query_budget(django_assert_num_queries):
    with django_assert_num_queries(4):   # fails when N+1 returns
        client.get("/posts?page=2")

Verification

Instrument per-request query counts in the test suite and assert the endpoint executes a fixed number of queries independent of result size — run the assertion across page sizes of 1, 25, and 100 to prove the constant, and correlate with the grouped general log to see identical-shaped lookups collapse into one batch. In production, watch connection-pool acquisition time drop once the storm stops, since the round-trip serialization was the real cost. Distinguish the sibling pattern: cartesian-product eager loading (joining a multi-row relation onto a multi-row relation) inflates the result set instead of the query count — the fix there is splitting into two batched queries, the way prefetch does.