Mastering MySQL Index Usage: When and How Queries Leverage Indexes

When optimizing MySQL queries, understanding how the optimizer decides to use indexes is crucial. This article builds a model to answer three common questions: which range conditions stop index matching, how LIKE fuzzy matches use indexes, and when indexes can support ORDER BY sorting.

Key Length (key_len) Basics

The key_len column in EXPLAIN shows how many bytes of an index are used. Its size is calculated as follows:

Fixed-length column types contribute their byte size (e.g., INT = 4 bytes, BIGINT = 8 bytes).

String columns add bytes for character set (e.g., CHAR(30) UTF8 → at least 90 bytes).

Nullable columns add 1 byte.

Variable-length columns (VARCHAR, TEXT, BLOB) add 2 bytes.

Three Parts of Index Utilization

The model divides index usage into:

Index Key : Determines the scan range (first/last key) based on the leftmost prefix and comparison operators.

Index Filter : Columns present in the index but not used to define the range; they filter rows after the range is identified.

Table Filter : Rows that cannot be filtered by the index and require a table (row) lookup.

Index Key Determination

MySQL uses =, >=, and > to set the lower bound (first key) and =, <=, < for the upper bound (last key), following the leftmost‑prefix rule. Matching stops when a column lacks an = or a range operator that can extend the bound.

exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1
--> first key (c1,c2)
   c1 uses '>=' → add to lower bound, continue
   c2 uses '>'  → add to lower bound, stop

Index Filter

Columns that appear in the index but are not part of the range become index filters. Example:

exp: idx_c1_c2_c3 where c1>=1 and c2<=2 and c3=1
index key  → c1
index filter → c2, c3

Table Filter

If a row cannot be filtered by the index, MySQL fetches it from the table and applies the remaining predicates at the server layer.

Handling BETWEEN and LIKE

BETWEEN is equivalent to a range with inclusive bounds:

where c1 between 'a' and 'b'
--> where c1>='a' and c1<='b'

LIKE can use an index only when the pattern does not start with a wildcard, adhering to the leftmost‑prefix rule:

where c1 like 'a%'
--> where c1>='a' and c1<'b'

A pattern like '%a' cannot use the index.

Index‑Based Sorting

When ORDER BY can be satisfied by an index, MySQL can stop scanning as soon as the required number of rows (LIMIT) is produced, avoiding a full sort. The ordering is guaranteed only if preceding columns in the index have a single value.

Examples of ordering rules for an index idx_c1_c2_c3: c1=3 → c2 ordered, c3 unordered. c1=3 AND c2=2 → c3 ordered. c1 IN (1,2) → c2 and c3 unordered.

Conclusion

The article provides a systematic way to determine whether MySQL can use an index for range scans, LIKE/BETWEEN predicates, and ORDER BY sorting, helping developers write queries that fully exploit index capabilities and avoid costly table filters.