Understanding Index Usage and Slow Queries in MySQL

Case Analysis

To illustrate the problem, a sample table CREATE TABLE `T` ( `id` int NOT NULL, `a` int DEFAULT NULL, PRIMARY KEY(`id`), KEY `a`(`a`) ) ENGINE=InnoDB; is created with a primary key on id and a secondary index on a .

MySQL determines a slow query by comparing the execution time with the long_query_time system variable (default 10 s, often set to 1 s in production). Whether an index is used can be seen in the EXPLAIN output: a non‑NULL KEY indicates an index was used.

Examples show that EXPLAIN SELECT * FROM t; returns NULL (no index), while EXPLAIN SELECT * FROM t WHERE id=2; returns PRIMARY (primary‑key index) and EXPLAIN SELECT a FROM t; returns a (secondary index). However, the latter still scans the entire secondary index tree.

Full‑Index Scan Drawbacks

In InnoDB, all data is stored in the primary‑key index tree. Even a query like SELECT * FROM t WHERE id>0 is considered to use the primary index, but it actually performs a full scan of the primary‑key leaf nodes.

Thus, "not using an index" is not an accurate description; the real issue is whether the optimizer can limit the number of scanned rows.

Index Selectivity Must Be Sufficient

Even with an index, a query such as SELECT * FROM t_people WHERE age BETWEEN 10 AND 15 can be slow if the condition matches a large fraction of rows (e.g., over 100 million rows in a 1‑billion‑row table).

The execution involves searching the age index to locate the first matching row, then scanning rightward in the index while performing a primary‑key lookup for each matching row.

Therefore, high selectivity (good filtering) of an index is essential for performance.

Cost of Back‑Table Lookups

When a composite index (e.g., (name, age) ) is used with a condition like WHERE name LIKE '张%' AND age=8 , MySQL may need to perform a back‑table lookup for each matching index entry, which can be costly if many rows satisfy the prefix condition.

MySQL 5.6 introduced Index Condition Pushdown (ICP) to evaluate additional predicates while scanning the index, reducing the number of back‑table lookups.

Virtual Columns

To avoid the left‑most‑prefix limitation, a virtual column can be added: ALTER TABLE t_people ADD name_first VARCHAR(2) GENERATED ALWAYS AS (LEFT(name,1)) VIRTUAL, ADD INDEX(name_first, age);

This creates a compact composite index on name_first and age , allowing queries such as SELECT * FROM t_people WHERE name_first='张' AND age=8 to scan far fewer index entries and perform far fewer back‑table lookups.

Summary

The article introduces the structure of indexes and common reasons why indexed queries can still be slow: full table scans, full index scans, low index selectivity, and frequent back‑table lookups. Understanding and improving index selectivity, using ICP, and leveraging virtual columns are key techniques to reduce scanned rows and accelerate MySQL queries.