Why Indexes Still Lead to Slow Queries: A Deep Dive into MySQL Optimization

Case Analysis

To illustrate, a 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. The explain of SELECT * FROM t; shows KEY=NULL , meaning no index is used. EXPLAIN SELECT * FROM t WHERE id=2; shows KEY=PRIMARY , using the primary‑key index. EXPLAIN SELECT a FROM t; shows KEY=a , using the secondary index, but it still scans the entire a index tree.

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 technically uses the primary‑key index, but the optimizer still performs a full scan of the index leaves, which can be slow for large tables.

Index Selectivity Must Be Sufficient

Even with an index on age, a query SELECT * FROM t_people WHERE age BETWEEN 10 AND 15 may still be slow if it matches over a hundred million rows, because the index does not filter enough rows.

Cost of Back‑Table Lookups

For a query SELECT * FROM t_people WHERE name='张三' AND age=8, a composite index on (name, age) reduces the number of rows scanned. However, a pattern query SELECT * FROM t_people WHERE name LIKE '张%' AND age=8 forces the optimizer to scan many index entries and perform many back‑table lookups (回表), which becomes a performance bottleneck.

MySQL 5.6 introduced Index Condition Push‑Down (ICP) , which evaluates the age=8 condition while scanning the composite index, reducing the number of back‑table accesses.

Virtual Columns

To further optimize, a generated virtual column can be added:

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

This creates a compact index on the first character of name and age, allowing queries like SELECT * FROM t_people WHERE name_first='张' AND age=8 to scan far fewer rows.

Summary

The article introduced the basic structure of indexes and several optimization ideas: using indexes does not automatically avoid slow queries; reducing scanned rows is the key.

Full table scan

Full index scan

Poor index selectivity

Frequent back‑table overhead

Thought

If a business must count the number of people aged 10‑15 in a 1.4‑billion‑row table without adding filtering factors, how can this be handled in an OLTP, real‑time scenario?