Multi-Valued Indexes in MySQL: Faster Queries on JSON Arrays

Vinoth Kanna
Jun 30, 2023
12
Mins to Read
All
Improving Query Performance with Multi-Valued Indexing in MySQL 8.0
Improving Query Performance with Multi-Valued Indexing in MySQL 8.0

A multi-valued index is a secondary index on a JSON array. Instead of one index entry per row, InnoDB writes one entry for each distinct value in the array, and every entry points back to the same row. MySQL has supported multi-valued indexes since 8.0.17, and they work the same way in MySQL 8.4 LTS and 9.7 LTS.

The optimizer uses one when the WHERE clause calls MEMBER OF(), JSON_CONTAINS() or JSON_OVERLAPS() on the indexed path. In the test later in this post, adding the index changed a MEMBER OF() lookup from a full table scan with an EXPLAIN estimate of 100,515 rows to an index lookup on 1 row.

Below you will find the syntax, the composite and unique variants, the three query forms, how to confirm the plan with EXPLAIN, and the limits to check before you add one in production.

Environment & Version Compatibility (as of November 2026)

  • Feature: InnoDB multi-valued indexes, MySQL 8.0.17 and later.
  • Syntax and limits checked against the MySQL 8.4 Reference Manual. Command output in this post is from the author's original MySQL 8.0 test (2023).
  • Current supported lines: MySQL 8.4 LTS (8.4.13, October 2026) and MySQL 9.7 LTS.

Deprecation Notice

  • MySQL 8.0 reached end of life in April 2026; 8.0.46 is the final release. Oracle's end-of-life notice recommends upgrading to 8.4 LTS or 9.7 LTS. If you still run 8.0 in production, plan a MySQL 8.0 to 8.4 LTS upgrade.
  • MySQL 5.7 does not support multi-valued indexes.

Multi-Valued Indexing in MySQL 8.0

MySQL 8.0 added multi-valued indexes in version 8.0.17. Before that, searching inside JSON arrays in MySQL usually meant full table scans, because a normal index stores one entry per row and cannot point at the individual elements of an array. A multi-valued index removes that gap: it indexes the elements of a JSON array, so a lookup for one value can go straight to the rows that contain it.

A Multi-Valued Index is a secondary index defined on a column that stores an array of values. Unlike traditional indexes that have a one-to-one relationship between index and data records, Multi-Valued Indexes allow for a many-to-one relationship, where each element within the array generates an index record. This means that querying and accessing data within JSON arrays becomes simple, fast, and highly optimized.

Normal Index vs Multi-Valued Index in MySQL — 1:1 vs N:1 Index Records.

Understanding Multi-Valued Indexes and their benefits

A Multi-Valued Index in MySQL 8.0 is a powerful feature that allows for efficient indexing and retrieval of data stored within JSON arrays. Unlike a normal index, which has a one-to-one relationship between index records and data records (1:1), a Multi-Valued Index enables a many-to-one relationship (N:1), where multiple index records can point to a single data record.

To illustrate this concept, let’s consider an example where we have a multi-valued index assigned to the “postal_codes” field. Suppose we have a JSON document representing location data:

{
    "city": "New York", 
    "state": "NY", 
    "country": "America",
    "postal_codes": [10001,10001,10002,10002,10003,10004]
}

In this case, the multi-valued index gets an entry for each distinct value in the “postal_codes” array. MySQL sorts the array and removes duplicate values before it writes the index, so this document produces four index records (10001, 10002, 10003 and 10004), and each one points to the same data record. The Multi-Valued Index effectively allows for direct access to each element within the array, improving query performance when searching for specific postal codes. This is most useful for applications that store IDs, tags or codes as JSON arrays and filter on them often.

