tech

Database Indexing Strategies

databasesqlperformance

Indexes are the difference between a 10ms query and a 10s query.

B-Tree Index

The default. Great for equality and range queries. Use on columns you filter, sort, or join on.

Composite Index

Index multiple columns together. Order matters: put high-selectivity columns first.

When Not to Index

  • Small tables (under 1000 rows)
  • Columns rarely queried
  • Write-heavy tables (indexes slow writes)

Use EXPLAIN ANALYZE before adding indexes. Measure, don’t guess.