Why Using Indexes Doesn’t Guarantee Fast Queries – MySQL Index Deep Dive

Case Analysis

Many students ask why a SQL statement that uses an index can still end up 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;

The table has three columns: id as the primary‑key index and a as a secondary index.

MySQL decides whether a statement is a slow query by comparing its execution time with the long_query_time system variable (default 10 seconds, often set to 1 second in production). The EXPLAIN output shows whether an index is used: if the KEY column is NULL, no index is used.

Examples: 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, especially on large tables.

Drawbacks of Full Index Scans

In InnoDB, all data is stored in the primary‑key index tree. A full index scan (scanning the entire primary‑key or secondary‑index tree) is effectively a full table scan and can be very slow on large datasets.

Index Selectivity Must Be Sufficient

Consider a table with 1.4 billion rows storing basic information of every Chinese citizen. A query like SELECT * FROM t_people WHERE age BETWEEN 10 AND 15 will use an index on age, but because the condition matches over 100 million rows, the query remains slow.

The execution flow is:

Search the age index to find the first record with age=10 and obtain its primary‑key id.

Use the primary‑key index to fetch the full row.

Repeat the process until age exceeds 15.

This shows that good selectivity (high discrimination) is essential for index effectiveness.

Cost of Back‑Table Lookups

When a composite index (e.g., on name and age) is used, MySQL first finds matching index entries and then performs a back‑table lookup to retrieve the full row. The back‑table step can dominate execution time, especially if many rows satisfy the index prefix.

MySQL 5.6 introduced Index Condition Push‑Down (ICP) to filter rows while scanning the index, reducing the number of back‑table lookups. The diagram below illustrates the ICP flow:

Virtual Columns

To further improve selectivity, a virtual column can be added that stores the first character of name. The following statement creates the virtual column and a composite index on name_first and age:

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

Now the query SELECT * FROM t_people WHERE name_first='张' AND age=8 can use the new compact index, scanning only the relevant rows and performing far fewer back‑table lookups.

Summary

The article introduced the basic structure of indexes and several fundamental optimization ideas. Using an index does not guarantee a fast query; optimization often means reducing the number of scanned rows.

Full table scan

Full index scan

Poor index selectivity

Frequent back‑table overhead

Thought

What if a business requirement is to count the number of people aged 10‑15 in a 1.4 billion‑row table without adding any filtering factor? How would you handle such a real‑time OLTP statistic?