What is a composite index?

What is a composite index?

• A. An index on a single column
• B. An index on two or more columns
• C. An index used only for unique values
• D. An index that sorts results without filtering

Answer: (B)

A composite index is an index that covers two or more columns, creating a single data structure that can speed up lookups and range queries that involve those columns. The order of the columns matters, because the index can efficiently support predicates on the leading columns (for example, filtering on the first column, or on the first and second). It’s different from a single-column index, which only helps with that one column. It’s also not required to be unique, even though a composite index can be unique if defined that way. While an index can help with sorting if the query’s ORDER BY matches the index order, its primary purpose is fast filtering based on the combination of columns. Therefore, the best description is an index on two or more columns.

A composite index is an index that covers two or more columns, creating a single data structure that can speed up lookups and range queries that involve those columns. The order of the columns matters, because the index can efficiently support predicates on the leading columns (for example, filtering on the first column, or on the first and second). It’s different from a single-column index, which only helps with that one column. It’s also not required to be unique, even though a composite index can be unique if defined that way. While an index can help with sorting if the query’s ORDER BY matches the index order, its primary purpose is fast filtering based on the combination of columns.

Therefore, the best description is an index on two or more columns.