SQL Indexes Explained

Without an index, finding rows that match a WHERE condition means the database scans the entire table, row by row, checking each one — a full table scan. On a table with a handful of rows this is instant; on a table with ten million rows, it's slow, and it stays slow no matter how selective the condition is, because every row still has to be checked.

An index fixes this by maintaining a separate, sorted structure — typically a B-tree — that maps column values to the rows containing them, so the database can jump almost directly to matching rows instead of checking every one.

CREATE INDEX idx_users_email ON users(email);

After this, SELECT * FROM users WHERE email = 'ada@example.com' can use the index to locate the matching row(s) in roughly logarithmic time, instead of scanning the whole table linearly.

What indexes speed up

• WHERE on the indexed column — the primary case, as above.
• JOIN conditions — joining orders.customer_id to customers.id is fast when customer_id (and id, which usually already has one as the primary key) is indexed; without it, every join has to scan one side's table for each row on the other.
• ORDER BY on the indexed column — since the index already stores values in sorted order, the database can sometimes read results out already sorted, avoiding a separate sort step.

A primary key is indexed automatically in essentially every database — this is why primary key lookups are fast by default, without you doing anything extra.

What indexes cost

Indexes aren't free, which is why you don't just index every column:

• Slower writes. Every INSERT, UPDATE, or DELETE has to update every index on the table too, not just the table's raw data — more indexes means more work per write.
• Storage. An index is a real, separate data structure taking up disk space, sometimes substantial on a large table.
• Diminishing returns on low-selectivity columns. Indexing a BOOLEAN column like is_active, where the values are overwhelmingly one or the other, often helps little — the database may decide a full scan is cheaper than jumping through the index anyway, since so much of the table matches regardless.

Composite indexes and the left-prefix rule

An index can span multiple columns:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

This composite index speeds up queries filtering on customer_id alone, or on customer_id AND order_date together — but it does not help a query filtering on order_date alone. A composite index is only usable from its leftmost column inward, the same way a phone book sorted by last-name-then-first-name lets you jump to a last name efficiently, but is useless for finding everyone with a given first name.

Deciding what to index

A reasonable starting rule: index columns you filter on (WHERE), join on, or sort by (ORDER BY) frequently and on large tables — and don't index columns you rarely query by, since the write cost isn't worth it. When in doubt, most databases have an EXPLAIN command that shows whether a specific query is actually using an index or falling back to a full scan, which is the reliable way to verify an index is helping rather than guessing.

The next lesson covers transactions — for when several related writes need to succeed or fail as a single unit.