Indexes Explained With Real Examples

What an index actually is

Without an index, a table is just an unordered pile of rows — the database has no way to know where the rows matching your WHERE clause live, so it reads every single one. That's a table scan, covered in depth in Index Scan vs Table Scan.

An index fixes this by maintaining a second, separate structure alongside the table: the indexed column's values, kept in sorted order, each paired with a pointer to where the full row actually lives. Sorting is what makes the shortcut possible — once values are sorted, you can find any one of them without checking every value before it.

How a B-tree makes lookups fast

Most database indexes are B-trees (balanced trees), not simple sorted lists. A B-tree organizes values into a shallow, wide tree of nodes, where each node holds a range of values and points down to child nodes covering narrower sub-ranges. Looking up a value means starting at the root and following one branch per level — for a table with a million rows, a B-tree is typically only 3–4 levels deep, so the lookup takes 3–4 comparisons instead of up to a million.

Example: a single-column index

Take an orders table with 2 million rows and a query that looks up orders by customer:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

SELECT id, total, status
FROM orders
WHERE customer_id = 4471;

Before the index, this query has to check customer_id against all 2 million rows. After it, the database walks the B-tree on customer_id, lands on the handful of rows for customer 4471, and reads only those.

Example: composite indexes and column order

A composite index covers more than one column, and it's sorted by the first column, then by the second column within each value of the first — like a phone book sorted by last name, then first name. Column order is not cosmetic; it determines which queries the index can actually serve.

CREATE INDEX idx_orders_cust_status_date
  ON orders (customer_id, status, order_date);

This index can serve a query filtering on customer_id alone, on customer_id and status together, or on all three — because each of those is a leftmost prefix of the column order. It cannot efficiently serve a query that filters on status alone, because within the index, rows for a given status are scattered across every customer, not grouped together.

Example: covering indexes

Normally, once the database finds a matching entry in an index, it still has to follow the pointer back to the full row to fetch any column that isn't in the index itself — an extra read per matching row. A covering index avoids that by including every column the query needs, so the database can answer the entire query from the index alone.

CREATE INDEX idx_orders_covering
  ON orders (customer_id, status)
  INCLUDE (total, order_date);

-- Answered entirely from the index — no trip back to the table
SELECT total, order_date
FROM orders
WHERE customer_id = 4471 AND status = 'pending';

This is also why SELECT * is expensive: pulling every column almost guarantees the database needs to leave the index and fetch the full row anyway, even when a covering index exists for the columns you actually need.

Unique vs non-unique indexes

A unique index does everything a regular index does, plus it enforces that no two rows can share the same indexed value — this is how a PRIMARY KEY or a UNIQUE constraint is typically implemented internally. A regular index allows duplicates and is used purely for lookup speed, with no constraint attached.

CREATE UNIQUE INDEX idx_customers_email ON customers (email);
-- Now a duplicate email fails at insert time, and email lookups are fast

Before/after: adding the right index

Same 2 million-row orders table, same query — before and after adding idx_orders_cust_status_date from the composite index example above.

SELECT id, total, order_date FROM orders
WHERE customer_id = 4471 AND status = 'pending';

Seq Scan on orders  (rows=2,003,442)
  Filter: customer_id = 4471
    AND status = 'pending'
  Rows Removed by Filter: 2003429

SELECT id, total, order_date FROM orders
WHERE customer_id = 4471 AND status = 'pending';

Index Scan using idx_orders_cust_status_date
  (rows=13)
  Index Cond: customer_id = 4471
    AND status = 'pending'

~339x faster one CREATE INDEX statement, same query, same 2M-row table

The cost indexes add to writes

Indexes aren't free. Every INSERT, UPDATE, or DELETE has to update every index on the table, not just the table itself — so a table with eight indexes pays eight extra writes for every one row change. This is exactly why you shouldn't index every column "just in case." The full trade-off, including when an index actively makes performance worse, is covered in When NOT to Use Indexes.