Systems Design Concepts Practice Test

A composite index is an index built on multiple columns together as a single index structure. The index key is a combination of the values from those columns in a defined order, which lets the database efficiently filter and sort on those columns in one pass.

The order of the columns matters. If your query predicates align with the leading columns of the composite index, the database can perform a range scan or lookup that uses the index to quickly locate matching rows, often avoiding extra lookups and enabling fast sorting by the indexed columns. For example, an index on (country, year) helps queries filtering by country and year and can support ORDER BY year, provided the predicates start with country (and then year). If you filter only on year, the index isn’t as useful.

This is different from a spatial index, which is designed for multi-dimensional spatial queries (like grids for geographic data). It’s also not a single-column index, which only keys on one column. And it isn’t about duplicating every column for redundancy; an index may include additional columns to cover common queries, but it remains a separate data structure built on the indexed columns.