How does the composite index (A, B, C) work in queries?

A composite index (A, B, C) in a database is created over multiple columns in a specified order. It is effective for queries that filter or sort data starting from the first column A, then B, and then C.

For example, an index on (A, B, C) will be used efficiently for queries with conditions:

• WHERE A = ...
• WHERE A = ... AND B = ...
• WHERE A = ... AND B = ... AND C = ...

However, a query with a condition only on B or C without A, such as WHERE B = ..., will not be able to use this index efficiently.

This is because the index is structured as a tree, ordered first by A, then within A by B, and within B by C. Therefore, searching by the initial columns of the index is necessary for its use.

SQL example:

-- Uses the (A, B, C) index
SELECT * FROM table WHERE A = 1 AND B = 2;

-- Does not efficiently use the (A, B, C) index
SELECT * FROM table WHERE B = 2;