[Update as of November 2026: An earlier version of this post said six index records are created here. The design worklog for the feature, WL#8955, states that the array is sorted and duplicate values are removed, so the two repeated postal codes do not get their own entries.]
One row: postal_codes array 10001 10001 10002 10002 10003 10004 location_id 1 sorted, duplicates dropped Index entries 10001 10002 10003 10004 row 1

Creating Multi-Valued Indexes in MySQL 8.0

Syntax and Examples of Creating Multi-Valued Indexes

To create a multi-valued index in MySQL, you can use either the ALTER TABLE or CREATE INDEX statement with the CAST(… AS … ARRAY) expression. Here’s an example using the location_data table:

Table Structure:

CREATE TABLE `location_data` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_info` json DEFAULT NULL,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_id`)
)

Using ALTER TABLE:

ALTER TABLE location_data ADD INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));

Using CREATE INDEX:

CREATE INDEX idx_postal_codes ON location_data ((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));

After applying the multi-valued index on the location_data table, the updated table structure will include the index definition.

mysql> show create table location_data\G
*************************** 1. row ***************************
       Table: location_data
Create Table: CREATE TABLE `location_data` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_info` json DEFAULT NULL,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_id`),
  KEY `idx_postal_codes` ((cast(json_extract(`location_info`,_utf8mb4'$.postal_codes') as unsigned array)))
) ENGINE=InnoDB AUTO_INCREMENT=101865 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The type after AS must match the array elements: UNSIGNED for the postal codes here. The manual allows any type that CAST() supports with ARRAY, except BINARY, JSON and YEAR.

Note: The MySQL manual states that online creation of a multi-valued index is not supported, so the operation uses ALGORITHM=COPY. On a large table, plan for a full table copy and a change window rather than an in-place index build.

Composite Indexing in Multi-Valued Indexes

A multi-valued index can be defined as a composite index, allowing you to combine multiple columns into a single index. This improves data organization and enhances query performance by efficiently indexing and retrieving multiple values.

Example:

To establish a composite index on the location_info and modified columns of the location_data table, use the following query:

ALTER TABLE location_data ADD INDEX idx_postal_codes_modified((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)), modified);

This composite multi-valued index enables efficient searching based on both the postal_codes array and the modified column.

Only one multi-valued key part is allowed per index. It does not have to be the last column: the manual states that the multi-valued key part may be used in any order relative to the other parts of the key.

Unique Indexing in Multi-Valued Indexes

A multi-valued index can also be defined as a unique index, ensuring the uniqueness of values in the indexed column(s) and preventing duplicate entries.

Example:

To create a unique multi-valued index on the postal_codes array within the location_info column, use the following query:

ALTER TABLE location_data ADD UNIQUE INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));

When attempting to insert duplicate values across different records, a duplicate error will occur, causing the insertion to fail. However, if duplicate values are inserted within the same record, the insertion will be successful.

For example, the first insertion below succeeds because the duplicates are within the same record, while the second insertion fails due to duplicates across different records:

-- Insert Success

mysql> INSERT INTO location_data (location_info) VALUES ('{"city":"New York", "state":"NY", "country": "America","postal_codes":[10001, 10001, 10002, 10002, 10003, 10004]}');
Query OK, 1 row affected (0.01 sec)

-- Insert Failed

mysql> INSERT INTO location_data (location_info) VALUES ('{"city":"New York", "state":"NY", "country": "America","postal_codes":[10001, 10002, 10003, 10004]}');
ERROR 1062 (23000): Duplicate entry '[10001, ' for key 'location_data.idx_postal_codes'

The first insert succeeds because duplicate values inside one array are removed before the index is written, so the unique check only sees the four distinct postal codes. The second row then collides with values that are already in the index.

By utilizing multi-valued indexes, including composite and unique indexing, you can optimize your database’s performance and enforce data integrity.

Accessing Multi-Valued Indexes using Functions

Exploring MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS() Functions

The Multi-Valued Index in MySQL enables efficient retrieval of records by utilizing three functions: MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS(). These functions are specified in the WHERE clause of the query to filter and search for specific values within multi-valued indexed columns.

