SQL Indexes

A B-tree index turns a slow table scan into a fast lookup, at the cost of writes.

SQL · Intermediate · ~4 min

In plain English

A sorted lookup table at the back of the book. Without it the database reads every page; with it, it jumps straight to the row.

Why it's worth your time

It's usually the difference between a 4-second query and a 4-millisecond one, and knowing when an index WON'T be used is the real skill.

If you remember three things

Indexes speed reads and slow writes
Composite index column order matters — leftmost prefix rule
A function applied to an indexed column disables the index

Overview

An index is a sorted side structure — typically a B-tree — that maps a column's values to their rows, so the database can seek directly instead of scanning every row. Indexes make reads on WHERE, JOIN, and ORDER BY columns dramatically faster, but each one adds storage and slows writes, since every INSERT, UPDATE, and DELETE must maintain it.

In an interview

A B-tree index keeps indexed values sorted with pointers to rows, turning an O(N) scan into an O(log N) seek. Index the columns you filter, join, and sort on. Composite indexes help left-to-right only. The tradeoff: faster reads, slower writes, more storage — so index deliberately and verify with EXPLAIN.

Production defaults

Index: columns in WHERE, JOIN and ORDER BY. Not every column
Composite order: equality columns first, then range columns
Verify: EXPLAIN every slow query. Assuming an index is used is how you tune the wrong thing
Write-heavy tables: each extra index is a tax on every insert. Audit unused ones

What breaks

Index exists but isn't used — A function or cast on the column (WHERE DATE(created) = …). Rewrite as a range, or add a functional index.
Writes got slow — Too many indexes. Every one must be updated on every insert and update.

Watch it explained

what is a database index? — Hussein Nasser, 5:12