Understanding Composite (Multi‑Column) Indexes in MySQL

Composite indexes, also known as multi‑column or joint indexes, are built on several columns and are useful when those columns are queried together or when a left‑most prefix of the columns is used.

By default the index order is ascending, equivalent to (f1 ASC, f2 ASC, f3 ASC) . Typical column combinations include (f1, f2, f3) , (f1, f2 DESC, f3) , (f1 DESC, f2 DESC, f3 DESC) , and so on.

The creation syntax is:

alter table t1 add key idx_multi(f1 [asc/desc], f2 [asc/desc], f3 [asc/desc]) [using btree | using hash];

MySQL supports up to 16 columns in a composite index. It can be built on a B‑tree (default) or a hash; the hash method is only useful when all indexed columns are filtered with equality.

For a composite index to be usable, the left‑most column (f1) must appear in the WHERE clause, preferably with an equality condition.

The article presents fifteen sample SQL statements (SQL 1‑15) that exercise various filtering patterns on columns f1, f2, and f3, all of which include f1.

SQL 1, SQL 2 and SQL 3 use the index idx_multi with a back‑table lookup; SQL 3 is the ideal case where every indexed column is filtered with equality. Its execution plan is:

(127.0.0.1:3400)|(ytt)>explain  select * from t1 where f1 = 1 and f2 = 1 and f3 = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_multi
          key: idx_multi
      key_len: 15
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

If the composite index is also a primary or unique key (as in table t3 with udx_multi ), the plan looks the same but the type becomes const .

SQL 4‑7 are covered‑index scans (type = index) that return results directly from the index without a back‑table lookup. For example, the plan for SQL 7 is:

127.0.0.1:3400)|(ytt)>explain
    -> select f1,f2,f3,count(*) from t1 group by f1,f2,f3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: idx_multi
          key: idx_multi
      key_len: 15
          ref: NULL
         rows: 32194
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

The article argues that creating separate single‑column indexes on f1 or a prefix index (f1,f2) is unnecessary because the left‑most column is already covered by idx_multi . This is confirmed by comparing the plans for SQL 2 using idx_multi versus forcing idx_multi_sub —both return identical statistics.

When the third column is filtered with a range condition (SQL 8), the composite index is still used, as shown by its plan with type: range and Using index condition . The same applies to SQL 9 where f2 is a range.

For queries where the first column is a range (SQL 11, SQL 12), only the first column of the composite index can be used.

In cases where later columns are equality‑filtered while the first column is a range (SQL 13, SQL 14), adding an extra index on (f2, f3) can improve performance. The article demonstrates creating idx_multi2(f2,f3) and shows the corresponding execution plans, which now use the new index.

SQL 15 filters on f1 and f3 (non‑contiguous columns). Because f1 is an equality condition, the original composite index can still be used, but adding a dedicated index on (f1, f3) yields a more efficient plan, as illustrated:

(127.0.0.1:3400)|(ytt)>explain  select * from t1 where f1 = 1 and f3 = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_multi,idx_multi3
          key: idx_multi3
      key_len: 10
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

The table definitions used in the examples are:

(127.0.0.1:3400)|(ytt)>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(127.0.0.1:3400)|(ytt)>show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int NOT NULL,
  `f1` int DEFAULT NULL,
  `f2` int DEFAULT NULL,
  `f3` int DEFAULT NULL,
  `f4` int DEFAULT NULL,
  UNIQUE KEY `udx_multi` (`f1`,`f2`,`f3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

In summary, when a column already appears as the first column of a composite index, adding separate single‑column or partial composite indexes provides no benefit and should be avoided.