MySQL Multi-Valued Indexes Give JSON Arrays Real Index Lookups, Six Traps Included
Array membership queries that used to force full table scans, generated columns, or a table restructure can now use a real index in MySQL 8.0.17+, but the index only fires when probe types align exactly with the declared element type and the table's collation passes a narrow whitelist. A single mis-typed WHERE clause silently sends the query back to a full scan.
MySQL 8.0.17 added multi-valued indexes, which transparently generate a virtual column, cast a JSON array to a SQL-typed array, and store one index entry per element. A row with `{"zipcode": [94477, 94536]}` produces two entries pointing back at the same row, so array membership becomes a ref or range lookup. Only three predicate forms trigger it: `MEMBER OF`, `JSON_CONTAINS`, and `JSON_OVERLAPS`; JSON_EXTRACT, JSON_SEARCH, IS NOT NULL, and LIKE gain nothing.
The feature is a functional index on a virtual column and inherits that category's constraints. Probe values must match the declared element type exactly, because JSON treats number 23 and string "23" as different values; any mismatch silently falls back to a full scan. Empty arrays never enter the index, ORDER BY and range scans do not work, and index creation runs under ALGORITHM=COPY, copying the entire table. Only binary and utf8mb4_0900_as_cs collations are accepted, so many existing tables cannot build a CHAR array index at all.
A relation table still beats the multi-valued index when a JSON array is a core business dimension that gets repeatedly filtered and counted. For infrequent ancillary tags with a small fixed element count, the multi-valued index is cheap, provided the index and the probe declare the same type.
Type alignment is where real deployments will stumble: the article's own opening example builds a CHAR(255) multi-valued index over integer business data that should have been UNSIGNED.
The index only proves itself in EXPLAIN's key column; the docs' own example requires the index to exist before the access path changes, and that gap is easy to miss on a staging environment with small data.
The utf8mb4_0900_as_cs-only charset rule quietly disables the feature for many existing tables still on utf8mb4_general_ci, so adoption depends on the state of the schema.
AI now absorbs much of the routine query-writing work, as the closing aside notes, but choosing which queries deserve an index still needs a human to reason about what the business actually filters on.
After aligning types, you also have to verify the execution plan.
Core SQL or complex SQL should naturally get an EXPLAIN after being written, to see whether it meets expectations, because load testing generally only happens at mid-to-large companies, and now that AI writes code faster and faster, the pressure it puts on testing keeps growing. Many times small companies just run load tests on the full-data migration environment, and if it's slow, then optimize it. But there are some pitfalls, like when a new feature goes live there isn't that much data, and nobody writes data-generation scripts (only in small companies). Your SQL optimizer, whether data is small or large, the indexes it takes are also somewhat different, but no matter what, verifying EXPLAIN is a good habit.