Why Indexes Can Still Cause Slow Queries and How to Optimize Them

Case Analysis

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.

MySQL marks a query as slow when its execution time exceeds the long_query_time setting (default 10 s, often lowered to 1 s in production). Whether a query uses an index can be seen with EXPLAIN; a non‑NULL KEY column indicates some index usage.

Examples: EXPLAIN SELECT * FROM t; The KEY result is NULL (full table scan).

Running EXPLAIN SELECT * FROM t WHERE id=2; shows PRIMARY, meaning the primary‑key index is used.

Running EXPLAIN SELECT a FROM t; shows a, indicating the secondary index is used, but the query still scans the entire index tree.

Thus, using an index does not guarantee a fast query; the execution time determines whether a query appears in the slow‑query log.

Drawbacks of Full Index Scan

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

Therefore, “no index used” only occurs when the optimizer scans the entire primary‑key leaf‑node range.

You can describe a full table scan as traversing the whole primary‑key index tree.

A full index scan means scanning the entire secondary index (e.g., SELECT a FROM t).

Queries like SELECT * FROM t WHERE id=2 truly benefit from index‑based point lookups.

Index Selectivity Must Be Sufficient

Even with an index, a query can be slow if the filter is not selective. For example, searching a table of 1.4 billion people for ages 10‑15 scans over 100 million rows despite an age index.

The execution flow is: use the age index to locate the first matching row, fetch the primary key, then repeatedly scan the age index and fetch full rows until the upper bound is reached. The number of scanned rows, not just index usage, determines performance.

For large tables, an index must have high filtering power (selectivity).

Cost of Table Lookups (Back‑Table)

Consider a query SELECT * FROM t_people WHERE name='张三' AND age=8 with a composite index on (name, age). The index quickly finds matching rows, but each match still requires a lookup to the primary‑key row (back‑table), which can be costly if many rows match.

When the query uses a prefix pattern, e.g., SELECT * FROM t_people WHERE name LIKE '张%' AND age=8, MySQL 5.5 scans the composite index, performs a back‑table lookup for each candidate, and then filters by age. This can involve millions of back‑table operations.

MySQL 5.6 introduced Index Condition Pushdown (ICP), which pushes the age=8 condition down to the index scan, reducing back‑table calls dramatically.

Virtual Columns

To avoid the left‑most‑prefix limitation, a virtual column can be added. For example, adding name_first that stores the first character of name and creating a composite index on (name_first, age):

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

This allows queries like SELECT * FROM t_people WHERE name_first='张' AND age=8 to use a compact index and dramatically reduce both index scans and back‑table lookups.

CREATE TABLE `t_people`( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `name_first` varchar(2) GENERATED ALWAYS AS (LEFT(`name`,1)) VIRTUAL, KEY `name_first`(`name_first`,`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Conclusion

The article introduced the structure of indexes and basic optimization ideas. It showed that an indexed query can still be a slow query, and that optimization often means reducing the number of scanned rows.

Typical causes of slow queries include:

Full table scans

Full index scans

Poor index selectivity

Frequent back‑table lookups