Given a SQL table with millions of rows and slow queries filtering on multiple columns, describe the approaches you would take to analyze and optimize query performance, including indexing strategy, query rewriting, and any trade-offs involved.

Question

VirtualInterview.AI · Accepted Answer

At my last role I inherited a report that ran in 90s over a 30M-row orders table and blocked nightly jobs. I started by capturing slow queries and EXPLAIN plans, then identified that many filters were on (customer_id, status, created_at). I introduced a composite index for (customer_id, status, created_at) for our most common path and separate single-column indexes for ad-hoc queries, which dropped median runtime from 90s to 1.8s for the report. I also rewrote queries to avoid SELECT * and used covering indexes and pagination for UI endpoints. For heavy analytical queries I recommended moving to a read replica and creating a periodic summarized table, accepting some staleness (1–5 min) to avoid locking the primary. Trade-offs were index write penalty (~6% slower INSERTs) vs read latency improvement.

Sample Answer

Keywords