Indexes

Without help, finding rows that match a condition means reading the whole table — fine for 100 rows, painfully slow for 100 million. An index is a separate, sorted data structure that lets the database jump straight to matching rows.

Real-world analogy: the index at the back of a book. To find "normalization", you don't read every page — you look it up in the index and go straight to page 212.

Creating an index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Now queries filtering or joining on customer_id can use the index instead of scanning every order. Most databases store indexes as B-trees, which keep values sorted and support equality (=), ranges (<, BETWEEN), prefix matches (LIKE 'abc%'), and sorting.

Primary keys and UNIQUE constraints get an index automatically. Foreign keys usually don't (MySQL's InnoDB is an exception) — index them yourself, since they're used in almost every join.

Composite indexes

An index can cover several columns:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_on);

Column order matters. Like a phone book sorted by surname, then first name, this index helps queries that filter on customer_id, or on customer_id and ordered_on — but not on ordered_on alone.

A common rule of thumb: put columns tested with equality (=) first, and the column used for ranges or sorting last.

Unique and partial indexes

CREATE UNIQUE INDEX idx_customers_email ON customers (LOWER(email));
CREATE INDEX idx_open_orders ON orders (ordered_on) WHERE status IN ('pending', 'shipped');

• A unique index on LOWER(email) blocks duplicate emails that differ only in case — an expression index.
• A partial index covers only some rows, keeping it small when you only ever query open orders (SQLite and PostgreSQL).

The cost of indexes

Indexes aren't free:

• Every INSERT, UPDATE, and DELETE must also update every index on the table, so writes get slower.
• They take disk space and memory.

Index the columns your queries actually filter, join, and sort on — and measure. The next lesson shows how to check whether an index is used.

When an index can't help

• Functions on the column: WHERE LOWER(email) = '...' can't use a plain index on email (an expression index can).
• Leading wildcards: LIKE '%son'.
• Low-selectivity columns: an index on a yes/no column rarely helps, because each value matches half the table.
• Tiny tables: scanning is already fast.