Why an Index on an Unindexed Filter Column Speeds Up a Slow Query, and Its Costs
Company: Mintlify
Role: Software Engineer
Category: Software Engineering Fundamentals
Difficulty: medium
Interview Round: Technical Screen
A database query against a large table runs slowly. The query filters rows on a column that has no index. Explain what adding an index on that column does, why it improves the query's performance, and what trade-offs or downsides adding the index brings.
```hint Picture the work without the index
Think about how many rows the database has to read to apply the filter today, and what kind of structure would let it go straight to the matching rows.
```
```hint Every write pays
Consider what has to happen to that new structure each time a row is inserted, updated or deleted, and where the structure itself is stored.
```
### Clarifying Questions
- Which database and storage engine is it, for example PostgreSQL or MySQL with InnoDB?
- Is the filter an equality, a range, a prefix match, or a function applied to the column?
- Roughly what share of the table does the filter return: a handful of rows, or a large fraction?
- How write-heavy is the table, and does the query also sort, join, or read only a few columns?
- Can the index be built while the table keeps serving traffic, or is there a maintenance window?
### What a Strong Answer Covers
- What the database does without the index (a full table scan) and how to confirm it from the query plan
- How a B-tree index is organized and why a lookup or range scan touches far fewer pages
- When the index does not help: low selectivity, functions or casts on the column, leading wildcards, stale statistics
- The costs: slower inserts, updates and deletes, extra storage and cache pressure, and the impact of building it on a large live table
- Refinements such as composite, covering or partial indexes, and measuring the gain before and after
### Follow-up Questions
- The filter actually matches a large share of the table. Will the optimizer use the index, and should it?
- The same query also sorts by a second column and returns only the first page of results. What index would you create, and why does column order matter?
- How would you add the index to a very large table that serves live traffic without blocking writes?
- After the index is added, the query is still slow. What would you check next?
Overview: Asks why a slow query on a large table that filters on an unindexed column speeds up once an index is added, and what the database does differently. It also tests the costs of an index: slower writes, extra storage, build impact on a live table and the cases where the optimizer ignores it.