How to Speed Up MySQL Queries with Covering Indexes: A Step‑by‑Step Guide

Problem Statement

A colleague reported a slow MySQL query that selects order_code and order_amount from a table with a unique index on order_code. Although an index exists, the optimizer chooses a full‑table scan, leading to poor performance.

Initial Table and Query

The table t_order has the following structure (irrelevant columns omitted):

CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` char(12) NOT NULL,
  `order_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The table contains 316,977 rows. The query executed is:

select order_code, order_amount from t_order order by order_code limit 1000;

The original execution plan shows a full‑table scan with file sorting, taking about 260 ms, as illustrated below.

Why the Index Was Not Used

The unique index uni_order_code is a secondary index that stores (order_code, id). For each matching row the engine must read the index entry, then perform a random I/O to fetch the full row to obtain order_amount. With a LIMIT 1000, this results in roughly 1,000 random I/Os, which is far slower on mechanical disks than a sequential scan.

Consequently the optimizer correctly prefers a full‑table scan for this workload.

Covering Index Solution

To eliminate the random I/O, a covering (composite) index that includes both selected columns is created:

ALTER TABLE `t_order`
ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

This index stores both order_code and order_amount, allowing the query to be satisfied entirely from the index.

The new execution plan now uses the covering index, scans only the 1,000 rows needed, and the query time drops to about 13 ms, as shown in the following screenshots.

Conclusion

Covering indexes allow MySQL to retrieve all required columns directly from the index, removing the need for row lookups and dramatically reducing query latency. Understanding execution plans and index structures is essential for writing high‑performance SQL.

Source: Mr 船长 my.oschina.net/loujinhe/blog/1528233