What is an index in SQL and why is it used?
An index is a data structure that helps the database find rows faster, much like the index at the back of a book. It speeds up SELECT queries and WHERE lookups on the indexed columns. The trade off is that indexes take extra storage and slow down inserts, updates, and deletes, because the index must be kept up to date.
Why it speeds things up
Without an index the database may scan every row to find matches, which is slow on large tables. An index keeps the values sorted in a structure, often a B tree, so the database can jump close to the answer instead of scanning everything.
CREATE INDEX idx_users_email ON users(email);
-- now lookups by email are much fasterThe trade off
- Reads get faster on indexed columns.
- Writes get slower because the index must be updated.
- Indexes use extra disk space.
Show balance. Say indexes speed up reads but slow down writes and use space, so you index columns you search or join on, not every column. That judgement is exactly what interviewers listen for.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions