Database Index

A database index is an auxiliary data structure, most often a B-tree, that lets the database locate rows matching a column value without reading the entire table. It works like the index at the back of a book.

Why it matters

Indexes are the biggest single lever for read performance. A lookup on a million-row table can drop from scanning every row to touching a handful of pages. Each index costs disk space and slows inserts and updates, because it must be maintained on every write, so you add them for the queries that matter rather than for every column.

Example

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;

The composite index serves both the filter and the sort.

Common mistakes

• Indexing every column. Write cost and storage grow while most indexes are never used.
• Ignoring column order in composite indexes. An index on (a, b) does not help a query filtering only on b.
• Wrapping an indexed column in a function, such as WHERE LOWER(email) = ..., which prevents index use unless an expression index exists.

Related

• Query optimization
• Query execution plan
• Primary key

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.