What is an index and why does it help?
SQLLevel: IntermediateDatabase Design
What is an index and why does it help?
Hints
- How is lookup performed without the structure and what changes with it?
- Check selectivity, key order and the cost of data updates.
Answer
An index is a separate data structure maintained by the database that allows fast lookups of rows without scanning the entire table. The most common type is a B-tree index, which supports equality lookups, range queries, and sorting efficiently.
-- Without an index: full table scan for every query
SELECT * FROM orders WHERE customer_id = 12345;
-- With an index: fast lookup
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Tradeoff: indexes speed up reads but slow down writes (every INSERT/UPDATE/DELETE must also update the index).
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT