Improving MySQL Query Performance with Covering Indexes

A performance issue is presented where a MySQL query aggregating columns c1 , c2 , and c3 on a 5‑million‑row table takes 55 seconds. The original SQL is:

SELECT c1,
       SUM(CASE WHEN c2=0 THEN 1 ELSE 0 END) as folders,
       SUM(CASE WHEN c2=1 THEN 1 ELSE 0 END) as files,
       SUM(c3)
FROM test
GROUP BY c1;

The table definition includes a primary key id and a composite index idx_test_01(c1, ...) , but c2 and c3 are not covered, causing MySQL to use the index for the lookup and then perform a costly “row‑lookup” (back‑table) for each matching row.

CREATE TABLE test (
  id bigint(20) NOT NULL,
  c1 varchar(64) COLLATE utf8_bin NOT NULL,
  c2 tinyint(4) NOT NULL,
  c3 bigint(20) DEFAULT NULL,
  ...
  PRIMARY KEY(id),
  KEY idx_test_01(c1, ...)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Because the index does not contain all columns needed for the SELECT list, MySQL must read the full rows, leading to high I/O. The article explains the concept of a covering index—an index that contains every column referenced by the query, allowing the engine to satisfy the query using only the index.

By creating a new composite index that includes c1 , c2 , and c3 :

CREATE INDEX idx_test_02 ON test(c1, c2, c3);

the query plan changes to show Using index in the Extra column, indicating that the covering index is used and no row lookups are required. The execution time drops from 55 seconds to roughly 2 seconds.

The article concludes that many SQL performance problems can be solved with fundamental knowledge such as proper index design, emphasizing the importance of understanding theory and applying it in practice.