Master MySQL Indexes: When and How They’re Used for Queries

1. Introduction

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

Typical questions include:

Which range conditions stop MySQL from matching further index parts?

How does MySQL use indexes with LIKE fuzzy matching?

Under what circumstances can MySQL use an index for sorting?

We will answer these questions with a unified model so you no longer fear MySQL indexes.

2. Knowledge Supplement

key_len

The

key_len

column in an EXPLAIN plan shows how many bytes of an index are used for the current query, helping you judge how many columns of a composite index are selected.

Calculation rules for

key_len

:

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

For string types, character set matters (e.g., CHAR(30) UTF8 → at least 90 bytes).

If the column allows NULL, add 1 byte.

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

3. Which Conditions Can Use an Index

Based on a diagram (see image below), the process is divided into three parts:

Index Key

The Index Key determines the scan range (first key for the lower bound, last key for the upper bound) using the leftmost‑most principle.

MySQL uses

=

,

>=

,

>

to set the lower bound and

=

,

<=

,

<

for the upper bound.

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

The upper bound works similarly with

=

,

<=

,

<

:

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

Note: If a comparison operator contains '=', the index part can be used; otherwise ( > or < without '=') the following index columns cannot be matched. Upper and lower bounds cannot be mixed; the side with more usable keys determines how many index columns are actually used.

Index Filter

Fields that appear in the index but cannot define the scan range become index filters.

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

Same rule applies: only columns with an '=' (or >=, <=) can be part of the key; others become filters.

Table Filter

If an index cannot filter rows, MySQL falls back to a table filter: rows are fetched from the storage engine and filtered in the server layer.

4. Handling BETWEEN and LIKE

BETWEEN

The condition

where c1 between 'a' and 'b'

is equivalent to

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

and is processed by the same index‑range logic.

LIKE

If the wildcard

%

appears at the leftmost position, e.g.,

where c1 like '%a'

, the index cannot be used because it violates the left‑most‑prefix rule.

When the pattern starts with a constant, e.g.,

where c1 like 'a%'

, it is equivalent to

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

and can use the index.

5. Index‑Based Sorting

If MySQL cannot use an index to satisfy ORDER BY, it must sort the entire result set, which becomes increasingly costly as the filtered row count grows. However, when ORDER BY is combined with LIMIT and the index can provide the required order, MySQL can stop scanning as soon as enough rows are produced.

Make sure it uses index – it is very important to have ORDER BY with LIMIT executed without scanning and sorting the full result set, so the index range scan stops as soon as the required number of rows is generated.

Consider a table with an index on (c1,c2,c3):

select c1,c2,c3 from t1;

This performs a full index scan, yielding the same order as

select c1,c2,c3 from t1 order by c1,c2,c3;

Ordering rules:

c1=3 → c2 ordered, c3 unordered c1=3,c2=2 → c3 ordered c1 IN (1,2) → c2 unordered, c3 unordered

Rule of thumb: In an index (c1,c2,c3), c1 is always ordered. c2 is ordered only when c1 has a single distinct value; otherwise it may be unordered. The same applies to c3.

6. Summary

This article presented a model for understanding how MySQL uses indexes in single‑table queries, covering key_len calculation, index key vs. filter vs. table filter, handling of BETWEEN and LIKE, and index‑based sorting. Apply these concepts to diagnose and improve your query performance.