Understanding Index Usage, Slow Queries, and Optimization Techniques in MySQL

Many developers wonder why a SQL statement that uses an index can still appear in the slow‑query log. The article starts by creating a simple table CREATE TABLE `T` (`id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `a`(`a`)) ENGINE=InnoDB; with a primary key on id and a secondary index on a , and then examines how MySQL determines slow queries based on the long_query_time threshold.

Using EXPLAIN SELECT * FROM t; shows KEY as NULL , indicating a full table scan. Queries like EXPLAIN SELECT * FROM t WHERE id=2; and EXPLAIN SELECT a FROM t; show the primary key and the secondary index being used, but the article points out that even when an index is reported, the optimizer may still scan the entire index tree, leading to slow performance on large tables.

The discussion then moves to InnoDB’s clustered index architecture, where all row data resides in the primary‑key index. It illustrates that a query such as SELECT * FROM t WHERE id>0 technically uses the primary index but still performs a full index scan.

Next, the article highlights the importance of index selectivity. For a massive table of 1 billion rows, a query like SELECT * FROM t_people WHERE age BETWEEN 10 AND 15 may use an age index yet still scan hundreds of millions of rows, making it slow.

To improve selectivity, a composite index on name and age is examined. A query SELECT * FROM t_people WHERE name='张三' AND age=8 benefits from the composite index, while a pattern search SELECT * FROM t_people WHERE name LIKE '张%' AND age=8 suffers from many row scans and frequent “back‑table” (回表) operations.

MySQL 5.6’s Index Condition Push‑Down (ICP) optimization is introduced, which pushes the age=8 predicate down to the index scan, reducing the number of back‑table lookups.

For further improvement, the article proposes using a virtual column (MySQL 5.7) to store the first character of name and creating a composite index on this virtual column and age : ALTER TABLE t_people ADD name_first VARCHAR(2) GENERATED (LEFT(name,1)), ADD INDEX(name_first, age); With this index, the query SELECT * FROM t_people WHERE name_first='张' AND age=8 scans only the relevant subset, dramatically reducing both index scans and back‑table operations. The article concludes by summarizing common slow‑query scenarios: full table scans, full index scans, low‑selectivity indexes, and excessive back‑table costs, and poses thought‑provoking questions about handling massive aggregations that cannot be further filtered.