Indexes: What They Are & How They Help

You need one definition from a 900-page textbook.

You could turn every page. Or you could flip to the index at the back, find the word, and go straight to page 412.

The database has the same choice.

Why & what

A full table scan is turning every page. It works, and it gets slower every time the table grows.

The fix is the same as the book's: keep a small, sorted list of one column, with a pointer to where each value actually lives.

An index is a separate structure that stores one or more columns in sorted order, along with a pointer to the row's location — so the database can jump straight to a value instead of scanning for it.

How it works

The Student table stores rows in whatever order they were inserted: 104 Dev, 101 Aisha, 107 Gita, 103 Cara. Now find roll 103.

1. Without an index. The rolls are unsorted, so there's no way to skip ahead. Read every block. Cara happens to be last, so you did all the work.
2. Build an index.

CREATE INDEX idx_roll ON Student(roll_no);

3. What that actually created. A second, small structure holding roll_no in sorted order — 101, 103, 104, 107 — each with a pointer to the block its row lives in.
4. Now search again. Because the index is sorted, the database can jump — check the middle, decide which half, repeat. It finds 103 in a couple of hops, reads the pointer, and fetches exactly one block of the real table.
5. Notice why the primary key is already fast. Databases build an index on the primary key automatically. That's why WHERE roll_no = 101 was never the slow query — you had an index all along without asking.

Two kinds are worth knowing:

• Clustered index — the table itself is kept in this order. The rows are the index, so there's no pointer hop. You get one per table, because rows can only be in one physical order.
• Non-clustered index — a separate sorted list that points back to the rows. You can have many, and each one costs extra disk space.

Pause here. Why can a table have only one clustered index?

Because a clustered index defines the physical order of the rows on disk, and rows can only be stored in one order at a time.

Notice: an index is a second, sorted copy of one column — plus a pointer home.

Common confusion

"So index everything?" No — and this is the answer interviewers are actually listening for.

Every index must be kept up to date. Every INSERT, UPDATE and DELETE on that column has to modify the index too. Ten indexes means ten extra updates per write. So an index buys you read speed and pays for it with write speed and disk space.

Second: an index can be quietly ignored. Write WHERE UPPER(name) = 'AISHA' and the index on name becomes useless, because the index stores names, not upper-cased names. Same with a leading wildcard: LIKE '%isha' can't use an index, though LIKE 'Ais%' can. Wrapping an indexed column in a function is the most common way people accidentally disable their own index.

Third: an index doesn't help when you want most of the rows. If your query returns 80% of the table, hopping through an index and fetching rows one at a time is slower than just scanning the whole thing. The optimiser knows this and will skip your index on purpose.

Interview angle

1. "What is an index and how does it speed up a query?" — A sorted structure with pointers, letting the database jump to a value instead of scanning. Mention that it turns "read every block" into "read one or two."
2. "Why not index every column?" — Every write must update every index, and each index costs space. Reads get faster, writes get slower.
3. "Difference between clustered and non-clustered?" — Clustered sets the physical row order, so one per table and no pointer hop. Non-clustered is a separate list pointing back, so many per table.
4. "Give a case where an index exists but isn't used." — A function wrapping the column, a leading % wildcard, or a query returning most of the table.

Recap

An index is a sorted structure with pointers that lets the database jump to a value instead of scanning — clustered sets the physical row order (one per table), non-clustered is a separate list (many per table), and every index makes writes slower.