How to Fix MySQL JSON_EXTRACT Query Not Using Functional Index in MySQL 8

You've created a functional index specifically to speed up queries filtering on a value inside a JSON column, but EXPLAIN shows MySQL is still doing a full table scan instead of using it. Functional indexes in MySQL 8 are powerful but strict — the query's expression needs to match the indexed expression essentially exactly, and even small syntactic differences prevent the optimizer from recognizing they're the same thing.

The Problem

A functional index exists, but the query planner ignores it:

CREATE INDEX idx_orders_status ON orders ((CAST(payload->>'$.status' AS CHAR(20))));

EXPLAIN SELECT * FROM orders WHERE JSON_EXTRACT(payload, '$.status') = 'shipped';

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 984213  | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

type: ALL confirms a full table scan — the index defined above is being completely ignored.

Why It Happens

A MySQL functional index stores the result of a specific expression, and the optimizer can only use it when a query's WHERE clause contains an expression that matches — not just semantically, but essentially syntactically — what was indexed. Common mismatches:

• Different JSON extraction syntax — JSON_EXTRACT(payload, '$.status') and the shorthand payload->>'$.status' aren't automatically recognized as identical by the optimizer for index-matching purposes, even though they produce the same result, unless the index was defined using the exact form your query also uses (or vice versa).
• A type mismatch between the indexed expression and the comparison value — indexing a CAST to CHAR but comparing against a value MySQL implicitly treats differently, or omitting a needed cast in the index definition that the query's own comparison relies on for a fair type match.
• Extracting via a different but logically equivalent path expression, such as accessing the same JSON key through a differently structured (but equivalent) path syntax.
• Outdated table statistics causing the optimizer to underestimate the index's value even when the expression matches correctly, leading it to prefer a full scan anyway for what it believes is a low-selectivity query.

The Fix

Match your query's expression exactly to how the index was defined. If the index uses JSON_EXTRACT and a CAST, use exactly that same form in your query rather than the shorthand arrow operator:

-- Index defined as:
CREATE INDEX idx_orders_status ON orders ((CAST(payload->>'$.status' AS CHAR(20))));

-- Query must match the same expression pattern to be eligible:
EXPLAIN SELECT * FROM orders
WHERE CAST(payload->>'$.status' AS CHAR(20)) = 'shipped';

Verify the index is now actually used:

+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys       | key                  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_orders_status    | idx_orders_status    | 83      | const |    5 | Using where |
+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------------+

type: ref with a small estimated row count confirms the index is now being used correctly instead of scanning the whole table.

If matching exact syntax in every query is impractical (especially with generated queries from an ORM), consider using a generated column instead of a pure functional index — this gives you a real, named column you can reference directly and unambiguously, sidestepping the exact-expression-matching requirement entirely:

ALTER TABLE orders
  ADD COLUMN status_extracted VARCHAR(20)
  GENERATED ALWAYS AS (payload->>'$.status') STORED,
  ADD INDEX idx_status_extracted (status_extracted);

Now queries can reference the generated column directly, which is simpler to get right consistently across different parts of an application or ORM-generated queries:

SELECT * FROM orders WHERE status_extracted = 'shipped';

Make sure table statistics are current, since stale statistics can cause the optimizer to underestimate an index's benefit even when the expression matches correctly:

ANALYZE TABLE orders;

Still Not Working?

If the expression matches exactly and statistics are fresh but the optimizer still prefers a full scan, check whether the query's selectivity is genuinely too low for the index to actually help — if most rows in the table have status = 'shipped', a full scan can legitimately be cheaper than an index lookup returning a large fraction of the table, and the optimizer's choice would be correct in that case rather than a bug to fix:

SELECT status_extracted, COUNT(*) FROM orders GROUP BY status_extracted;

If the value you're filtering on is genuinely common (representing a large fraction of all rows), the full scan may actually be the right choice, and the real fix is reconsidering what you're actually trying to optimize for — a highly selective filter combined with this specific common value might benefit more from adding an additional filter condition that narrows the result set further, rather than trying to force index usage for a query pattern where a scan is genuinely the better strategy.