Database Indexes: The Cheapest Performance Win Most Teams Skip

How to find missing indexes, when to add them, and why over-indexing hurts — a practical guide to the highest-leverage database fix.

Most slow applications aren't slow because of the language, the framework, or the server. They're slow because a query is reading a million rows to find twenty. The fix is usually a single line of SQL that takes seconds to apply — and yet missing indexes remain one of the most common problems we see in production systems.

An index is a sorted lookup structure the database maintains alongside your table. Without one, a query like WHERE customer_id = 123 forces a full table scan: the database reads every row and checks each one. With an index on customer_id, it jumps almost directly to the matching rows. On a table with a few thousand rows, the difference is invisible. On a few million, it's the difference between 2 milliseconds and 20 seconds.

How to find the queries that need one

Don't guess — measure. Every serious database tells you exactly where the pain is:

• PostgreSQL: enable pg_stat_statements and sort by total execution time. The top five queries usually account for most of the load.
• MySQL/MariaDB: turn on the slow query log with a threshold of one second, then lower it as you clean up.
• Any database: prefix a suspect query with EXPLAIN. If you see "full table scan" or "Seq Scan" on a large table in a hot path, you've found your candidate.

The pattern to look for: a query that filters or joins on a column, runs frequently, and touches a table that keeps growing. That's an index waiting to be created.

What to index

A few rules cover most real-world cases:

• Foreign keys. Any column used in a JOIN almost always deserves an index. Some databases don't create these automatically.
• Columns in frequent WHERE clauses. Especially high-selectivity ones — email or order_number, not is_active with two possible values.
• Composite indexes for multi-column filters. A query filtering on (tenant_id, created_at) wants one composite index, not two separate ones. Column order matters: put the equality filter first, the range filter last.
• Columns in ORDER BY on large result sets. Sorting a million rows in memory is expensive; reading them pre-sorted from an index is nearly free.

Why you shouldn't index everything

Indexes aren't free. Every INSERT, UPDATE, and DELETE has to update every index on the table, so a write-heavy table with ten indexes pays a real penalty on every write. Indexes also consume disk and memory — and an index the query planner never uses is pure overhead.

The discipline is the same as for code: add what's justified by evidence, and remove what's dead. PostgreSQL's pg_stat_user_indexes and MySQL's sys.schema_unused_indexes both show you indexes with zero reads. Audit them a few times a year and drop the ones nothing touches.

Make it part of your workflow

The teams that stay fast don't treat indexing as a one-time cleanup. They build it into how they ship:

• Review EXPLAIN output for new queries that touch large tables before merging.
• Add indexes in migrations, alongside the feature that needs them — not months later during an outage.
• On big production tables, create indexes concurrently (PostgreSQL's CREATE INDEX CONCURRENTLY) to avoid locking writes.

Performance work has a reputation for being deep and mysterious. Most of the time it isn't. Before you reach for caching layers, read replicas, or a rewrite, run EXPLAIN on your slowest query. The answer is usually one index away.