Table has a composite index on (last_name, first_name). Which query can use it efficiently?
Options
- WHERE first_name = 'Ana'
- WHERE last_name = 'Ruiz' (Correct option)
- WHERE first_name LIKE '%a'
- None of them
Answer
WHERE last_name = 'Ruiz'
Why
A composite index is sorted by the first column, then by the second inside each first value, like a phone book sorted by surname, then first name.
You can quickly find everyone named Ruiz, or Ruiz + Ana. But finding every 'Ana' regardless of surname means scanning the whole book.
This is the leftmost-prefix rule: the index helps only if you filter on its first column(s).
Remember
Composite index works left to right.