Each one asks a different question of the same index. MEMBER OF() checks for one value. JSON_CONTAINS() returns rows whose array holds every value you pass, and JSON_OVERLAPS() returns rows whose array holds at least one of them. In the manual's words, JSON_CONTAINS() performs an AND operation on search keys, while JSON_OVERLAPS() performs an OR operation.

MEMBER OF() one value 85001 10001 10003 85001 85002 ✓ JSON_CONTAINS() every value (AND) [10001,10003] 10001 10003 85001 85002 ✓ JSON_OVERLAPS() any value (OR) [10002,85002] 10001 10003 85001 85002 ✓

Against the location_data table, the two array forms look like this:

SELECT * FROM location_data
WHERE JSON_CONTAINS(location_info->'$.postal_codes', CAST('[10001,10003]' AS JSON));

SELECT * FROM location_data
WHERE JSON_OVERLAPS(location_info->'$.postal_codes', CAST('[10002,85002]' AS JSON));
Best Practice: Write the predicate against the same JSON path you indexed ('$.postal_codes' here), then run EXPLAIN and check that the key column names the multi-valued index. If it shows NULL, the query is not using it.

In this example, let’s focus on using the MEMBER OF() function to search for the postal code 85001 within the location_info column of the location_data table:

mysql> SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
| location_id | location_info                                                                                          | modified            |
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
|          11 | {"city": "Phoenix", "state": "AZ", "country": "America", "postal_codes": [85001, 85002, 85003, 85004]} | 2023-05-28 19:50:16 |
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

The query returns the records that contain the specified postal code within their postal_codes array. In this case, it retrieves the record with location_id 11, which has the postal code 85001.

By using the MEMBER OF() function, you can efficiently search for specific values within multi-valued indexed columns and retrieve the relevant records.

Explain Plan :

mysql> EXPLAIN SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | location_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100515 |   100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

The current query performs a full table scan, taking approximately 150 ms to execute.

‍

Query Optimization Using Multi-Valued Indexes

When dealing with large datasets, query performance becomes crucial. By creating a multi-valued index on the postal_codes array within the location_info column, you can significantly improve the query performance for searching and retrieving records based on postal codes.

To optimize the query, you can add the multi-valued index using the following ALTER TABLE statement:

mysql> ALTER TABLE location_data ADD INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

After adding the multi-valued index, the query’s execution plan changes. Instead of performing a full table scan, it utilizes the index to efficiently retrieve the desired records. The query processed only one row, resulting in an execution time of 1 ms.

The EXPLAIN statement demonstrates the improved query plan:

mysql> EXPLAIN SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | location_data | NULL       | ref  | idx_postal_codes | idx_postal_codes | 9       | const |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

The use of appropriate functions and proper index creation can greatly enhance the efficiency of your database queries.

‍

Note: These figures come from the author's 2023 test on MySQL 8.0 against a table of about 100,000 rows. Hardware, buffer pool state and run count were not recorded, so read the timings as an illustration. Re-run EXPLAIN ANALYZE on your own version and data before you size the benefit.

With the multi-valued index in place, the query’s performance is significantly improved, reducing the number of rows examined and reducing the execution time from 150 ms to just 1 ms. This optimization enhances the overall efficiency of your queries and improves the responsiveness of your database system.

Query performance analysis before and after optimization

Key Considerations for Multi-Valued Indexing

  • DML operations for multi-valued indexes are handled similarly to normal indexes, with the distinction that there may be multiple inserts or updates for a single clustered index record.
  • When indexing data, the multi-valued index does not consider empty arrays, so searching for empty values using the index will not yield any matches.
  • It’s important to note that JSON null values are not allowed within multi-valued indexed arrays. If any element within the array is null, it will be treated as a JSON null value and can result in an error due to an invalid JSON value.
  • A composite index can only accommodate one multi-valued index, so it’s essential to consider the index structure and column combinations accordingly.
  • Furthermore, multi-valued indexes do not support the ordering of values, making them unsuitable for use as primary keys. Additionally, they cannot be utilized with ASC or DESC orders.

