Skip to content

Performance & indexing · Medium

What is an index, how does it speed up queries, and what does it cost?

Short answer

An index is a separate, sorted structure, usually a B-tree, that maps column values to row locations, letting the database seek directly to matching rows instead of scanning the whole table. It helps filters, joins and ordering on the indexed columns. The cost is extra storage and slower inserts, updates and deletes, because every index must be maintained.

Detailed answer

Analogy: the index at the back of a book. Without it you read every page (a full table scan); with it you jump straight to the right pages.

-- See how SQLite plans the query before and after adding an index
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 7;
CREATE INDEX IF NOT EXISTS idx_orders_customer_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 7 AND order_date >= '2026-01-01';

The first plan says SCAN orders; after the index it reports SEARCH orders USING INDEX idx_orders_customer_date.

Points interviewers look for:

  • Composite index column order matters. An index on (customer_id, order_date) helps queries filtering on customer_id alone, or on both, but not on order_date alone (the leftmost-prefix rule).
  • Selectivity: indexing a column with very few distinct values (such as status) rarely helps on its own.
  • Primary keys are indexed automatically; foreign keys usually are not, and are good candidates because they are join columns.
  • On a 120-row table none of this is measurable — indexes matter at millions of rows.