A query is slow in production. How do you diagnose and fix it?

Question

Accepted Answer

I diagnose before I change anything. First I confirm which query is actually slow, from the slow query log or from APM and metrics, rather than guessing, because the query I suspect is often not the real offender. Then the key step: I run EXPLAIN, or EXPLAIN ANALYZE for real timings, to see the execution plan. That tells me what the database is actually doing, a sequential full table scan where an index should be used, a large in-memory or disk sort, a poor join order, or scanning far more rows than the result needs. From the plan the fix usually becomes obvious: add or correct an index on the columns being filtered or joined, stop selecting columns I do not need (SELECT * pulls extra data and can prevent index-only scans), rewrite the query, or, very commonly, fix an N+1 pattern where the application is issuing one query per row instead of a single join or batched query. Then I verify by re-running EXPLAIN ANALYZE to confirm the plan actually improved, and I keep an eye on it, since an index that speeds reads also adds write overhead, so I make sure it is a net win. The discipline is measure, read the plan, targeted fix, verify, not reflexively add an index.

Answer

What does EXPLAIN tell you that guessing cannot?

The actual execution plan: whether it scans or uses an index, the join strategy and order, estimated vs actual rows, and where time goes. It points you at the real bottleneck instead of a guessed fix.

Answer

Why can SELECT * make a query slower?

It fetches columns you do not need (more I/O and network) and can prevent a covering/index-only scan, forcing the DB back to the table. Select only the columns required.

A query is slow in production. How do you diagnose and fix it?

A query is slow in production. How do you diagnose and fix it?

The follow-ups they ask next

What the interviewer is listening for

What sinks the answer

If you genuinely do not know

Keep going with databases

Knowing the answer is not the same as recalling it under pressure