How Index Covering and Avoiding Table Lookups Boost MySQL Query Performance

1 Introduction

During MySQL query processing, index covering and avoiding unnecessary table lookups (back‑to‑table) are effective ways to reduce retrieval steps and improve execution efficiency. This article analyzes how to enhance MySQL retrieval from these two perspectives.

2 Data Preparation

We simulate a department table emp with 5 million rows. The table definition is as follows:

CREATE TABLE `emp` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `empno` int unsigned DEFAULT NULL,
  `empname` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  `mgr` int DEFAULT 1,
  `hiredate` datetime DEFAULT NULL,
  `sal` int DEFAULT 0,
  `comn` int DEFAULT 0,
  `depno` int DEFAULT 100,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2345 DEFAULT CHARSET=utf8;

3 Analyzing Table Lookups

3.1 Concept of Table Lookup (Back‑to‑Table)

First, understand two basic concepts: primary (clustered) index and secondary index.

Primary index: data and index are stored together in the leaf nodes of the same B+Tree, typically the primary key.

Secondary index: leaf nodes store only the indexed columns and the primary key value. After locating the primary key, the engine must fetch the full row from the primary index, which requires an additional index traversal—this is the table lookup.

The essence of a table lookup is that the secondary index leaf contains only the indexed column values and the primary key; the engine must use the primary key to retrieve the complete row, incurring an extra index scan.

In the figure, the secondary index on empname yields the primary key id, then a lookup in the primary index retrieves the full record.

3.2 Performance Cost of Table Lookup

Using the secondary index on empname avoids a full table scan, and B+Tree leaf pages are linked for efficient traversal. However, after the secondary index finds the primary key, MySQL must access the primary index again, which may cause random I/O if the row is not cached, leading to noticeable latency.

Therefore, to ensure MySQL efficiency, we should minimize table lookups:

Avoid table lookups whenever possible.

If a lookup is unavoidable, reduce its frequency.

One way to avoid lookups is to have the index contain all required columns—this is called a covering index.

4 Covering Index

4.1 Definition

What is a covering index? A covering index is a non‑clustered index that includes all columns referenced in the query, so the optimizer does not need to perform an additional table lookup. Because all needed data resides in the index, the operation is faster.

In other words, a covering index allows the query to be satisfied using only a single index scan.

For the emp table, if a secondary index stores both empname and job, a query that selects only those columns can be answered without a table lookup.

SELECT id, empname, job FROM emp WHERE empname = "Deny";

This query can be satisfied by a covering index on (empname, job).

4.2 Practical Use

Create the covering index:

CREATE INDEX idx_emp_empname_job ON emp(empname(5), job);

Analyze the execution plan:

EXPLAIN SELECT id, empname, job FROM emp WHERE empname = "Deny";

When the Extra column shows Using index, the query uses a covering index.

Optimization suggestion:

If we query only the id value, the index on empname can return it directly without a second lookup:

SELECT id FROM emp WHERE empname = ?;

However, if we also need hiredate, the covering index cannot be used:

SELECT id, empname, hiredate FROM emp WHERE empname = ?;

This explains why writing explicit column lists instead of SELECT * can enable covering indexes and improve query performance.