How database indexes work and when to use them

What an Index Does

An index is a separate data structure that maps column values to row locations. Without an index, every query scans every row. With one, the database jumps directly to matching rows.

B-Tree Index

The default index type in most databases. Sorted, balanced tree. Supports equality lookups (WHERE email = 'x'), range queries (WHERE age > 30), and ORDER BY. Read time: O(log n). Write time: O(log n) per insert/update — every write must update the index.

When to Add an Index

-- High selectivity column: good candidate
-- email is almost always unique
CREATE INDEX idx_users_email ON users(email);

-- Low selectivity: bad candidate
-- gender has 2-3 values, index barely helps
CREATE INDEX idx_users_gender ON users(gender); -- avoid

-- Composite index: order matters
-- Supports WHERE user_id = X AND created_at > Y
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Covering Index

If your query only needs columns that are all in the index, the database never touches the main table — it reads entirely from the index. This is called an index-only scan and is very fast.

Index trade-off: every index slows writes (must maintain the index) and uses disk space. Only index columns you actually query.