An index is like a book's index: instead of scanning every page, the database jumps straight to the rows it needs. Understanding them turns multi-second queries into millisecond ones.
How a B-Tree Index Works
Most indexes are balanced trees that keep values sorted, so the database can binary-search instead of scanning. That's why indexed lookups, ranges, and ORDER BY get dramatically faster.
Composite Index Column Order
-- Index on (status, created_at)
CREATE INDEX idx ON orders(status, created_at);
-- ✅ Uses the index (filters on the leftmost column)
WHERE status = 'paid' AND created_at > '2026-01-01';
-- ❌ Can't use it efficiently (skips the leftmost column)
WHERE created_at > '2026-01-01';This 'leftmost prefix' rule is the most misunderstood part of indexing. Order composite columns by how you filter: equality columns first, range columns last.
Covering Indexes
If an index contains every column a query needs, the database never touches the table at all — an 'index-only scan'. Add frequently-selected columns to turn a hot query into a covering one.
The Trade-Off
Indexes speed reads but slow inserts/updates and consume storage. Index your real query patterns, then confirm with EXPLAIN — never guess.
