Real-World Context: When JSON Arrays Choke Your Database
During the last Black Friday, our e-commerce system triggered critical red alerts. The CPU on our master database (an AWS RDS db.r6g.2xlarge instance) spiked straight to 98%. Multiple API endpoints continuously threw 504 Gateway Timeouts.
The root cause stemmed from the assigned_roles column in the app_users table and the tags column in the products table. The team had designed them to store role IDs as JSON arrays like [101, 204, 305]. Back in staging, when testing with tens of thousands of records, functions like MEMBER OF() or JSON_CONTAINS() ran blazingly fast in under 10ms.
Once deployed to production with over 10 million rows, everything fell apart. Every query filtering user permissions consumed anywhere from 1.8s to nearly 3s. MySQL was forced to execute a Full Table Scan (ALL) across the entire 10 million rows. The engine had to unpack each individual JSON array in every single record just to look for a single number, instantly bottlenecking the system.
In older MySQL versions, the standard workarounds were database normalization: splitting data into a separate 1-to-many relational table or leveraging Generated Columns. However, traditional B-Tree indexes only map a single row to exactly one scalar value. They cannot index individual elements inside a JSON array.
MySQL 8.0.17 solved this issue once and for all with Multi-Valued Indexes. This feature allows the B-Tree index to create multiple index records that all point back to a single Primary Key.
Environment Requirements & Sample Data
This feature requires at least MySQL 8.0.17 (or newer 8.0.x releases, such as 8.4 LTS) running on the InnoDB engine.
You can quickly check your current version with:
mysql -u root -p -e "SELECT VERSION();"Below is a schema that reproduces the exact table structure from our incident:
CREATE DATABASE IF NOT EXISTS demo_indexing;
USE demo_indexing;
CREATE TABLE app_users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
assigned_roles JSON NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Each record stores a list of user permissions, such as: [1, 5, 12, 45].
How to Configure a Multi-Valued Index
1. Syntax for Creating an Index on a JSON Array
We use the CAST(... AS type ARRAY) function inside a Functional Index definition:
ALTER TABLE app_users
ADD INDEX idx_user_roles ( (CAST(assigned_roles AS UNSIGNED ARRAY)) );The double parentheses ( (...) ) are mandatory syntax. If you omit one set of parentheses, MySQL will throw a syntax error immediately.
The underlying mechanism is very straightforward. Suppose user ID 999 holds the array [1, 5, 12]. The B-Tree creates three distinct index keys for 1, 5, and 12. All three keys point directly to primary key 999.
2. The Three Query Functions That Leverage the Index
The MySQL Optimizer only uses a Multi-Valued Index for the following three functions:
- MEMBER OF(): Checks whether a scalar value exists within the array.
- JSON_CONTAINS(): Checks whether the array contains a specified subset.
- JSON_OVERLAPS(): Checks whether two arrays share any common elements. This is especially useful when filtering users who have role 12 OR 45.
Practical query examples:
-- 1. Find users with role ID 12
SELECT id, username, assigned_roles
FROM app_users
WHERE 12 MEMBER OF (assigned_roles->'$');
-- 2. Find users who have both roles 12 and 45
SELECT id, username, assigned_roles
FROM app_users
WHERE JSON_CONTAINS(assigned_roles, '[12, 45]');
-- 3. Find users who have at least one of roles 12 or 45 (overlap)
SELECT id, username, assigned_roles
FROM app_users
WHERE JSON_OVERLAPS(assigned_roles, CAST('[12, 45]' AS JSON));3. The Type Mismatch Pitfall
This is the most common mistake that silently invalidates the index.
When creating the index, you defined the type as UNSIGNED ARRAY. However, during querying, backend code might pass a string instead: '12' MEMBER OF (...). Due to the type mismatch between a string and an integer, MySQL will immediately bypass the index. The query will fall back to a dreaded Full Table Scan.
Always ensure that the query argument type matches the indexed array type 100%.
Benchmarking & Performance Trade-offs
Don’t just assume everything is fine right after your ALTER TABLE command finishes. Always verify execution plans using EXPLAIN.
1. Execution Plan Comparison
Query before adding the index:
EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | app_users | NULL | ALL | NULL | NULL | NULL | NULL | 10245100 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+MySQL must scan all 10.2 million rows with type = ALL.
After adding idx_user_roles:
EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | app_users | NULL | ref | idx_user_roles | idx_user_roles | 9 | const | 42 | 100.00 | NULL |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+The access type improves to ref. The number of rows examined plummets from over 10 million to just 42. In our production environment, query latency dropped from 1.8s down to 2ms.
2. Write Amplification and Disk Footprint Costs
Fast reads often come at the expense of write performance. This feature is not free.
On a 10-million-row table, if each JSON array contains an average of 5 role IDs, MySQL must maintain up to 50 million index entries in the B-Tree.
You can check the index storage overhead using this query:
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'demo_indexing' AND table_name = 'app_users';Practical tip: Only apply Multi-Valued Indexes to small, bounded arrays (under 20 items), such as role IDs, permissions, or category IDs. If your arrays contain hundreds of elements and the table experiences heavy INSERT/UPDATE traffic, disk I/O will climb rapidly.
