Why must the leftmost column in a composite index appear in the query for the index to be usable?

Why must the leftmost column in a composite index appear in the query for the index to be usable?

• A. The leftmost column must appear because B-trees sort keys by the first column then the second.
• B. Because leftmost column is the only indexed column.
• C. Because the order has no effect on usability.
• D. Because the leftmost column stores all possible values.

Answer: (A)

When a composite index covers several columns, the index is organized as a sequence of keys in a specific order: first by the leftmost column, then by the next, and so on. Because of this structure, the database traverses the index by using the value of the leftmost column to locate the relevant portion of the tree. If you don’t constrain that leftmost column in your query, there’s no efficient way to narrow down to the matching rows, so the index can’t be used effectively to speed up the lookup. For example, an index on (A, B) can quickly find all rows with a given A and then apply a B condition within that subset. But a condition on B alone provides no practical starting point within the index, so it won’t be usable as efficiently. The other choices don’t fit because the index can include multiple columns (not just the leftmost one), the order of the columns does affect usability (leftmost prefixes are required for efficient use), and the leftmost column doesn’t merely store all possible values—it contains actual values from the indexed rows.

When a composite index covers several columns, the index is organized as a sequence of keys in a specific order: first by the leftmost column, then by the next, and so on. Because of this structure, the database traverses the index by using the value of the leftmost column to locate the relevant portion of the tree. If you don’t constrain that leftmost column in your query, there’s no efficient way to narrow down to the matching rows, so the index can’t be used effectively to speed up the lookup.

For example, an index on (A, B) can quickly find all rows with a given A and then apply a B condition within that subset. But a condition on B alone provides no practical starting point within the index, so it won’t be usable as efficiently.

The other choices don’t fit because the index can include multiple columns (not just the leftmost one), the order of the columns does affect usability (leftmost prefixes are required for efficient use), and the leftmost column doesn’t merely store all possible values—it contains actual values from the indexed rows.