Why Nested Subqueries Drag Your SQL Queries Down – A Real‑World Debugging Story

Background

During a production rollout a page failed to fetch data; the root cause was an API timeout caused by an SQL query that ran for more than 20 seconds.

Complex SQL Statement

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, then a nested subquery to find group IDs shared by multiple users.

Where Does the Time Go?

Assuming a_table holds 200 k rows and b_table 2 billion rows, the author asks whether the join or the subquery is the main time consumer.

Problem Diagnosis

Without deep knowledge of SQL internals, the author used analogies and simple tests to locate the slowdown.

Initial Assumption

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 suspected the nested subquery was the major cost.

Further Verification

Running the subquery alone in the database proved it was fast, eliminating it as the culprit. The remaining 20‑second delay was therefore attributed to the nested structure.

Quick Validation Steps

Execute the subquery to obtain a list of IDs (e.g., 1,2,3,…,999).

Manually replace the subquery in the original SQL with this ID list.

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

Result: the issue is confirmed and ready for a fix before release.

Solution

Since the problem is identified, a straightforward code change to avoid the costly nested subquery resolves the performance issue.

Additional Observation

In practice, a large table left‑joined to a small table can be slow (≈1 s), while a small table left‑joined to a large table is fast (≈100 ms). This counter‑intuitive behavior highlights the importance of understanding join direction and data distribution.

Summary

Nested subqueries can severely degrade performance, especially when combined with large‑table joins. Misconceptions that the database will automatically optimize such patterns can lead to hidden bottlenecks. Careful testing, simplifying queries, and awareness of table‑size effects are essential for efficient SQL design.