How does the order of columns in the composite index definition affect its efficiency and usage?

The order of columns in a composite index is critically important for its efficiency. An index works best when queries filter or sort data based on the first columns of the index.

For example, if there is an index on (A, B), then queries with conditions on A or on A and B can efficiently use the index. But queries only on B — no, because the index is ordered first by A.

This is because a composite index stores data in the order of the first column, then the second, and so on. Therefore, the correct order is chosen based on the most frequently used filters in queries.

Example:

CREATE INDEX idx_ab ON table_name (A, B);
-- efficient for WHERE A = ?
-- efficient for WHERE A = ? AND B = ?
-- inefficient for WHERE B = ?