What a database index actually is

The single most common fix for "this query is slow" — and why adding one isn't free, so understanding what it actually does matters before reaching for it blindly.

Beginner

3 min read

The problem: finding a row means checking every row, by default

SELECT * FROM users WHERE email = 'ada@example.com';

Without any help, a database answering this query has no way to know where the matching row is — it has to check every single row in the users table, one at a time, comparing each one's email column to the target. This is called a full table scan, and its cost grows directly with the table's size: instant on 100 rows, and genuinely slow on 10 million rows, even though the query itself never changed.

An index: a separate, pre-sorted structure that makes lookups fast

CREATE INDEX idx_users_email ON users (email);

An index is a separate data structure the database maintains, specifically organized to make looking up rows by a given column (or columns) fast — commonly implemented as a B-tree, a structure that supports finding a specific value in roughly O(log n) instead of the O(n) a full scan requires. Once idx_users_email exists, the same WHERE email = ... query can jump almost directly to the matching row instead of scanning the whole table — the exact same practical benefit binary search gets from sorted data, covered in the DSA domain's Big-O lesson, applied here at the database level.

The book-index analogy, made precise

A book's index at the back doesn't contain the book's actual content — it's a separate list mapping topics to page numbers, letting you jump directly to the right page instead of reading the entire book front to back looking for one topic. A database index works the same way: it's a separate structure mapping column values to the location of the matching rows, letting a query jump directly to the relevant rows instead of scanning the entire table.

The real cost: indexes aren't free, which is why you don't index everything

INSERT INTO users (email, name) VALUES ('grace@example.com', 'Grace');

Every index on a table has to be updated on every INSERT, UPDATE, or DELETE that touches the indexed column — the database doesn't just write the new row, it also updates every index that includes that column, to keep them accurate. More indexes means slower writes, and each index also takes real disk space. This is the actual trade-off: an index speeds up reads (specifically, reads that filter or sort on the indexed column) at the cost of slower writes and more storage — "just add an index to everything" isn't free optimization, it's trading one cost for another, and the right call depends on whether that table is read far more often than it's written to (usually, but not always, true).

Which columns are actually worth indexing

The practical rule: index columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY — the columns real queries actually filter or sort by. A users.email column that's constantly used to look up a specific user (during login, for instance) is a strong candidate. A column that's rarely queried directly, or a table that's written to constantly but read rarely, often isn't worth indexing at all — the write-speed cost wouldn't be earning its keep.

The primary key is (almost) always indexed automatically

Most databases automatically create an index on a table's primary key (id in the earlier database lessons' examples) the moment the table is created — this is why looking up a row by its id is fast by default, without ever having to think about indexing it explicitly. It's every other column — the ones used in real query filters but not automatically indexed — that requires a deliberate decision.