Why MySQL Chooses a Composite Index Even When the Query Skips the Leftmost Column

The problem originates from a peer question: a table has a composite index, but the SQL query does not satisfy the leftmost‑prefix rule (it skips column b and uses column c), yet the execution plan shows the composite index being used.

The article aims to teach a diagnostic method for such cases, helping readers without prior knowledge to find clues and understand why the optimizer selects the index.

First, a test database is created, a table is defined, and the execution plan is inspected. The plan surprisingly chooses the composite index, prompting deeper analysis.

Using an optimizer trace (referenced from experiment 27), the trace is examined in a JSON visualizer to locate entries for the index name. The trace reveals that MySQL considers the composite index the optimal covering index and possibly a range index, influencing its selection.

Further trace entries show that, due to cost considerations, MySQL does not treat the composite index as a skip‑scan. The discussion introduces three concepts—covering index, range index, and skip scan—and provides official MySQL documentation links for each.

In conclusion, the composite index is used as a covering index, serving as an alternative to a full table scan for the given SQL. A tip notes that in MySQL 5.7 the optimizer trace may lack this information, while MySQL 8.0 improves optimizer diagnostics, so using the newer version is recommended.