Mastering MySQL Composite Indexes: When and How They Boost Query Performance

Background

Frequent slow‑SQL incidents prompted an investigation into index optimization. Adding more single‑column indexes can degrade insert performance, so the article explores whether composite (multi‑column) indexes can provide a better solution.

Understanding Composite Indexes

When a WHERE clause involves multiple columns, a composite index can be created on those columns (e.g., province and city) to significantly speed up queries on large tables.

Reduced query overhead: a composite index on (c1,c2,c3) implicitly creates indexes for (c1), (c1,c2), and (c1,c2,c3), cutting the cost for large tables.

Covering index: MySQL can retrieve data directly from the index without a table lookup, reducing random I/O.

Higher efficiency: More indexed columns filter out more rows, improving query speed.

Drawbacks include increased disk space for each additional indexed column and potential write‑performance impact on INSERT/UPDATE/DELETE operations.

Guideline: a single table should generally have no more than one composite index, and a composite index should contain no more than three columns.

Left‑most Matching Principle

The leftmost column of a composite index is matched first. Therefore, place the most frequently filtered column at the leftmost position when defining the index.

B‑tree indexes compare columns in the order they are defined; missing the leftmost column prevents further searching.

When a WHERE clause uses columns c1 and c2, the correct index order is (c1, c2) if c1 has fewer distinct values.

Impact of Field Order in Queries

MySQL’s optimizer reorders conditions to use the index efficiently, so the order of columns in the WHERE clause does not affect which index is chosen.

select * from t_user where c1 = 1 and c2 = 4;

select * from t_user where c2 = 4 and c1 = 1;

EXPLAIN shows identical execution plans for both statements, confirming that column order in the query is irrelevant.

Can a Single Column Trigger an Index?

For a composite index (c1,c2,c3):

If the WHERE clause filters on c1, the index is used.

If the WHERE clause filters only on c2, the behavior depends on the SELECT list: explain select * from t_user where c1 = 1; This uses the ref index type, efficiently locating matching rows. explain select c2 from t_user where c2 = 4; This uses the index type, scanning the entire index and performing poorly. explain select * from t_user where c2 = 4; When selecting all columns (*) with a non‑leading column condition, MySQL falls back to a full table scan.

Conclusion: a leading column of a composite index works like a normal index; a non‑leading column works only if the SELECT list contains that column alone, otherwise a full scan occurs.

Can a Composite Index Replace a Single‑Column Index?

Comparing a single‑column index (c1) with a composite index (c1,c2):

When querying by c1, both indexes perform similarly, sometimes the single‑column index is slightly faster.

A composite index does not help if only a non‑leading column (c2) is used in the WHERE clause.

If a composite index (c1,c2) exists, a separate single‑column index on c1 is unnecessary.

If only a single‑column index exists but query patterns evolve, adding a composite index can improve performance.

Summary

The article consolidates key points about using MySQL composite indexes, emphasizing the left‑most rule, the importance of column selectivity, and how to verify index usage with EXPLAIN. It also notes that execution plans may not always reflect actual runtime behavior, so practical testing is essential.