Master MySQL Index Usage: When and How Queries Leverage Indexes

Introduction

When optimizing SQL in MySQL, many developers are confused about when the optimizer can use an index. This article answers three typical questions: range conditions, LIKE fuzzy matching, and index‑based ordering.

Key Length (key_len) in EXPLAIN

EXPLAIN shows a column key_len that indicates how many bytes of the chosen index are used. The length is calculated as follows:

Fixed‑length columns contribute their byte size (e.g., INT = 4 bytes, BIGINT = 8 bytes).

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

If the column permits NULL, add 1 byte.

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

Which Conditions Can Use an Index

The model splits index usage into three parts:

Index Key : determines the scan range (the part reflected in key_len).

Index Filter : columns that can be filtered by the index but do not define the range.

Table Filter : rows that must be fetched and filtered by the server after the index scan.

Examples illustrate how MySQL matches the first‑key (lower bound) and last‑key (upper bound) based on comparison operators. The article includes pseudo‑code showing the matching process for queries such as:

idx_c1_c2_c3(c1,c2,c3)
WHERE c1>=1 AND c2>2 AND c3=1
--> first key (c1,c2)

and for upper‑bound matching:

WHERE c1<=1 AND c2=2 AND c3<3
--> last key (c1,c2,c3)

If a comparison contains “=”, the index can continue matching subsequent columns; operators “>” 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 become index filters. Example:

WHERE c1>=1 AND c2<=2 AND c3=1
-- index key → c1
-- index filter → c2, c3

Table Filter

When the engine cannot use the index to filter rows, it returns the rows to the server layer, which then applies a table filter.

Handling BETWEEN and LIKE

BETWEEN is rewritten to two range predicates, e.g., c1 BETWEEN 'a' AND 'b' becomes c1>=‘a’ AND c1<=‘b’, and the same range‑matching logic applies.

For LIKE, the leftmost character must not be a wildcard. A pattern like c1 LIKE 'a%' is equivalent to c1>=‘a’ AND c1<‘b’.

Index‑Based Sorting

If MySQL can satisfy ORDER BY using the index, it avoids a separate sort step, which is especially important for large result sets or queries with LIMIT. The article shows a sample table and index, then demonstrates that a full‑index scan returns rows in index order, effectively the same as SELECT … ORDER BY c1,c2,c3 without an explicit sort.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  `c3` int(11) NOT NULL DEFAULT '0',
  `c4` int(11) NOT NULL DEFAULT '0',
  `c5` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_c1_c2_c3` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Summary

The article presents a practical model for understanding how MySQL decides whether an index can be used for range scanning, filtering, or ordering. By examining key_len, the comparison operators, and the left‑most‑prefix rule, developers can predict index utilization and write more efficient queries.