Taming N+1 Queries in PHP

Last month our API's /posts endpoint went from 80ms to nearly 900ms after we added a comments_count field to every item in the list. The culprit was a classic N+1 query: for each row returned by the initial SELECT * FROM posts, we ran a second query to count its comments.

The fix was straightforward once we saw it. Instead of looping and querying per row, we pulled the counts in a single GROUP BY query and merged them in PHP with array_column(). Response times dropped back under 90ms, and the database load graph flattened out almost immediately.

The lesson that stuck with me: any time you see a query running inside a loop, stop and ask whether it can be batched. A JOIN or a second bulk query is almost always cheaper than a thousand tiny ones, even if the code looks a little less tidy.