What Is a Multi-Valued JSON Indexes in MySQL?
A multi-valued index emits multiple entries from elements of a JSON array expression and supports selected membership predicates.
How the Multi-Valued JSON Indexes Algorithm Works
A multi-valued index emits multiple entries from elements of a JSON array expression and supports selected membership predicates.
When to Use Multi-Valued JSON Indexes
Bounded JSON arrays queried repeatedly for element membership.
How to Create a MySQL Multi-Valued JSON Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX product_tags_mvi ON products ((CAST(tags AS CHAR(32) ARRAY)));Multi-Valued JSON Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT id FROM products WHERE 'outdoor' MEMBER OF (tags);Queries Supported by Multi-Valued JSON Indexes
Bounded JSON arrays queried repeatedly for element membership. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Each element adds index storage and write work; supported expressions and operators are constrained. Indexes reduce some search work but require storage and add maintenance to writes.
Multi-Valued JSON Indexes Limitations and Common Mistakes
Requires MySQL 8.0.17+ and exact expression/operator compatibility; not a substitute for unbounded relations.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Multi-Valued JSON Indexes
Verify EXPLAIN and test empty, null, duplicate, and large arrays against the target release.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Multi-Valued JSON Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official MySQL Index Documentation
Check MySQL 8.4 Reference Manual: Optimization and Indexes for release-specific requirements and supported operators.