Understanding MySQL Multi-Value Indexes and Their Use Cases

MySQL multi-value indexes are fundamentally different from composite (multi‑column) indexes; a composite index combines several one‑dimensional columns, while a multi-value index is built on a single multi‑dimensional field such as an array or JSON array.

Multi-value indexes were introduced in MySQL 8.0.17 to overcome the hard limit of 64 indexes per table, allowing efficient searches within an array of values stored in a single column.

Example 1 creates a simple table t1 with six integer columns and demonstrates a query that searches for the value 650 across all columns. Individual indexes are added to each column, enabling MySQL’s INDEX_MERGE optimization.

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `r3` int DEFAULT NULL,
  `r4` int DEFAULT NULL,
  `r5` int DEFAULT NULL,
  `r6` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> select count(*) from t1 where r1 = 650 or r2 = 650 or r3 = 650 or r4 = 650 or r5 = 650 or r6 = 650;

mysql> alter table t1 add key idx_r1(r1), \
    -> add key idx_r2(r2), \
    -> add key idx_r3(r3), \
    -> add key idx_r4(r4), \
    -> add key idx_r5(r5), \
    -> add key idx_r6(r6);

mysql> explain select count(*) from t1 where r1 = 650 or r2 = 650 or r3 = 650 or r4 = 650 or r5 = 650 or r6 = 650\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index_merge
possible_keys: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
          key: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
      key_len: 5,5,5,5,5,5
          ref: NULL
         rows: 18392
     filtered: 100.00
        Extra: Using union(idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6); Using where

To simplify the schema, the six columns are packed into a JSON array in a new table t3 . A multi-value index is then created on the JSON array, and the same query can be expressed with the MEMBER OF operator.

mysql> create table t3 (id int primary key, r_com json);

mysql> insert into t3 \n    select id, json_object('id', json_array(r1,r2,r3,r4,r5,r6)) from t1;

mysql> alter table t3 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (58.17 sec)

select count(*) from t3 where 650 member of(r_com->"$.id");

mysql> explain select count(*) from t3 where 650 member of(r_com->"$.id")\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: idx_r_com
          key: idx_r_com
      key_len: 9
          ref: const
         rows: 153
     filtered: 100.00
        Extra: Using where

When a table needs hundreds of columns, creating an index for each column is impossible. The article shows how to generate a table t2 with 500 integer columns using a stored procedure, populate it with random data, and then migrate the data to a JSON‑based table t4 that can be indexed with a single multi‑value index.

DELIMITER $$
CREATE PROCEDURE sp_extend_t2_columns(IN f_num INT UNSIGNED)
BEGIN
  DECLARE i INT DEFAULT 1;
  SET @stmt = 'alter table t2 ';
  WHILE i <= f_num DO
    SET @stmt = CONCAT(@stmt,' add r',i,' int,');
    SET i = i + 1;
  END WHILE;
  SET @stmt = LEFT(@stmt,CHAR_LENGTH(@stmt)-1);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DROP PREPARE s1;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_generate_t2_data(IN f_num INT UNSIGNED, IN f_total_record INT UNSIGNED)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE j INT;
  SET @@autocommit = 0;
  WHILE i <= f_total_record DO
    SET @s1 = 'insert into t2 (';
    SET @s2 = ' values (';
    SET j = 1;
    WHILE j <= f_num DO
      SET @s1 = CONCAT(@s1,' r',j,',');
      SET @s2 = CONCAT(@s2,CEIL(RAND()*1000),',');
      SET j = j + 1;
    END WHILE;
    SET @s1 = LEFT(@s1,CHAR_LENGTH(@s1)-1);
    SET @s2 = LEFT(@s2,CHAR_LENGTH(@s2)-1);
    SET @stmt = CONCAT(@s1,')',@s2,')');
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
    IF MOD(i,100)=0 THEN COMMIT; END IF;
    SET i = i + 1;
  END WHILE;
  COMMIT;
END$$
DELIMITER ;

mysql> create table t4 (id int primary key, r_com json);

mysql> alter table t4 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (2 min 15.08 sec)

select count(*) from t4 where 270 member of(r_com->'$.id');

The experiments show that a multi‑value index can replace dozens or hundreds of single‑column indexes, keeping the index count within MySQL’s limit while still providing acceptable query performance.

Overall, multi‑value indexes are a powerful tool for scenarios where many columns need to be searched for the same value, especially when the number of columns exceeds MySQL’s hard limit of 64 indexes per table.