MySQL Subqueries
debt(d9/e3/b3/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints field explicitly states automated detection is 'no', with only a code_pattern hint. No static analysis tool is listed. Correlated subqueries and IN vs EXISTS misuse execute silently and only reveal performance problems under production-level data volumes — invisible until users experience slow queries.
Closest to 'simple parameterised fix' (e3). The quick_fix states 'Replace correlated subqueries with a JOIN — use EXISTS instead of IN for large subquery result sets.' This is a targeted rewrite of the affected query or small set of queries — more than a one-liner because query logic must be restructured, but contained within one component or query file.
Closest to 'localised tax' (b3). The applies_to covers web and cli contexts broadly, but the debt is query-local — a bad subquery pattern imposes a performance tax only on the queries and code paths that call it. The rest of the codebase is generally unaffected unless the pattern is pervasive.
Closest to 'serious trap' (t7). The misconception is explicit: developers believe IN (SELECT ...) and EXISTS (SELECT ...) are always equivalent, but EXISTS short-circuits while IN evaluates everything. This contradicts the intuition that two logically equivalent constructs perform equivalently, and the correlated subquery N+1 behaviour also surprises developers familiar with ORM-level N+1 but not SQL-level equivalents.
Also Known As
TL;DR
Explanation
Subqueries can appear in SELECT (scalar), FROM (derived table), WHERE, or HAVING. Correlated subqueries reference the outer query and re-execute for each outer row — potentially catastrophically slow on large tables. Non-correlated subqueries execute once. IN (subquery) evaluates the full subquery; EXISTS short-circuits on first match — EXISTS is usually faster for large sets. MySQL 8+ improves subquery decorrelation automatically in some cases.
Common Misconception
Why It Matters
Common Mistakes
- Using a correlated subquery in SELECT to fetch a related value — re-executes for every row.
- Using IN with a large subquery instead of a JOIN or EXISTS.
- Nesting subqueries more than two levels deep — readability and optimisability both suffer.
Avoid When
- Avoid correlated subqueries in SELECT — replace with a JOIN.
- Avoid deep nesting — flatten with CTEs (WITH clause) in MySQL 8 for readability.
When To Use
- Use EXISTS for semi-join checks — checking whether any related row exists.
- Use derived tables in FROM for complex aggregations that need to be joined back.
Code Examples
-- Correlated: re-runs for every user row → O(n²)
SELECT email,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;-- EXISTS short-circuits on first match
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');
-- Derived table in FROM (executes once)
SELECT u.email, stats.total
FROM users u
JOIN (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) stats
ON stats.user_id = u.id;