Katkat
Back

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

MintlifyBackend Engineer · Onsite
MediumBackend EngineerOnsiteSoftware Engineering Fundamentals

Last updated

A query on a large database table is slow and the columns it filters on have no index: should you add one? It tests reading query plans, weighing selectivity and query frequency against write cost, designing composite or partial indexes, and building an index safely on a busy production table.

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.

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?

Hints

Hint 1

Measure before you index: Decide what evidence would show that the missing index, and not something else, is why the query is slow.

Hint 2

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.

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?