Database index

What is a database index?

A database index is a separate, sorted structure that a database keeps next to a table so it can find rows without reading every one. Without an index, answering a query like WHERE customer_id = 42 means scanning the whole table. With an index on customer_id, the engine jumps straight to the matching rows.

The everyday comparison is the index at the back of a book. You do not read all 400 pages to find one topic, you look it up in the index, get a page number, and turn straight to it. A database index stores column values in sorted order, each value paired with a pointer back to the full row.

You create one with a single statement:

CREATE INDEX idx_orders_customer
ON orders (customer_id);

Creating the index does not force the database to use it. The query optimizer decides, per query, whether reading the index is cheaper than scanning the table, and you can see the choice it made in the execution plan.

B-tree indexes and covering indexes

When you create an index without asking for anything special, you get a B-tree. It is the default in PostgreSQL and the standard structure behind indexes in MySQL and SQL Server. A B-tree keeps values in sorted order, which is why a single index can answer equality tests (=), range scans (>, <, BETWEEN) and ORDER BY on the same column.

A covering index goes one step further: it holds every column a query needs, so the engine answers from the index alone and never touches the table. PostgreSQL calls this an index-only scan. You add the extra payload columns with an INCLUDE clause:

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

Now SELECT status, total FROM orders WHERE customer_id = 42 reads only the index. The status and total values sit in the index leaf next to customer_id, so there is no second read into the table to fetch them.

Composite indexes and column order

A composite index covers more than one column, and the order of the columns decides which queries it helps. An index on (customer_id, order_date) is sorted by customer_id first, then by order_date within each customer.

That index helps queries that filter on customer_id, or on customer_id and order_date together. It does nothing for a query that filters on order_date alone, because order_date is not the leading column. MySQL calls this the leftmost prefix rule: an index can be used only for a leftmost prefix of its columns. The guidance is consistent across PostgreSQL, MySQL and SQL Server: put the column you test for equality first, then order the rest from most distinct to least.

Why an index goes unused

An index only helps when the optimizer can match your predicate against the sorted values it stores. SQL Server calls a predicate that an index can use sargable, short for search argument able. A filter like order_date >= '2026-01-01' is sargable and can seek straight into the index. Wrap the same column in a function, as in YEAR(order_date) = 2026, and the predicate stops being sargable, because the engine would have to compute YEAR() for every row before it could compare.

The same trap appears with LIKE. A pattern anchored at the start, such as LIKE 'Patrick%', can use a B-tree index. A leading wildcard, such as LIKE '%patrick%', cannot, because the index is sorted from the first character and there is no prefix to seek on. These same sargable predicates are also what make predicate pushdown effective, so writing them cleanly pays off in more than one place.

The cost of every extra index

Reads get faster, but an index is never free. Every INSERT, UPDATE and DELETE that changes an indexed column has to update the index as well. A table with five indexes writes five extra structures on every change, so a busy transactional database can slow down under its own indexes. Microsoft's guidance is blunt about it: creating many indexes speculatively "slows down data modifications and can cause concurrency problems".

Indexes also take disk space. A covering index copies its payload columns into the index leaf, so those values now live in both the table and the index. The working rule is to index the queries that are actually slow and to drop the indexes a database reports as unused.

When an index is the wrong tool

Indexes work best when a query touches a small slice of a big table, the typical OLTP pattern of pulling one customer or one invoice. They help far less when a query reads most of the rows anyway, which is the normal shape of analytics. Summing revenue across five years of orders has to scan almost everything, and a row-by-row B-tree index buys little there.

For that workload the better tools are columnstore storage, a data warehouse built for wide scans, or partitioning to skip whole date ranges before any index is consulted. Primary keys and unique constraints, which you define as part of database normalisation, always get an index for free. Beyond those, let the way a table is actually queried decide where an index earns its place.