Indexes

What is it?

Picture a users table with 10 million rows, and you run SELECT * FROM users WHERE email = 'amara@example.com'. Without any help, the database has no shortcut — it has to check every single row's email column, one by one, until it either finds a match or reaches the end. On a huge table, that's slow, and it gets slower as the table grows.

An index fixes this. It's a separate, ordered structure — much like the index at the back of a textbook — that maps values in a specific column straight to the location of the rows that have them. With an index on email, the database can jump almost directly to the matching row, instead of reading the whole table.

Explain like I'm 10

Without an index, finding a topic in a book means flipping through every page from the start. With an index at the back of the book, you look up the topic alphabetically and it tells you exactly which page to turn to — you skip straight there.

Examples

Before: scanning every row

-- No index on email.
-- The database checks every row's email, one at a time,
-- until it finds (or rules out) a match.
SELECT * FROM users WHERE email = 'amara@example.com';

On a table with millions of rows and no index, this query's cost grows directly with the table's size — it's called a 'full table scan.'

After: creating an index

CREATE INDEX idx_users_email ON users (email);

-- Same query as before, now much faster:
SELECT * FROM users WHERE email = 'amara@example.com';

After the index exists, the same query can jump almost directly to the matching row using the index, instead of checking every row in the table.

How it works

Most indexes are built as a B-tree — a sorted, tree-shaped structure that lets the database narrow down to a matching value in a small number of steps, similar to how you'd find a word in a sorted dictionary by repeatedly splitting the search space in half, rather than reading every entry from the start.

The tradeoff: an index isn't free. Every time a row is inserted, updated, or deleted, every index on that table has to be updated too, which adds overhead to writes. Indexes also take up extra disk space. That's why databases don't index every column automatically — indexing is a deliberate choice, usually made for columns that are frequently searched or joined on.

Why does it exist?

Without indexes, every query that filters or joins on a non-trivial condition would need to scan an entire table, and query time would grow linearly (or worse) with data size. Indexes exist to trade a bit of extra storage and slightly slower writes for dramatically faster reads on the columns that matter most.

When to use it

Add an index on columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY clauses — especially on large tables. Foreign key columns are also strong candidates, since they're joined on often.

When not to use it

Avoid indexing columns that are rarely searched or that change on almost every write (since every index update adds write overhead), and avoid indexing very small tables — scanning a few hundred rows is already fast enough that an index adds cost without meaningful benefit.

Common mistakes

• Adding an index to every column 'just in case,' which slows down every write without meaningfully speeding up reads that don't use those columns.

• Expecting an index to help a query that doesn't filter, join, or sort on the indexed column at all.

• Forgetting that foreign key columns often benefit from an index too, since joins on them are common.

Practice exercises

1. Easy:

   Explain in your own words why a full table scan gets slower as a table grows, but an indexed lookup barely does.

2. Medium:

   Write a CREATE INDEX statement for a status column on an orders table that's frequently filtered by status.

3. Hard:

   Describe a scenario where adding an index would actually hurt overall performance rather than help it.

Interview questions

What is a database index?

A separate, ordered lookup structure the database maintains for a column, letting it jump to matching rows instead of scanning the whole table.

What's the tradeoff of adding an index?

Faster reads on the indexed column, at the cost of extra disk space and slower writes, since every insert, update, or delete has to update the index too.

What data structure do most database indexes use internally?

A B-tree, which allows narrowing down to a matching value in relatively few steps by keeping data sorted in a tree shape.