Indexes and why queries get slow

How a database finds rows without reading them all.

A new app feels fast with a thousand rows. A year later the same page takes seconds, and nothing in the code changed. The cause is almost always the same: the database is reading far more data than the query needs. Understanding why, and the standard fix, is one of the most valuable skills in backend work.

Suppose the orders table has 10 million rows and you ask for the orders of customer 77. With no help, the database does a full table scan: it reads every row and checks the customer. If each row is about 100 bytes, that is roughly 1 GB read to return a handful of rows. An index is a separate sorted structure, usually a B-tree, that maps a column's values to where the rows live. Looking up a value in a sorted tree takes only a few steps (around 3 to 4 levels for 10 million rows), then a direct jump to the rows.

-- slow without an index: scans the whole table
SELECT * FROM orders WHERE customer_id = 77 ORDER BY created_at DESC LIMIT 20;

-- fix: one composite index serves both the filter and the sort
CREATE INDEX idx_orders_customer_time
  ON orders (customer_id, created_at DESC);

-- check what the database really does
EXPLAIN SELECT * FROM orders WHERE customer_id = 77;

Indexes are not free. Every index takes disk space, and every insert or update must also update each index, so writes get slower. An index on a column that is rarely used in queries is pure cost. Order matters in a composite index: one on (customer_id, created_at) helps queries filtering on customer_id, or on both, but not queries that filter only on created_at. Indexes also help little when a column has very few distinct values, like a boolean.