What is a composite index?

A composite index (also called a multi-column index) is an index on two or more columns. Example: CREATE INDEX idx_name_dept ON employees(last_name, dept_id). The order of columns matters: the index is most effective when queries filter on the leading column(s) first. A query filtering only on dept_id cannot use this index efficiently, but a query filtering on last_name alone or last_name + dept_id together can. This is called the leftmost prefix rule. Composite indexes are ideal for queries that frequently filter or sort by multiple columns together.