Adding an Index on a JSON Column in MySQL 8.0 Using a Stored Generated Column

Desired outcome

Create an index that allows MySQL to efficiently filter rows based on a scalar value inside a JSON document, without scanning the entire table.

Prerequisites

• MySQL Server version 8.0.0 or later.
• A table that already contains a JSON column (e.g., profile JSON).
• Privileges to alter the table and create indexes (ALTER, INDEX).
• Enough free disk space for the generated column values and the index (roughly proportional to the number of rows times the size of the extracted scalar).

Procedure

1. Identify the JSON path you want to index. For example, to index an age field stored as $.age inside a JSON document.
2. Add a stored generated column that extracts the scalar value deterministically:

ALTER TABLE users
  ADD COLUMN age_int INT
    GENERATED ALWAYS AS (JSON_EXTRACT(profile, '$.age')) STORED;

Run this command with a MySQL client that has the necessary privileges. The column age_int will be materialized on disk.

3. Create an index on the generated column:

CREATE INDEX idx_users_age ON users(age_int);

4. Verify the column and index exist:

SELECT COLUMN_NAME, DATA_TYPE, GENERATION_EXPR
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'age_int';

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'age_int';

5. Test query usage with EXPLAIN:

EXPLAIN SELECT * FROM users WHERE age_int = 30;

Look for type: ref or type: range and the key idx_users_age in the output.

Expected checks

• The generated column appears in INFORMATION_SCHEMA.COLUMNS with GENERATION_EXPR showing the JSON path.
• The index appears in INFORMATION_SCHEMA.STATISTICS with NON_UNIQUE = 0 if you created a unique index, or NON_UNIQUE = 1 for a regular index.
• EXPLAIN shows the index being used (key = idx_users_age) for queries that filter on the generated column.
• Query latency for selective filters should be noticeably lower than a full table scan (you can compare with and without the index using a benchmark tool).

Recovery options (rollback)

Adding the generated column and its index changes the table structure. To revert:

1. Drop the index:

ALTER TABLE users DROP INDEX idx_users_age;

2. Drop the generated column:

ALTER TABLE users DROP COLUMN age_int;

After these steps the table returns to its original state, and any queries that relied on the index will fall back to a full scan of the JSON column.

Limitations and considerations

• The generated column must be STORED; virtual generated columns cannot be indexed.
• Only deterministic, single‑valued JSON expressions can be used (e.g., JSON_EXTRACT(doc, '$.field') that returns a scalar). Arrays, objects, or non‑deterministic functions cannot be indexed.
• Index size grows with the number of distinct extracted values; monitor disk usage if the JSON documents are large or the extracted field has high cardinality.
• Performance gain depends on selectivity. If most rows share the same value, the index may still lead to an index scan rather than a significant speedup.
• Updates to the JSON column cause the generated column to be recomputed, which adds write overhead.