Skip to content
Quantoros

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

Related questions