Understanding When a MySQL Composite Index Is Used: Experiments with ORDER BY and Range Conditions

The example creates an employees table with a composite BTREE index unique_birth_name (first_name, last_name) . Three queries are run with EXPLAIN to observe index usage.

1. SELECT gender FROM employees ORDER BY first_name, last_name shows type: ALL and Extra: Using filesort , indicating the index is not used.

2. Adding a range condition WHERE first_name > 'Leah' still results in a full‑table scan ( type: ALL ) but reduces the rows examined to about half of the table.

3. Changing the range to WHERE first_name > 'Tzvetan' further reduces the rows examined to roughly one‑tenth of the table, and the execution plan now uses the composite index.

The analysis hypothesizes that MySQL decides whether to perform a second lookup using the primary keys returned by the index based on the number of rows matched. When the matched row count is close to the total table size, MySQL prefers a full‑table scan and filesort; when the row count drops below a certain threshold, it uses the index for the second lookup, avoiding the filesort.

Empirical counts confirm this behavior: the first query processes 300,024 rows (the whole table), the second processes 159,149 rows (≈ ½ of the table), and the third processes 36,731 rows (≈ ⅒ of the table). Only the third query triggers index usage.

In summary, MySQL will use a composite index for ordering only when the initial index condition filters enough rows; otherwise it falls back to a full‑table scan with filesort.