A generated column you can index – Türker Yıldırım

Indexing an expression is not possible directly, so a query filtering on YEAR(placed_at) or on a JSON path scans the table regardless of what indexes exist.

ALTER TABLE orders
  ADD COLUMN customer_tier VARCHAR(16)
    GENERATED ALWAYS AS (options->>'$.tier') STORED,
  ADD INDEX idx_tier (customer_tier);

-- VIRTUAL: computed on read, indexable, no storage
-- STORED:  computed on write, indexable, takes space

SELECT * FROM orders WHERE customer_tier = 'gold';   -- uses the index

The optimiser can substitute the generated column when it sees the same expression in a WHERE, so existing queries benefit without being rewritten — which is the property that makes this worth doing on a legacy schema. VIRTUAL is usually the right choice because a secondary index on it is materialised anyway; STORED earns its space when the expression is expensive. Adding one is an ALTER, so on a large table it belongs in the schema-change process.