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

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 indexes and slow queries in MySQL.

Case Analysis

To illustrate, a test table is created:

CREATE TABLE `T`(
  `id` int(11) NOT NULL,
  `a` int(11) DEFAUT NULL,
  PRIMARY KEY(`id`),
  KEY `a`(`a`)
) ENGINE=InnoDB;

The table has a primary key on id and a regular index on a. MySQL determines whether a statement is slow by comparing its execution time to the long_query_time system variable (default 10 seconds, often set to 1 second in production). The presence of an index can be seen in the EXPLAIN output when the KEY column is not NULL.

Examples show that EXPLAIN SELECT * FROM t; yields KEY=NULL (full table scan), while EXPLAIN SELECT * FROM t WHERE id=2; shows KEY=PRIMARY (primary‑key index), and EXPLAIN SELECT a FROM t; shows KEY=a (regular index). However, the last query still scans the entire index tree, which can be slow on large tables.

Drawbacks of Full Index Scan

In InnoDB, all data is stored in the primary‑key index tree. Even a simple range query like SELECT * FROM t WHERE id>0 may be reported as using the primary key, but it actually performs a full scan of the primary‑key index.

Thus, KEY NOT NULL does not always mean the query avoids a full scan; only a scan that starts from the leftmost leaf of the primary‑key index and proceeds rightward counts as a true full‑table scan.

You can describe a query that traverses the entire primary‑key index as a full table scan .

You can describe a query that traverses the entire secondary index as a full index scan .

A query like SELECT * FROM t WHERE id=2 truly uses the index’s fast lookup capability.

Index Selectivity Must Be Sufficient

Even with an index, a query can be slow if the index’s filtering power is weak. For example, selecting rows where age BETWEEN 10 AND 15 on a table of 1.4 billion people may still scan over 100 million rows despite the index.

The execution flow involves searching the index tree to find the first matching row, then repeatedly scanning the index and fetching full rows via the primary key. The key metric is the number of rows scanned, not merely whether an index is used.

Cost of Back‑to‑Table Lookups

When a composite index does not fully satisfy the query predicates, MySQL must perform “back‑to‑table” lookups (row lookups) for each matching index entry. This can be extremely costly, especially for patterns like WHERE name LIKE '张%' AND age=8, which may require millions of lookups.

MySQL 5.6 introduced Index Condition Push‑Down (ICP) to filter rows while scanning the index, reducing the number of back‑to‑table operations.

Virtual Columns

To further optimize, MySQL 5.7 allows virtual columns. By adding a generated column that stores the first character of name and creating a composite index on that column and age, the query can be rewritten as:

SELECT * FROM t_people WHERE name_first='张' AND age=8;

The table alteration uses a virtual column:

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

Creating the table with the virtual column looks like:

CREATE TABLE `t_people`(
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAUT NULL,
  `name_first` varchar(2) GENERATED ALWAYS AS (LEFT(`name`,1)) VIRTUAL,
  KEY `name_first`(`name_first`,'age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This tighter composite index reduces the scanned rows and back‑to‑table operations dramatically.

Summary

The article introduced the basic structure of indexes and common optimization ideas. It showed that using an index does not automatically avoid slow queries; the real goal is to minimize the number of scanned rows. Typical causes of slow queries include full table scans, full index scans, poor index selectivity, and excessive back‑to‑table lookups.

Reflection

Consider how to count the number of people aged 10‑15 in a 1.4 billion‑row table without adding a filtering factor, and how to provide real‑time OLTP statistics for such a query.