MySQL GENERATED Columns: Virtual Indexes and JSON Extraction

MySQL GENERATED columns compute their value automatically from an expression — either as a VIRTUAL column (computed on read) or a STORED column (computed on write and persisted). They are ideal for extracting JSON fields, computing derived values, and enabling indexes on expressions without changing application code.

Problem: A WooCommerce product table stores JSON metadata in a text column — querying specific JSON keys requires LIKE '%key%' patterns that cannot use indexes, causing full table scans as the catalogue grows.

Solution: Use a MySQL JSON column type instead of text, and add a VIRTUAL GENERATED column that extracts the frequently queried key with JSON_EXTRACT(). Add a regular B-tree index on the generated column — MySQL can then use the index for equality and range queries on the extracted value without scanning the full JSON.

The examples below add a generated column to extract a JSON meta value from wp_postmeta, index it, and use it to replace a slow CAST(meta_value AS DECIMAL) query.

-- 1. VIRTUAL generated column — computed on every read, costs no storage
ALTER TABLE wp_postmeta
  ADD COLUMN meta_value_num DECIMAL(15,4) GENERATED ALWAYS AS (
    CASE WHEN meta_value REGEXP '^-?[0-9]+(\.[0-9]+)?$'
         THEN CAST(meta_value AS DECIMAL(15,4))
         ELSE NULL
    END
  ) VIRTUAL;

-- 2. Add an index to the generated column — enables index-based sorting/filtering
ALTER TABLE wp_postmeta
  ADD INDEX idx_meta_key_num (meta_key, meta_value_num);

-- 3. Now this query uses the index instead of a full table scan:
SELECT post_id, meta_value_num
FROM wp_postmeta
WHERE meta_key = '_price'
ORDER BY meta_value_num DESC
LIMIT 20;
-- EXPLAIN shows: key: idx_meta_key_num, type: ref — fast!

-- 4. STORED generated column — persisted to disk (faster reads, slower writes)
ALTER TABLE wp_postmeta
  ADD COLUMN meta_value_upper VARCHAR(255) GENERATED ALWAYS AS (
    UPPER(meta_value)
  ) STORED,
  ADD INDEX idx_meta_upper (meta_key, meta_value_upper);

Use a generated column to extract a field from a JSON meta value:

-- Example: wp_postmeta stores JSON like {"color":"red","size":"L"} in meta_value
-- Extract 'color' as a generated column for fast filtering

ALTER TABLE wp_postmeta
  ADD COLUMN meta_json_color VARCHAR(50) GENERATED ALWAYS AS (
    JSON_UNQUOTE( JSON_EXTRACT(meta_value, '$.color') )
  ) VIRTUAL,
  ADD INDEX idx_product_color (meta_key, meta_json_color);

-- Now filter products by color via index:
SELECT post_id FROM wp_postmeta
WHERE meta_key = '_product_attributes'
  AND meta_json_color = 'red';

-- Check which columns are generated:
SELECT column_name, generation_expression, extra
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name   = 'wp_postmeta'
  AND extra LIKE '%GENERATED%';

-- Remove a generated column (safe — no data loss):
ALTER TABLE wp_postmeta DROP COLUMN meta_value_num;

NOTE: VIRTUAL columns cost no disk space but recompute on every read — use them for rarely-filtered derived values. STORED columns take disk space but are faster for frequently-queried expressions and can be referenced in foreign keys. Generated columns cannot reference other generated columns or non-deterministic functions like NOW() or RAND().