[Update as of November 2026: further limits listed in the MySQL 8.4 manual:]

  • A multi-valued index cannot be a covering index, and it does not support range scans or index-only scans, because the entries for one row are spread across the index.
  • Online creation is not supported; the operation uses ALGORITHM=COPY.
  • Multi-valued indexes are not permitted in foreign key specifications, index prefixes cannot be defined on them, and they cannot be defined on data cast as BINARY.
  • Only two character set and collation combinations are supported: binary with the default binary collation, and utf8mb4 with the default utf8mb4_0900_as_cs collation.
  • The values for one record must fit in a single undo log page, 65221 bytes. Past that, an insert fails with an error similar to ERROR 3905 (HY000): Exceeded max number of values per record.

Multi-valued indexes provide an effective solution for indexing and querying JSON arrays in MySQL. The use of functions like MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS() allows for efficient retrieval of records based on specific array values. These indexes improve query performance by enabling direct access to array elements, eliminating the need for time-consuming table scans.

However, it is important to note that multi-valued indexes have certain limitations, including the inability to support ordering and primary keys. By considering these limitations and making informed decisions, multi-valued indexes can greatly enhance the efficiency and effectiveness of MySQL queries involving JSON arrays.

For more comprehensive information on multi-valued indexes, you can refer to the official MySQL documentation on multi-valued indexes. It provides in-depth insights and guidance on utilizing this feature to its fullest potential.

For related reading on the Mydbops blog, see the JSON functions added in MySQL 8.0, including JSON_OVERLAPS(), indexing virtual columns in MySQL 5.7 (the older way to index a single JSON value), functional indexes in MySQL 8.0, composite and covering indexes, and why the MySQL optimizer skips an index. You can also browse all of our informative blogs on database consulting, support, and technology.

If you want a second opinion on JSON-heavy query plans or index design, our MySQL consulting team can review them as a fixed-scope engagement, or you can reach out to us directly.

‍

Frequently Asked Questions

What is a multi-valued index in MySQL?

A multi-valued index is a secondary InnoDB index defined on a JSON array with CAST(... AS type ARRAY). It stores one index record for each distinct value in the array, and every record points to the same row, so a lookup on one array element can use the index instead of scanning the whole table.

Which MySQL versions support multi-valued indexes?

MySQL 8.0.17 and later, including MySQL 8.4 LTS and MySQL 9.7 LTS. MySQL 8.0 reached end of life in April 2026 with release 8.0.46, so new work should target 8.4 LTS or 9.7 LTS.

Which queries can use a multi-valued index?

The optimizer uses a multi-valued index when MEMBER OF(), JSON_CONTAINS() or JSON_OVERLAPS() is used in the WHERE clause on the indexed JSON path. Check with EXPLAIN: the key column should name the multi-valued index.

Does the multi-valued key part have to be last in a composite index?

No. Only one multi-valued key part is allowed per index, but the MySQL manual states that it may be used in any order relative to the other parts of the key.

Why does a unique multi-valued index accept duplicate values inside one row?

MySQL sorts each array and removes duplicate values before it writes the index entries, so repeats inside one row never reach the unique check. Inserting a value that is already indexed for another row returns a duplicate key error (ERROR 1062).

What are the main limitations of multi-valued indexes?

Only one multi-valued key part per index. It cannot be a primary key, a foreign key, a covering index or a prefix index, and it cannot use ASC or DESC. It does not support range scans or index-only scans. Creating one uses ALGORITHM=COPY. Only the binary and utf8mb4_0900_as_cs collations are supported. Empty arrays are not indexed, and a JSON null element raises an error.

No items found.

About the Author

Founding Partner and Database Architect at Mydbops, with 14+ years running MySQL, MariaDB and MongoDB systems. Before co-founding Mydbops in 2016, he worked as a remote DBA at Percona. He holds AWS Solutions Architect Professional and Oracle MySQL 8.0 certifications.

Topic covered

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.