Understanding Index Usage and Slow Queries in MySQL

Understanding Index Usage and Slow Queries in MySQL

Many developers wonder why a query 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 NOT NULL, `a` int DEFAULT NULL, PRIMARY KEY(`id`), KEY `a`(`a`) ) ENGINE=InnoDB;

with a primary key and a secondary index, and then examines how MySQL decides whether a statement is slow.

The execution time of a statement is compared against the long_query_time system variable (default 10 s, often set to 1 s in production). Whether an index is used is determined by the EXPLAIN output: a non‑NULL KEY column indicates that some index was consulted.

Examples show three queries: EXPLAIN SELECT * FROM t; – KEY is NULL (full table scan). EXPLAIN SELECT * FROM t WHERE id=2; – KEY is PRIMARY (uses primary‑key index). EXPLAIN SELECT a FROM t; – KEY is a (uses secondary index), but the query still scans the entire secondary‑index tree.

Even when an index is used, the query can be slow if it scans many rows, e.g., a table with one million rows where a secondary‑index scan touches the whole index.

The article clarifies that “using an index” does not always mean a selective lookup; sometimes the optimizer reports a non‑NULL KEY while the operation is effectively a full‑index scan. The term full‑index scan is introduced to describe scanning the entire primary‑key or secondary‑index tree.

Next, the importance of index selectivity is discussed. For a massive table of 1.4 billion people, a query like SELECT * FROM t_people WHERE age BETWEEN 10 AND 15; may use an index on age but still scan over 100 million rows, making it slow. The article emphasizes that the number of rows scanned, not merely the presence of an index, determines performance.

When a composite index is used (e.g., (name, age)), the optimizer may need to perform many “back‑table” lookups (retrieving the full row from the primary‑key index). This back‑table cost can dominate execution time, especially for patterns like WHERE name LIKE '张%' AND age=8, where millions of rows match the prefix.

MySQL 5.6’s Index Condition Pushdown (ICP) reduces back‑table calls by evaluating additional predicates while scanning the secondary index. The article illustrates the ICP flow with diagrams.

To further improve selectivity, the article introduces virtual columns (MySQL 5.7). By adding a generated column name_first that stores the first character of name and creating a composite index (name_first, age), the query SELECT * FROM t_people WHERE name_first='张' AND age=8; can avoid scanning the large name prefix and dramatically reduce back‑table operations.

Finally, the article summarizes the main causes of slow queries: full table scans, full index scans, low index selectivity, and frequent back‑table lookups, and poses thought‑provoking questions about handling massive aggregations without additional filters.