MySQL 8.0 JSON Functional Indexes: Fast Path Lookups Without Generated Columns

The problem with JSON path filtering in MySQL 5.7

MySQL 5.7 introduced the JSON data type with validation and the ->/->> path extraction operators. But if you wanted to filter rows by a value buried inside a JSON document — say, customer->>'$.tier' = 'platinum' — the optimizer had no choice but to scan the whole table. The workaround was to create a virtual generated column that extracted the scalar, then index that column. That meant an ALTER TABLE to add the column, another to add the index, and extra metadata to maintain.

Functional indexes in MySQL 8.0

MySQL 8.0 adds functional indexes (sometimes called expression indexes). The syntax CREATE INDEX idx_name ON tbl ((col->>'$.path')) creates a hidden virtual generated column behind the scenes and indexes it in one statement. No explicit ALTER TABLE ... ADD COLUMN is required. The optimizer can then use ref or eq_ref access for equality, IN, and range predicates on the extracted scalar (string, number, boolean).

Worked example: filtering orders by customer tier

Assume an orders table with a customer JSON column that stores objects like {\"id\": 123, \"tier\": \"platinum\"}.

-- Run in a MySQL 8.0+ shell with ALTER privilege on the table
ALTER TABLE orders ADD INDEX idx_customer_tier ((customer->>'$.tier'));

-- Verify the index exists and shows the hidden column
SHOW CREATE TABLE orders;
-- Output includes: KEY `idx_customer_tier` ((`customer` ->> '$.tier'))

-- Query that uses the index
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE customer->>'$.tier' = 'platinum';
-- Expected: -> Index lookup on orders using idx_customer_tier (ref, key_len=...)

The ->> operator returns the unquoted scalar (text without surrounding quotes), which is what the index stores. Using -> (which returns a JSON value with quotes for strings) would not match the index and falls back to a full scan.

Trade-offs and limitations

• Key length limit: The extracted value must fit within the 3072-byte InnoDB index key prefix. For long strings or arrays, wrap the expression: CAST(customer->>'$.notes' AS CHAR(255)) or SUBSTRING(customer->>'$.notes', 1, 255).
• Collation sensitivity: The hidden column inherits the table's default collation unless you specify COLLATE in the index definition. This affects case-sensitivity and sort order for string values.
• No ORDER BY / GROUP BY support alone: The optimizer only uses the functional index for ordering or grouping if the query also filters on that same path with a compatible predicate.
• Replication format: Statement-based replication does not replicate the hidden generated column. Use row-based or mixed format to avoid replica drift.
• Statistics freshness: After bulk loads, run ANALYZE TABLE orders so the optimizer has accurate cardinality estimates. Stale stats can cause the index to be ignored.
• Instant DDL: Dropping a functional index is metadata-only (instant) in MySQL 8.0.23+. Earlier 8.0 versions may rebuild the table.

How to verify it's working

1. EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE customer->>'$.tier' = 'platinum'; — look for Index lookup with your index name and ref access.
2. SELECT INDEX_NAME, COLUMN_NAME, INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='orders' AND INDEX_NAME='idx_customer_tier'; — INDEX_TYPE should be FUNCTIONAL and COLUMN_NAME shows the expression.
3. Check index size: SELECT NAME, STAT_VALUE FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='idx_customer_tier'; compare STAT_VALUE (pages) to table row count.
4. Test the predicate mismatch: EXPLAIN SELECT * FROM orders WHERE customer->'$.tier' = 'platinum'; should show a full table scan.

Closing: put it in production with guardrails

Functional indexes give you JSON path filtering performance without the generated-column ceremony. Add the index, run ANALYZE TABLE, confirm with EXPLAIN FORMAT=TREE, and monitor index size via INNODB_INDEXES. If you hit the key-length ceiling, truncate the extracted value in the index definition. Keep replication in row-based mode, and remember that ORDER BY on the path alone won't use the index. That's the practical checklist — now go make those JSON queries fast.