What is an index in a database and how does it speed up queries?
An index is a separate sorted structure, usually a B-tree, that maps column values to row locations. It lets the database jump to matching rows instead of scanning the whole table, at the cost of extra storage and slower writes.
Explain it simply
A textbook index at the back lists topics with page numbers. To find "Normalization" you look it up alphabetically and jump to page 142, instead of reading all 600 pages. A database index does the same for column values and row locations.
Example
CREATE INDEX idx_students_email ON students(email);
SELECT * FROM students WHERE email = 'asha@example.com';- Without the index, the database reads every row: a full table scan, O(n).
- With the index, it walks a balanced B-tree in about O(log n) steps, then reads the matching row.
- For 10 million rows, that is the difference between millions of reads and a handful.
The cost: every INSERT, UPDATE or DELETE must also update the index, and the index takes disk space. So you index columns that are searched, joined or sorted often, not every column.
Primary keys and unique constraints create an index automatically in most databases.
What to say in the interview
"An index is a sorted lookup structure, typically a B-tree, that turns a scan into a seek. It speeds up reads on filtered, joined or sorted columns but slows writes and uses space, so I add indexes based on actual query patterns."
Common mistakes
- Indexing every column and then wondering why inserts are slow.