Why Indexes Still Lead to Slow Queries and How to Optimize Them

Case Study

To illustrate the problem, a simple table

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

is created with a primary key on id and a secondary index on a.

Running EXPLAIN SELECT * FROM t; shows KEY = NULL, indicating no index usage. EXPLAIN SELECT * FROM t WHERE id=2; shows KEY = PRIMARY, and EXPLAIN SELECT a FROM t; shows KEY = a, meaning the index is used.

Even though the second and third queries report a non‑NULL KEY, the third query actually scans the entire secondary index tree.

If the table contains one million rows, the full‑index scan in the third query becomes very slow, especially under high CPU load, and may exceed long_query_time, causing the query to appear in the slow‑query log.

Full Index Scan Drawbacks

In InnoDB, all data is stored in the primary‑key index tree. A query like SELECT * FROM t WHERE id>0 uses the primary key index to locate the first matching row, but then performs a full scan of the primary‑key leaf nodes, effectively a full‑table scan.

Thus, a non‑NULL KEY in EXPLAIN does not guarantee that the optimizer avoids a full scan.

You can describe a query that traverses the entire primary‑key index tree as a full‑table scan.

A query like SELECT a FROM t; performs a full secondary‑index scan.

A query like SELECT * FROM t WHERE id=2; truly uses the index for fast row lookup.

Index Selectivity Must Be Sufficient

Even when an index is used, if its selectivity is low, the optimizer may still scan millions of rows. For example, querying a table of 1.4 billion people for ages 10‑15: SELECT * FROM t_people WHERE age BETWEEN 10 AND 15; The index on age finds the first matching row, then scans the index tree to retrieve each subsequent row, resulting in over 100 million row scans.

Step 1: Use the age index to locate the first matching record and obtain its primary‑key id.

Step 2: Use the primary‑key index to fetch the full row.

Step 3: Continue scanning the age index to the right until age exceeds 15, repeating steps 1‑2.

The key metric is the number of rows scanned, not merely whether an index appears in EXPLAIN.

Back‑Table (Row Lookup) Cost

When a composite index does not cover all needed columns, MySQL must perform a “back‑table” lookup: using the index to obtain the primary‑key id, then fetching the full row from the primary‑key index. In a query like SELECT * FROM t_people WHERE name LIKE '张%' AND age=8;, the optimizer may traverse many index entries and perform millions of back‑table lookups, which dominates execution time.

Index Condition Pushdown (ICP)

MySQL 5.6 introduced ICP, which pushes additional predicates (e.g., age=8) down to the index traversal phase, reducing the number of back‑table lookups. The optimizer now evaluates age while scanning the composite index and only performs a row lookup when the predicate is satisfied.

Virtual Columns for Better Indexes

To avoid the left‑most‑prefix limitation of composite indexes, MySQL 5.7 allows virtual columns. By adding a generated column name_first that stores the first character of name, a new composite index (name_first, age) can be created:

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

Now the query SELECT * FROM t_people WHERE name_first='张' AND age=8; can use this compact index, scanning only the relevant 1 million rows and performing 1 million back‑table lookups instead of tens of millions.

Conclusion

The article demonstrates that using an index does not guarantee fast execution; the real performance factor is the number of rows scanned. Common causes of slow queries include full‑table scans, full‑index scans, low index selectivity, and excessive back‑table lookups. Effective optimization involves designing highly selective indexes, leveraging ICP, and employing virtual columns to create tighter covering indexes.