Understanding Database Indexes

Indexes are data structures that enable fast data retrieval without scanning every row.

B-Tree Index (Default)

Most databases use B-Tree indexes.

          [50]
         /    \
      [25]    [75]
     /   \   /   \
   [10] [30][60] [90]

Supports: Range queries, ORDER BY, equality Not ideal for: Full-text search, geospatial

Hash Index

Perfect hash function maps key → bucket.

Supports: Exact equality only (WHERE id = 5) Not ideal for: Range queries

Composite Index

Index on multiple columns: (last_name, first_name)

-- Uses index (leftmost prefix rule)
SELECT * FROM users WHERE last_name = 'Smith';

-- Also uses index
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

-- Does NOT use index (skips leftmost column)
SELECT * FROM users WHERE first_name = 'John';

Covering Index

Index includes all columns needed by query — no table lookup needed.

CREATE INDEX idx_user_email ON users(email, name);
-- This query is served entirely from index:
SELECT name FROM users WHERE email = 'x@x.com';

Index Pitfalls

• Too many indexes slow down writes
• Indexes consume disk space
• Low-cardinality columns (boolean) rarely benefit from indexing

EXPLAIN ANALYZE

Always use EXPLAIN to verify index usage:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

Conclusion

Index the columns you query most. Use composite indexes wisely, covering indexes for read-heavy tables, and monitor with EXPLAIN.