When to Reach for a Database Index (and When It Won't Save You)

A query that used to return in milliseconds starts taking seconds. The table grew, the data grew with it, and now every request scanning that table is doing real work to find a handful of rows. The instinct, almost always, is to add an index and move on.

Sometimes that's exactly right. Sometimes it makes things worse in a way that doesn't show up until later, when writes to that table start slowing down and nobody connects it back to the index added three sprints ago.

An index is a separate data structure, usually a B-tree, that the database maintains alongside your table so it can find rows matching a condition without scanning every row. Instead of checking all 500,000 orders to find the ones for a given customer, the database walks the index straight to the matching rows.

That speed isn't free. Every time a row is inserted, updated, or deleted, every index on that table has to be updated too. A table with five indexes doesn't just store the row once, it maintains five extra structures that all have to stay correct on every write. Reads get faster. Writes get slower. That trade-off is the entire story, and most advice about indexing skips straight past it.

Indexing a column that's rarely used in WHERE clauses or JOIN conditions adds write overhead for a read speedup nobody actually benefits from. This is the most common mistake, indexing a column because it seems important, not because queries actually filter on it.

Indexing a column with low cardinality, a boolean, or a status field with only three possible values, often doesn't help either. The database ends up scanning a large fraction of the index anyway, since so many rows share the same value, and sometimes chooses to ignore the index entirely and just scan the table, making the index pure overhead with no upside.

A table with heavy write traffic and infrequent reads is the worst case for over-indexing. A logging table that gets thousands of inserts a second and is only queried once a month for an audit doesn't need five indexes protecting a query pattern that barely happens.

Index columns that actually appear in WHERE, JOIN, and ORDER BY clauses on your most frequent queries, not columns that feel important. Use your database's query planner (EXPLAIN in Postgres and MySQL) to confirm an index is actually being used before assuming it's helping, a query planner will often surprise you by ignoring an index you expected it to use.

When in doubt, measure before and after. Run the slow query, note the timing, add the index, run it again. If the read speedup doesn't meaningfully outweigh the write cost for that table's actual traffic pattern, the index isn't earning its place. An index is a targeted fix for a measured problem, not a default you sprinkle across every column that looks like it might get queried someday.