Composite Index

A composite index (also called a concatenated or multi-column index) is a single index built on two or more columns together, rather than one index per column.

Column order matters: a composite index on (department_id, hire_date) can serve queries filtering on department_id alone, or on both columns together, but not on hire_date alone — that's the leftmost-prefix rule. As a rule of thumb, put equality-filtered columns before range-filtered columns in the column order.

Composite indexes are usually more efficient than several single-column indexes when queries commonly filter on the same combination of columns together.

CREATE INDEX idx_dept_hire
  ON employees (department_id, hire_date);