Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies

The previous article introduced the concept and suitable use cases of MySQL composite (multi‑column) indexes; this follow‑up focuses on the scenarios where such indexes are ineffective and how to improve query performance.

Review of Composite Index Syntax and Required Condition

Creating a composite index (default ascending order) is done with:

alter table t1 add idx_multi(f1, f2, f3);

The essential rule is that the first column (the leftmost column) of the index must appear in the WHERE clause, preferably with an equality condition. If the leftmost column is missing, the index cannot be used.

Analysis of Typical SQL Statements

Below are seven example queries and why they do not suit the idx_multi index.

SQL 1 – Filter on f2 only

select * from t1 where f2 = 1;

Since f2 is the second column of the composite index, MySQL cannot use idx_multi . Adding a single‑column index on f2 resolves the issue:

alter table t1 add key idx_f2(f2);

Execution plan changes from a full table scan (type=ALL) to an indexed lookup (type=ref).

SQL 2 – Filter on f3 only

Same situation as SQL 1; a single‑column index on f3 ( idx_f3 ) is recommended.

SQL 3 – f2 = 1 AND f3 = 1

Both columns are present but the leftmost column f1 is missing, so idx_multi cannot be used. MySQL can merge the two single‑column indexes ( idx_f2 and idx_f3 ) via index_merge . For frequent use, creating a composite index on (f2, f3) is more efficient:

alter table t1 add key idx_multi_sub(f2, f3);

SQL 4 – f2 = 1 OR f3 = 1

The OR condition prevents use of a composite index. Two separate single‑column indexes allow MySQL to apply index_merge with a union operation.

SQL 5 & SQL 6 – f1 = 1 OR f2 = 1 (or f3 = 1)

These queries include the leftmost column f1, so idx_multi can participate, but the OR forces MySQL to also use the single‑column indexes on the other columns, resulting in an index_merge with sort_union . Adding a single‑column index on f1 ( idx_f1 ) lets MySQL use a plain union instead, improving performance.

SQL 7 – f1 = 1 OR f2 = 1 OR f3 = 1

MySQL merges three single‑column indexes ( idx_f1 , idx_f2 , idx_f3 ) via index_merge . This demonstrates that a well‑designed set of single‑column indexes can handle complex OR queries.

Overall Index Landscape

show create table t1\G
CREATE TABLE `t1` (
  `id` int NOT NULL,
  `f1` int DEFAULT NULL,
  `f2` int DEFAULT NULL,
  `f3` int DEFAULT NULL,
  `f4` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_multi` (`f1`,`f2`,`f3`),
  KEY `idx_f2` (`f2`),
  KEY `idx_f3` (`f3`),
  KEY `idx_multi_sub` (`f2`,`f3`),
  KEY `idx_f1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The table now holds a primary key plus five secondary indexes. In real projects you should keep only the indexes that support frequently executed queries; rarely used indexes can be dropped to reduce write overhead.

In summary, composite indexes are powerful when the leftmost column participates in equality filtering. When that condition is not met, consider adding appropriate single‑column indexes or redesigning the composite index to match the query pattern.