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

Introduction

When optimizing SQL in MySQL, many developers are confused about when MySQL can actually use an index. Common questions include which range conditions stop index matching, how LIKE fuzzy matching utilizes indexes, and under what circumstances indexes can be used for sorting. This article presents a model that answers these questions step by step.

Knowledge Supplement

key_len

The

key_len

column in an EXPLAIN plan shows how many bytes of an index are used for a query, helping to determine how many columns of a composite index are selected.

Calculation rules:

Generally,

key_len

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

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

If a column allows NULL, add 1 byte.

For variable‑length types such as VARCHAR (or partial indexes on TEXT/BLOB), add 2 bytes.

Which Conditions Can Use an Index

Thanks to a helpful diagram (see image below), the index usage model is divided into three parts:

Index Key : Determines the data range MySQL scans, reflected in the key length.

Index Filter : Columns that appear in the index but cannot define the scan range; they are used only for filtering.

Table Filter : Rows that MySQL must fetch and filter at the server layer because the index cannot filter them.

Index Key

The Index Key defines the lower and upper bounds of the scan range using the left‑most principle.

Lower bound (first key) is built from conditions using

=

,

>=

, or

>

. MySQL checks the first indexed column in the WHERE clause; if it appears with

=

or

>=

, the lower bound is set and the next column is examined. If a column appears with

>

, it is added to the lower bound and matching stops.

<code>exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1 --&gt; first key (c1,c2) --&gt; c1 uses '&gt;=' (add lower bound), continue; c2 uses '&gt;' (add lower bound), stop.</code>

Upper bound (last key) follows a similar logic with

=

,

<=

, or

<

. If a column uses

<=

or

=

, it is added to the upper bound; if

<

appears, it is added and matching stops.

<code>exp: idx_c1_c2_c3(c1,c2,c3) where c1&lt;=1 and c2=2 and c3&lt;3 --&gt; last key (c1,c2,c3) --&gt; c1 '&lt;=' (add upper bound), continue; c2 '=' (add upper bound), continue; c3 '&lt;' (add upper bound), stop.</code>

Note: If a comparison operator contains “=”, the index key can be used; otherwise ( > or < without “=”), subsequent 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

Index Filter columns are present in the index but do not contribute to defining the scan range; they are only used for filtering after the range is determined.

<code>exp: idx_c1_c2_c3 where c1>=1 and c2&lt;=2 and c3=1 --&gt; index key: c1 --&gt; index filter: c2, c3</code>

Same rule applies: only columns with “=” (or “>=”/“<=”) can be part of the index key; others become index filters.

Table Filter

If MySQL cannot use the index to filter rows, it performs a table filter: the engine returns rows to the server layer, where additional filtering occurs.

Handling BETWEEN and LIKE

BETWEEN

The condition

WHERE c1 BETWEEN 'a' AND 'b'

is equivalent to

WHERE c1 >= 'a' AND c1 <= 'b'

, allowing the same index‑range logic to apply.

LIKE

For a pattern like

WHERE c1 LIKE '%a'

, the leading wildcard prevents index usage because the left‑most prefix rule is violated.

Conversely,

WHERE c1 LIKE 'a%'

can be transformed to

WHERE c1 >= 'a' AND c1 < 'b'

, enabling index usage.

Index Sorting

If MySQL cannot use an index to satisfy ORDER BY, sorting cost grows with data volume, even when LIMIT is present. MySQL can optimize LIMIT when the order can be satisfied by an index range scan, stopping execution 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; the index range scan will stop as soon as the required number of rows is generated.

Consider a table with an index on (c1,c2,c3). A full index scan (e.g.,

SELECT c1,c2,c3 FROM t1;

) yields the same result as a full table scan with ORDER BY (

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 is uniquely determined; otherwise c2 may be unordered. The same applies to c3.

Conclusion

This article presented a model for understanding how MySQL uses indexes in single‑table queries, covering range conditions, LIKE patterns, and sorting. Readers should now have a clearer picture of when and how MySQL can leverage indexes.