It was a Tuesday. Traffic was normal. Then the database CPU hit 100% and stayed there. The site went down for twelve minutes. The culprit was a single SQL query that looked completely innocent. This is the story of how a simple comment count destroyed production — and what I learned about query optimization in the process.
The most dangerous queries are the ones that look harmless.
The application displayed a list of blog posts with comment counts. The naive implementation used a correlated subquery. It worked fine in development with fifty posts. In production, with two hundred thousand posts and four million comments, it became a death sentence.
-- The killer query (DO NOT USE)
SELECT
    p.id,
    p.title,
    p.created_at,
    (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
FROM posts p
WHERE p.published = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- This runs a full table scan on comments FOR EVERY POST.
-- With 200k posts, that is 200,000 subqueries.
The EXPLAIN output was horrifying. The query planner could not use an index because the correlated subquery forced a nested loop. Each iteration scanned the entire comments table. The query took four minutes to return twenty rows.
The fix was a lateral join with a covering index. But first, I needed the right index. A composite index on comments(post_id, id) allowed the database to satisfy the count with an index-only scan. Then I rewrote the query to use a derived table, letting the optimizer handle the aggregation efficiently.
-- The fix: derived table with proper indexing
CREATE INDEX idx_comments_post_id ON comments(post_id, id);

SELECT
    p.id,
    p.title,
    p.created_at,
    COALESCE(cc.count, 0) AS comment_count
FROM posts p
LEFT JOIN (
    SELECT post_id, COUNT(*) AS count
    FROM comments
    GROUP BY post_id
) cc ON cc.post_id = p.id
WHERE p.published = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- Execution time: 3 milliseconds.
The deeper lesson was about denormalization. For read-heavy workloads, storing the comment count directly on the posts table and updating it via triggers or application logic is often the right call. The database is not a black box where you throw SQL and hope for the best. It is a system you must understand.
Always EXPLAIN before you deploy. Always test with realistic data volumes. And never assume that a query that works in development will survive production. The database is the foundation of your application. When it falls, everything falls with it.