Why Index Hits Still Cause Slow Queries and How to Optimize MySQL

The article explains why an index hit does not guarantee fast query performance, shows how to locate and analyze index issues using EXPLAIN and the slow query log, and provides practical optimization strategies for MySQL.

1. Setup: Create table and data

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `address` varchar(128) COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

Insert 10,000 rows into the user table.

@GetMapping("/insert_batch")
public Object insertBatch(@RequestParam("batch") int batch) {
    for (int j = 1; j <= batch; j++) {
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= 100; i++) {
            User user = User.builder()
                .userName("Tom哥-" + ((j - 1) * 100 + i))
                .age(29)
                .address("上海")
                .build();
            userList.add(user);
        }
        userMapper.insertBatch(userList);
    }
    return "success";
}

2. Slow query definition and parameters

MySQL's slow query log records statements whose execution time exceeds the long_query_time threshold.

slow_query_log: enable (1) or disable (0) the slow query log.

log-slow-queries: path for the slow query log in MySQL 5.6 and earlier.

slow-query-log-file: path for the slow query log in MySQL 5.6 and later.

long_query_time: threshold (seconds) for a query to be considered slow.

log_queries_not_using_indexes: optionally log queries that do not use indexes.

By default slow_query_log is OFF; enable it with SET GLOBAL slow_query_log=1; or by setting the variable in my.cnf. The default long_query_time is 10 s, often reduced to 1 s in production.

3. Experiments

Run EXPLAIN on several queries and observe the key column and the number of rows scanned.

EXPLAIN SELECT * FROM user; → key is NULL (full table scan).

EXPLAIN SELECT * FROM user WHERE id=10; → key = PRIMARY (primary‑key index used).

EXPLAIN SELECT user_name FROM user; → key = idx_user_name (secondary index used).

Even when the secondary index is used, the rows column may show a value close to the total row count (e.g., 9968), indicating that MySQL still performs a full scan.

4. Index hit but still slow

Queries such as EXPLAIN SELECT * FROM user WHERE id>0; use the primary index but effectively scan the whole table because the condition matches most rows.

The key insight is that index usage alone is not enough; the number of rows examined ( rows) is a more reliable performance indicator.

5. Optimization: Composite index

Create a composite index on user_name and age to improve filtering.

ALTER TABLE `user` ADD INDEX idx_user_name_age (`user_name`,`age`);

6. Index Condition Pushdown (ICP)

MySQL 5.6 introduced ICP, which evaluates additional conditions (e.g., age=29) while scanning the composite index, reducing the number of costly table‑row lookups (back‑table operations).

7. Virtual column

Define a generated column that stores the prefix of user_name and index it together with age:

ALTER TABLE `user` ADD user_name_first VARCHAR(12) GENERATED ALWAYS AS (LEFT(user_name,6)), ADD INDEX(user_name_first, age);

EXPLAIN on queries that filter by user_name_first and age shows a dramatically lower rows count, confirming the optimization.

8. Conclusion

Slow queries are not solely caused by missing indexes; the number of rows examined matters most. Use EXPLAIN and the slow query log to identify high‑row scans, then apply strategies such as composite indexes, index condition pushdown, and virtual columns to reduce row examinations and improve performance.