Tell me about a time you had to debug a slow or incorrect SQL query.

Model answer

In my previous role I inherited a reporting query that joined five tables and took forty seconds to run each morning. I started by running EXPLAIN (or the equivalent execution plan in SQL Server) and immediately saw a full table scan on the orders table because the WHERE clause was filtering on a non-indexed column. I added a covering index for that column and the query dropped to under two seconds. I also noticed an implicit cast between a varchar column and an integer parameter, which was preventing index use, so I fixed the parameter type. I documented the change and added the query to a slow-query monitoring alert so we would catch regressions early.