Tell me about a time you had to optimize a slow-performing query or database. What was your approach?
Quick Answer
A strong answer follows a clear, methodical narrative: how the problem was noticed (a specific symptom — a slow dashboard, a timeout, a customer complaint), how the actual bottleneck was diagnosed (using `EXPLAIN ANALYZE` or equivalent, not guessing), what the root cause turned out to be, what specific change was made, and how the fix was verified and its impact measured. Interviewers are listening for a diagnostic process, not just a final answer.
Detailed Answer
This is a behavioral question with a technical spine — the interviewer wants to hear a real, specific story with a clear diagnostic arc, not a generic list of optimization techniques recited in the abstract.
A strong structure for the answer (STAR-shaped, technically grounded)
Situation: What was the concrete symptom? "A customer-facing dashboard was timing out for accounts with a large order history" is much stronger than "a query was slow" — specificity signals a real memory, not a fabricated generic story.
Task: What was actually asked of you, and why did it matter? (e.g., "this was blocking a major customer's renewal, so it had real urgency.")
Action — this is where the technical depth should show:
- How did you first investigate? ("I pulled the slow query log / ran
EXPLAIN ANALYZEon the actual query with representative parameters, rather than guessing.") - What did the execution plan actually reveal? ("The plan showed a sequential scan on a 40-million row table, and the estimated vs. actual row counts were wildly different, which pointed at stale statistics as well as a genuinely missing index.")
- What was the root cause, specifically? (a missing index, a non-sargable predicate, an N+1 pattern, a join fanning out unexpectedly — see the relevant topics in this stack for the vocabulary to describe this precisely.)
- What did you actually change, and why that fix specifically (as opposed to some other plausible option)?
Result: What was the measured outcome? ("Query time dropped from 12 seconds to 80ms; we verified with EXPLAIN ANALYZE again post-fix, and confirmed via monitoring that the dashboard's p99 latency improved correspondingly over the following week.") Concrete before/after numbers are far more convincing than "it got a lot faster."
What separates a strong answer from a weak one
- Weak: "I added an index and it got faster." (No diagnostic process, no reasoning, sounds rehearsed/generic.)
- Strong: names the actual tool used to diagnose (
EXPLAIN ANALYZE, a slow query log, an APM trace), describes what the evidence actually showed, explains the reasoning connecting the evidence to the fix, and quantifies the result.
Common technical themes to be ready to discuss in depth if asked to go deeper
Missing/wrong indexes, non-sargable predicates, N+1 query patterns, join row-explosion, stale statistics, lock contention, or a genuine schema/data-model issue requiring more significant rework (denormalization, a materialized view, caching). Being able to go two or three "why" questions deep into any of these — not just name-drop them — is what actually distinguishes real hands-on experience from memorized talking points.
Preparing for this question
Have at least one real, specific story ready (even a modest one from a side project counts, if you don't have significant production experience) with actual before/after numbers if at all possible — a specific, well-reasoned example beats a more impressive-sounding but vague one every time.