Database Indexing Mistakes That Are Quietly Killing Your App's Performance

Indexing is one of those topics every developer has heard of, most have used, and surprisingly few have actually reasoned through carefully. It's easy to add an index and move on — it's much harder to know whether that index is actually helping, or just adding write overhead while your slow query is still slow for a completely different reason.

Here are the indexing mistakes that show up again and again in real codebases, and what to do instead.

Mistake 1: Indexing Every Column "Just In Case"

It feels safe to add an index to any column that shows up in a WHERE clause somewhere. The problem is that every index has a cost on every write — inserts, updates, and deletes all have to update every index on that table, not just the one relevant to your read query. A table with ten indexes can turn a simple insert into ten additional write operations behind the scenes.

The better approach: index based on actual query patterns, not hypothetical ones. Use your database's query planner (EXPLAIN in Postgres and MySQL) to see what's actually being scanned, and index those specific access patterns.

Mistake 2: Ignoring Column Order in Composite Indexes

A composite index on (user_id, created_at) is not the same as one on (created_at, user_id). Order matters because a composite index can only be used efficiently as a left-to-right prefix. If queries always filter by user_id first and sometimes by created_at, the (user_id, created_at) order serves both cases — but a query that only filters by created_at won't use that index efficiently at all.

Before creating a composite index, write out your actual query patterns and check which columns appear together, and in what order they're typically filtered.

Mistake 3: Not Indexing Foreign Keys

This one is deceptively common, especially in ORMs that don't do it automatically. A foreign key relationship without a supporting index means every join, every cascading delete, and every "find all children of this parent" query does a full table scan. This is often the real root cause behind a dashboard that "gets slower over time" as a related table grows.

Mistake 4: Trusting the Index Without Checking If It's Used

Adding an index doesn't guarantee your database will actually use it. Type mismatches, wrapping an indexed column in a function call in your WHERE clause, or a leading wildcard in a LIKE query can all silently prevent an index from being used, even though it exists on the table. Running EXPLAIN ANALYZE on important queries is the only way to confirm the index you added is actually being used.

Mistake 5: Over-Indexing for a Query That Should Be Cached Instead

Not every performance problem is an indexing problem. If a query is expensive because it's aggregating across millions of rows on every dashboard load, indexes will only get you so far — at some point the query should be pre-computed, cached, or served from a materialized view instead. Indexing helps databases find rows faster; it doesn't make heavy aggregation work disappear.

A Practical Way To Audit Existing Indexes

If you've inherited a codebase with indexes added over years by different people, a useful exercise is:

• Pull a list of all indexes and their sizes from system tables or built-in views.
• Cross-reference against actual query logs to see which indexes are used and which are dead weight.
• Remove indexes that aren't supporting any real query pattern.
• Re-check composite index column order against your most frequent queries.
• Re-run EXPLAIN on your top 10 slowest queries and confirm indexes are actually being hit.

Indexing is a genuinely small, well-understood piece of database design in theory, but it's one of the areas where "it works" and "it works well" diverge the most in real production systems. A little query-pattern-driven discipline tends to fix performance problems that look, on the surface, like they need a much bigger infrastructure change.

This article is based on patterns seen while auditing and optimizing databases for client projects. For more on how we approach performance and architecture work, see our approach to backend architecture and performance work.