Before caching layers, before replica sharding, before rewriting application logic: check the indexes. Missing indexes are the most common and most fixable cause of database pain.

Find the real offenders

Turn on the slow query log and rank by total time, not single query time. A query taking two hundred milliseconds that runs ten thousand times an hour outranks the monster query that runs once a day.

Designing the index

Composite indexes follow the leftmost prefix rule: equality columns first, then the range or sort column. Covering indexes that include the selected columns skip table access entirely and turn hot queries into memory-speed lookups.

Every index is a bet: faster reads in exchange for slower writes and more storage. Place bets where traffic lives.

The anti-patterns

  • Indexing every column because storage is cheap; write amplification is not.
  • Leading with low-cardinality columns like status flags.
  • Forgetting that functions on indexed columns disable the index.
  • Adding an index without measuring the plan change afterwards.

We make index reviews a standing agenda item in performance engagements at ByteHexa, because the hour spent reading query plans routinely saves thousands in infrastructure.