Indexes — Why Some Queries Are Fast

What an index actually is

An index is a separate, sorted data structure (almost always a B-tree) that maps a column's values to row pointers. Without an index, finding `WHERE email = 'x'` requires a FULL TABLE SCAN — read every row. With an index on `email`, the database does an O(log n) tree search.

CREATE INDEX idx_users_email ON users(email);

-- Now this query is O(log n) instead of O(n):
SELECT * FROM users WHERE email = 'ada@example.com';

The cost

• Disk space — every index is a copy of (column → row pointer), often 10-30% of the table size.
• Slower writes — every INSERT/UPDATE/DELETE must also update every relevant index.
• On UPDATEs, only indexes on changed columns get touched.

Composite indexes

An index on `(country, city)` accelerates queries filtering on `country`, or on `country AND city`. But NOT on `city` alone — because the index is sorted by country first. Order matters.

EXPLAIN — your single most important debugging tool

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid';

The output tells you: was an index used? How many rows did it actually scan? Did it sort or hash join? When a query is mysteriously slow, EXPLAIN is the first thing to look at.