Missing Indexes
Missing indexes force full table scans whose cost grows with table size, so queries that pass testing collapse under production volume. Design indexes from real query predicates, confirm them with EXPLAIN, and test at production scale.
A missing index means a frequent query has no index to support its filter, join, or sort, so the database scans the entire table to find matching rows. With a few hundred test rows the scan is instant; with millions of production rows it is catastrophic. This is one of the most common and most fixable database performance problems.
Why It Happens
Indexes are invisible in application code, so they are easy to forget. Development databases are tiny, so unindexed queries appear fast and pass review. New query patterns are added over time without revisiting which indexes they need. Sometimes the schema author indexes primary keys and stops, missing the foreign keys and filter columns that real queries use.
Why It Hurts
A full table scan reads every row regardless of how many match, so cost grows with table size rather than with result size. The query that returned in 5 milliseconds during testing takes 5 seconds in production and gets slower every week as data accumulates — a scalability cliff. Scans evict useful pages from cache and saturate I/O, degrading unrelated queries too. Under concurrency, long scans hold resources and amplify contention. The failure mode is gradual, so it often surfaces as a mysterious slowdown rather than an obvious bug.
Warning Signs
- Query plans show sequential or full-table scans on large tables.
- Filters on non-key columns are slow, especially on foreign keys.
- Queries that were fast in testing degrade steadily as data grows.
- Database CPU and I/O spike on a handful of recurring queries.
Better Alternatives
Design indexes from real query patterns: index the columns used in WHERE, JOIN, and ORDER BY clauses, and consider composite indexes whose column order matches the query. Read query plans with EXPLAIN to confirm an index is used and chosen. Use covering indexes to satisfy read-heavy queries entirely from the index. Balance read gains against write cost so you index what matters rather than everything.
How to Refactor Out of It
Identify the slow queries using the database's slow-query log or your APM, ordered by total time. Run EXPLAIN to find scans, then add indexes matching their predicates and re-check the plan. Test against production-scale data, not the dev dataset, so the improvement is real. Monitor index usage over time and drop indexes that no query uses to keep writes fast. Make index review part of adding any new query pattern.