Understanding Index Usage and Slow Queries in MySQL

Many developers wonder why a SQL statement that uses an index can still appear in the slow‑query log; this article explores the relationship between index usage and slow queries.

Case Analysis

A test 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 determines a slow query by comparing execution time with the long_query_time system variable (default 10 s, often set to 1 s in production). Whether a statement uses an index can be seen with EXPLAIN ; a non‑NULL KEY column indicates some index usage.

Examples:

EXPLAIN SELECT * FROM t; → KEY is NULL (full table scan).

EXPLAIN SELECT * FROM t WHERE id=2; → KEY is PRIMARY (primary‑key index).

EXPLAIN SELECT a FROM t; → KEY is a (secondary index), but the query still scans the entire secondary index tree.

Even when KEY is not NULL, the optimizer may perform a full index scan, which can be slow for large tables.

Full Index Scan Drawbacks

In InnoDB, all data is stored in the primary‑key index tree; secondary indexes contain only the indexed columns plus the primary key. A query like SELECT * FROM t WHERE id>0 shows KEY as PRIMARY , but it actually performs a full scan of the primary‑key tree.

Index Selectivity Must Be Sufficient

Even with an index, a query that returns millions of rows (e.g., SELECT * FROM t_people WHERE age BETWEEN 10 AND 15 ) can be slow because the index does not filter enough rows.

Cost of Back‑to‑Table (Row Lookup)

When a composite index does not cover all needed columns, MySQL must fetch the full row from the primary‑key index for each matching entry, which can be expensive if many rows match.

Index Condition Pushdown (ICP)

MySQL 5.6 introduced ICP, which pushes additional predicates (e.g., age=8 ) down to the index scan, reducing the number of back‑to‑table lookups.

Virtual Columns

MySQL 5.7 allows creation of generated (virtual) columns. Example to improve a query on names starting with “张” and age = 8:

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

Now the query SELECT * FROM t_people WHERE name_first='张' AND age=8 can use a compact covering index, dramatically reducing scans and back‑to‑table operations.

Summary

The article introduced the structure of indexes, explained why using an index does not guarantee a fast query, and presented optimization ideas such as improving index selectivity, minimizing back‑to‑table lookups, using index condition pushdown, and leveraging virtual columns.

Typical causes of slow queries include full table scans, full index scans, poor index selectivity, and frequent back‑to‑table operations.

Thought

When a business requires counting billions of rows (e.g., ages 10‑15) without additional filters, consider alternative strategies such as pre‑aggregated tables or OLAP solutions, because pure OLTP scans will remain costly.