Master MySQL Index Usage: When and How Indexes Improve Queries

Introduction

When optimizing SQL in MySQL, many developers are confused about when the optimizer can use an index.

Which range conditions stop MySQL from matching further index columns?

How does MySQL use an index for LIKE fuzzy matching?

Under what circumstances can MySQL use an index for sorting?

This article answers each question with a clear model, so you no longer fear MySQL index usage.

Key Concepts

key_len is a column in the EXPLAIN output that shows how many bytes of an index are used for a query. It helps determine how many columns of a composite index are actually applied.

Generally, key_len equals the byte length of the column type (e.g., INT = 4 bytes, BIGINT = 8 bytes).

For string types, character set matters; CHAR(30) UTF8 requires at least 90 bytes.

If the column allows NULL, add 1 byte.

Variable‑length types ( VARCHAR, TEXT, BLOB) add 2 bytes.

Which Conditions Can Use an Index

The following diagram (illustrated below) splits the index usage process into three parts:

1. Index Key : Determines the scan range (lower and upper bounds).

2. Index Filter : Columns that exist in the index but cannot define the range.

3. Table Filter : Rows that must be filtered after the index scan.

Index Key

The lower bound (first key) is built from conditions using =, >=, or > following the left‑most principle. The upper bound (last key) uses =, <=, or <. Matching stops when a column does not satisfy the required operator.

exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1 --> first key (c1,c2) --> c1 uses ">=", continue, c2 uses ">", stop matching

Upper‑bound example:

exp: idx_c1_c2_c3(c1,c2,c3) where c1<=1 and c2=2 and c3<3 --> last key (c1,c2,c3) --> c1 uses "<=", continue, c2 uses "=", continue, c3 uses "<", stop matching

Note: If an operator contains "=", the index can continue to be used; operators without "=" (only ">" or "<") stop further matching. Upper and lower bounds cannot be mixed.

Index Filter

Columns that appear in the index but are not part of the range definition act as filters after the index key determines the scan range.

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

Table Filter

If the engine cannot filter rows using the index, the matching rows are returned to the server layer, where a table filter performs the remaining conditions.

Handling BETWEEN and LIKE

BETWEEN

A BETWEEN 'a' AND 'b' clause is equivalent to c1 >= 'a' AND c1 <= 'b', so it is transformed into lower and upper bounds in the same way as range conditions.

LIKE

The leading wildcard prevents index use because it violates the left‑most prefix rule. A pattern like c1 LIKE 'a%' can be rewritten as c1 >= 'a' AND c1 < 'b', allowing the index to be used.

Index Sorting

If MySQL cannot use an index to satisfy ORDER BY, it must sort the entire result set, which becomes increasingly expensive as data volume grows. When LIMIT is present, MySQL can stop the scan once enough rows are retrieved if an appropriate index is used.

Consider a table with columns c1, c2, c3 and a composite index on them. The ordering rules are:

If c1 = 3, c2 is ordered, c3 is not.

If c1 = 3 and c2 = 2, c3 becomes ordered.

If c1 IN (1,2), neither c2 nor c3 is ordered.

In a composite index idx_c1_c2_c3, a column is ordered only when all preceding columns have a single, deterministic value; otherwise the later columns cannot be relied on for ordering.