What are database indexes and how do they work

· Category: SQL & Databases

Short answer

Indexes are data structures that speed up read queries by allowing the database to find rows without scanning the entire table. They slow down writes and consume extra storage. For schema design basics, see how to design a database schema from scratch. For managing schema changes, see how to implement database migrations.

Steps

  1. Identify slow queries in your application logs
  2. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses
  3. Choose index types: B-tree for range queries, hash for equality
  4. Measure query performance before and after
  5. Remove unused indexes to reduce write overhead

Tips

  • Index cardinality matters; low-cardinality columns benefit less
  • Covering indexes can eliminate table lookups entirely
  • For transactional consistency with indexes, see what are database transactions