Composite index column order for multi-column filters

WHAT THIS TESTS The interviewer checks whether you understand multi-column indexes and the leftmost-prefix principle that governs how they are used.

A GOOD ANSWER COVERS The right choice is a single composite index on (last_name, first_name), not two separate single-column indexes. A composite index is sorted first by last_name, then by first_name within each last_name. The leftmost-prefix rule means the engine can use this index for queries filtering on last_name alone, or on last_name and first_name together, because those match a prefix of the index's column order. It cannot efficiently use this index for a query on first_name alone, since first_name is not a leading column. Column order therefore matters: place the column that is always present in the filter, or the more selective one, first. For this exact equality-on-both query, either order works for the lookup, but the leftmost-prefix behavior dictates which partial queries the index can also serve.

COMMON WRONG ANSWERS Creating two separate indexes on last_name and first_name; the engine usually uses only one per table access, missing the combined benefit of a composite index. Assuming column order is irrelevant. Believing the composite index helps a first_name-only query, which it does not.

LIKELY FOLLOW-UPS What is the leftmost-prefix rule precisely? How does index-merge differ from a composite index? When does selectivity change the ideal order? What is a covering index for SELECT *?

ONE CONCRETE EXAMPLE With an index on (last_name, first_name), the query for last_name Smith and first_name John jumps directly to the Smith range and then to John within it. A separate query for everyone named Smith also uses this index, but a query for first_name John alone falls back to a scan.