What is an index in SQL and how does it improve performance?
- Technology:
- SQL
Quick answer
An index is a separate sorted data structure (usually a B-tree) that lets the database find rows without scanning the whole table, at the cost of extra storage and slower writes.
Detailed explanation
Without an index, a query like WHERE email = 'a@b.com' reads every row (a full table scan). With an index on email the database walks the tree in a handful of steps and jumps straight to the matching rows. Indexes also speed up JOIN, ORDER BY and GROUP BY on the indexed columns.
Every INSERT, UPDATE and DELETE must also update every index on the table, so do not index everything. In a composite index the column order matters: an index on (last_name, first_name) helps a search on last_name alone but not on first_name alone. An index is also skipped when you wrap the column in a function (WHERE LOWER(email) = ...) or use a leading wildcard (LIKE '%smith'), and it is of little use on low-cardinality columns such as a boolean.
Example
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);
-- uses the index
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2026-01-01';
-- cannot use it efficiently (function on the column)
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';How to explain it in an interview
An index is a separate sorted structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up reads that filter, join or sort on the indexed columns, but every insert and update must also maintain the index, so I add indexes for real query patterns and check the execution plan.
Key points
- Faster reads, slower writes, more storage
- Column order in composite indexes matters
- Functions on the column and leading wildcards prevent index use
Common mistakes
- Indexing every column. Each index costs write time and storage, and the optimizer may ignore most of them.
- Wrapping an indexed column in a function, such as WHERE LOWER(email) = ..., which stops a normal index from being used.
- Ignoring column order in a composite index. An index on (a, b) helps queries on a, but not queries on b alone.
Follow-up questions
- What is a covering index?
- How do you decide which columns to index?