Index

An index is a separate data structure (usually a B-tree) that lets the database find rows without scanning the whole table — like the index at the back of a book. It speeds up reads at the cost of slightly slower writes.

Without an index, finding matching rows means a full table scan. An index on the searched column turns that into a fast lookup. The trade-off: every INSERT, UPDATE, or DELETE must also maintain the index, so over-indexing slows writes.

Index the columns you filter, join, and sort on most often — not every column. A composite index on (a, b) can serve queries filtering on a alone, but not on b alone.

Example

CREATE INDEX idx_emp_last_name
  ON employees (last_name);