SQL Query Optimization & Indexing

Interviewers ask about query optimization and indexing because it is the fastest way to tell apart a candidate who has written SQL from a candidate who has kept a production database alive. Almost everyone can write a SELECT with a WHERE clause. Far fewer can look at a query that takes eleven seconds, read the plan the database is actually executing, name the specific reason it's slow, and propose a fix that doesn't just move the cost somewhere else. That gap is exactly what this topic is designed to expose.

The weak answer to "this query is slow, what do you do?" is "add an index." It is not wrong often enough to be useless, which is precisely why it is a trap — it works often enough in a toy example that candidates lean on it as a reflex, and interviewers know it. The strong answer starts one step earlier: run EXPLAIN ANALYZE, read what the database actually did, and diagnose before prescribing. An index only helps if the planner will choose to use it, and the planner's choice depends on selectivity, column order, data types, and statistics — none of which "just add an index" accounts for. The strong candidate can also say when not to add an index, because every index is a standing cost on every write, forever, whether or not it ever gets read.

This subject builds that diagnostic loop end to end: reading a plan, understanding what each index type is actually good at, writing predicates the planner can use (sargability), understanding why the planner sometimes gets it wrong (stale statistics, bad cardinality estimates), recognizing the query shapes that cause pain at scale, and rewriting them. The execution-order and set-theory foundations this topic assumes are covered in sql-mental-model-and-query-execution; window functions and CTEs that show up in some of the rewriting examples below are covered in advanced-sql-window-functions-and-ctes. If you take one thing from this subject into an interview, make it this: every optimization claim should be backed by a plan, not a guess.