Should you add an index for a slow query on a large table's unindexed columns?

A query against a large database table is slow, and the columns the query filters on have no index. Should you add an index? Explain how you would decide, what the index would look like, and how you would add it safely to a production table. This came up as a fundamentals question in a short hiring-manager screen for a senior backend role, and the interviewer recorded the answer without asking follow-ups, so give a complete answer. ```hint Measure before you index Decide what evidence would show that the missing index, and not something else, is why the query is slow. ``` ```hint Indexes are not free Consider what every write to this table pays once the index exists, and what building the index does to a busy production table. ``` ### Clarifying Questions - Which database engine and version is it? - What does the query look like: equality filters, ranges, sorting, joins, or pattern matching on text? - How selective are the filters: what fraction of the rows does a typical execution return? - How often does the query run, and how write-heavy is the table? - How large is the table, and is there a maintenance window? ### What a Strong Answer Covers - Confirming the cause with the query plan and current statistics before acting - Weighing selectivity, query frequency and write load to decide whether an index pays off - Designing the right index: column order in composite indexes, covering or partial indexes, and query shapes that cannot use an index - Building the index without blocking production writes, then verifying it is used and watching its cost - Alternatives for cases where an index is the wrong fix ### Follow-up Questions - The new index exists, but the planner still chooses a sequential scan. What could cause that? - The query filters on `status` and `created_at` and sorts by `created_at`. Which composite index would you create, and in what column order? - How would you find indexes that slow down writes but are never used?