Subqueries in SELECT Clauses Are Performance Killers

Subqueries in SELECT Clauses Are Performance Killers

A query that took 40+ seconds to run had this pattern: each row triggered a separate subquery execution—terrible for large tables.

The problematic query looked like this:

SELECT 
    p.id,
    p.title,
    (SELECT COUNT(*) 
     FROM tasks t 
     WHERE t.project_id = p.id 
       AND t.status = 'active') as task_count
FROM projects p
WHERE p.created_at > '2024-01-01'

The issue: for every row in the projects table, MySQL executes the subquery individually. With 10,000 projects, that’s 10,000 separate COUNT queries. Ouch.

The fix is to refactor to a JOIN with aggregation:

SELECT 
    p.id,
    p.title,
    COUNT(t.id) as task_count
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id 
    AND t.status = 'active'
WHERE p.created_at > '2024-01-01'
GROUP BY p.id, p.title

Same result, but execution time dropped from 40+ seconds to under 200ms. The database can optimize JOINs far better than correlated subqueries.

How to spot these: Use EXPLAIN to catch correlated subqueries before they hit production. Look for “DEPENDENT SUBQUERY” in the type column—that’s your red flag. Here’s what you’ll see:

EXPLAIN SELECT ...

+----+--------------------+-------+-------------------+
| id | select_type        | table | type              |
+----+--------------------+-------+-------------------+
|  1 | PRIMARY            | p     | ALL               |
|  2 | DEPENDENT SUBQUERY | t     | ref               |
+----+--------------------+-------+-------------------+

That “DEPENDENT SUBQUERY” line means the inner query depends on the outer query’s values and runs once per row. Refactor it to a JOIN with GROUP BY, and watch your query times drop dramatically.