Database Index

A database index is a data structure, usually a B-tree, that speeds up finding rows by the values of one or more columns, much like the index at the back of a book. Without an index the database may have to scan every row.

Indexes make searches, joins and sorting much faster, but they take extra storage and slow down inserts and updates slightly, because the index must be updated too. Primary keys and unique constraints are indexed automatically; other indexes are added on columns used often in WHERE, JOIN and ORDER BY.

CREATE INDEX idx_orders_customer ON orders (customer_id);
-- speeds up: SELECT * FROM orders WHERE customer_id = 42;