Why Using Indexes Doesn’t Guarantee Fast Queries: A MySQL Slow‑Query Deep Dive

Case Study

We create a simple InnoDB table

CREATE TABLE `T` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `a`(`a`) ) ENGINE=InnoDB;

where id is the primary‑key index and a is a secondary index. MySQL marks a query as a slow query when its execution time exceeds the long_query_time system variable (default 10 s, commonly set to 1 s in production).

Using EXPLAIN we see the KEY column: EXPLAIN SELECT * FROM t; → KEY is NULL (no index used). EXPLAIN SELECT * FROM t WHERE id=2; → KEY is PRIMARY (primary‑key index used). EXPLAIN SELECT a FROM t; → KEY is a (secondary index used).

Even though the last two queries show a non‑NULL KEY , the second query scans only a few rows, while the third scans the entire secondary‑index tree. With a million rows, the third query can become slow, and under high CPU load it may exceed long_query_time and appear in the slow‑query log. Conclusion: Using an index only describes the execution path; whether a query becomes a slow query is determined by its total execution time, which can be affected by many external factors.

Full Index Scan Drawbacks

In InnoDB, all data are stored in the primary‑key index tree. Therefore, even a query that appears to use an index may actually scan the whole index tree. The only case where an InnoDB query truly does not use an index is when it starts from the leftmost leaf of the primary‑key index and scans rightward across the entire tree.

Full‑table scan = scanning the whole primary‑key index tree.

Full‑index scan = scanning the whole secondary‑index tree (e.g., SELECT a FROM t;).

Using an index for point lookup (e.g., SELECT * FROM t WHERE id=2;) reduces scanned rows dramatically.

Index Selectivity Must Be Sufficient

Consider a table t_people that stores basic information for 1.4 billion people. A query to find all rows where age BETWEEN 10 AND 15 would use an index on age , but if more than 100 million rows satisfy the condition, the index still scans a huge range and the query remains slow. The execution flow for such a query is:

Search the age index tree to locate the first row with age=10 and obtain its primary‑key id.

Use the primary‑key index to fetch the full row.

Continue scanning the age index to the right, repeating steps 1‑2 until age>15 is encountered.

Thus, even though an index is used, the number of scanned rows determines performance.

Cost of Back‑Table Lookups

When a composite index on name and age exists, a query such as SELECT * FROM t_people WHERE name LIKE '张%' AND age=8; must first locate matching entries in the composite index, then retrieve the full rows from the primary‑key index (the “back‑table” step). In MySQL 5.5 the optimizer may need to perform millions of back‑table lookups (e.g., 80 million for the name prefix ‘张’), which dominates execution time.

Index Condition Pushdown (ICP) Optimization

MySQL 5.6 introduced ICP, which pushes the age=8 condition down to the index traversal phase. The optimizer now evaluates the condition while scanning the composite index, only performing a back‑table lookup when the condition is satisfied, reducing back‑table calls from tens of millions to the number of rows that actually match both predicates.

Virtual Columns for Better Indexes

MySQL 5.7 allows virtual columns that are computed from other columns. By adding a virtual column name_first that stores the first character of name , we can create a more selective composite index (name_first, age) :

ALTER TABLE t_people ADD name_first VARCHAR(2) GENERATED ALWAYS AS (LEFT(name,1)) VIRTUAL, ADD INDEX(name_first, age);

The new index lets us rewrite the previous query as SELECT * FROM t_people WHERE name_first='张' AND age=8; , which scans only the 1 million rows that satisfy both conditions and performs 1 million back‑table lookups, dramatically improving performance.

Summary

The article shows that merely having an index does not guarantee fast queries. Effective optimization focuses on reducing the number of scanned rows, improving index selectivity, minimizing costly back‑table lookups, and leveraging MySQL features such as index condition pushdown and virtual columns.

Common Slow‑Query Causes

Full‑table scan

Full‑index scan

Poor index selectivity

Frequent back‑table lookups