Database indexes explained: how a B-tree makes queries fast

Without an index, finding rows means reading the entire table (a full scan). With an index, the database jumps almost directly to the rows.

The book analogy

A book's index lists topics in alphabetical order with page numbers. You do not read the whole book to find "caching". A database index is a sorted copy of one or more columns with pointers to the full rows.

B-tree

Most indexes are B-trees, a balanced tree. A lookup starts at the root and follows a few branches to a leaf, so searching a million rows takes only a handful of steps (about log n), instead of reading a million. B-trees also keep keys sorted, so they are great for:

• Equality: WHERE email = 'a@x.com'
• Ranges: WHERE created_at > '2026-10-01'
• Sorting: ORDER BY created_at

Creating one

CREATE INDEX idx_articles_author ON articles (author_id);
CREATE UNIQUE INDEX idx_users_email ON users (email);

A primary key and a unique constraint create an index automatically. Foreign key columns are not always indexed, so add them yourself.

Composite indexes and the leftmost-prefix rule

An index on (author_id, created_at) is sorted by author first, then date. It can serve:

• WHERE author_id = 5
• WHERE author_id = 5 AND created_at > ...
• WHERE author_id = 5 ORDER BY created_at

It generally cannot help with WHERE created_at > ... alone, because it is not the leftmost column. Order columns by how you filter: equality columns first, then range or sort columns.

Covering index

If an index contains all the columns the query needs, the database answers from the index alone without touching the table. Fast for hot queries.

The cost

• Every INSERT, UPDATE and DELETE must also update each index, so more indexes slow writes.
• Indexes take disk and memory.
• So index what you query, not every column.

Why your index may not be used

• Function on the column: WHERE LOWER(email) = ... cannot use a plain index on email. Use an expression index or store normalised data.
• Leading wildcard: LIKE '%word' cannot use a B-tree. LIKE 'word%' can.
• Low selectivity: an index on a column with few distinct values (like a boolean) often does not help, because the planner prefers a scan when many rows match.
• Type mismatch (comparing a number column to a string).
• Stale statistics. Run ANALYZE.

How to check

Use EXPLAIN (or EXPLAIN ANALYZE) and look for an index scan versus a sequential/full scan. Do not guess, measure.

Takeaway: index the columns you filter, join and sort by, order composite columns carefully, and verify with EXPLAIN.

---

Backend & Database series, Part 1 (SQL and transactions): 2 of 4. Previous: SQL JOINs explained with simple examples.