Why Nested Subqueries Slow Down Your SQL Queries and How to Diagnose Them

Background

During a production rollout a page failed to retrieve data because the API timed out; the root cause was an SQL query that ran for more than 20 seconds. The author shares this experience as a reminder of common performance pitfalls.

Structure of a Complex SQL Query

The problematic query can be represented as:

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE a.id IN (
    SELECT DISTINCT id FROM a_table
    WHERE user_id IN (100,102,103) GROUP BY user_id HAVING count(id) > 3
)

Join vs. Subquery

The query first performs a left join between a_table and b_table, then executes a nested subquery to find group IDs shared by multiple users. The author asks readers to consider whether the join or the subquery is the main bottleneck.

Problem Diagnosis

Without deep knowledge of SQL internals, the author uses simple tests to locate the issue. By simplifying the query to a single user ID:

SELECT * FROM a_table AS a
LEFT JOIN b_table AS b ON a.id=b.id
WHERE user_id IN (100)

the author observes that the nested subquery consumes most of the time.

Further Verification

To confirm, the subquery is executed alone in the database and runs quickly, ruling out the subquery itself as the slow part. The remaining delay is attributed to the nesting of the subquery within the join.

Three quick checks are performed:

Execute the subquery separately and collect the resulting IDs (e.g., 1,2,3,…,999).

Replace the subquery in the original SQL with this explicit ID list.

Run the modified query, which completes in about 150 ms.

Having verified the cause, the author proceeds to fix the issue for the release.

Solution

The fix involves rewriting the query to avoid the costly nested subquery, using a more efficient approach (details omitted for brevity). The key point is that once the problem is identified, the implementation is straightforward.

Additional Observation

An interesting performance pattern is noted: joining a large table to a small table (large LEFT JOIN small) is slow, while joining a small table to a large table (small LEFT JOIN large) is fast. This aligns with the intuition that the driving (left) side should be the smaller dataset.

Summary

Nested subqueries can dramatically degrade query performance, especially when combined with large‑table joins. Simple isolation tests—running parts of the query individually—help pinpoint the bottleneck. Understanding join direction and avoiding unnecessary nesting are essential for writing efficient SQL.