What is an index in SQL and how does it improve performance?
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.
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.
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';Key points
- Faster reads, slower writes, more storage
- Column order in composite indexes matters
- Functions on the column and leading wildcards prevent index use
Questions interviewers ask next
- What is a covering index?
- How do you decide which columns to index?