MySQL 8.0 Generated Columns: Virtual, Stored, and JSON Index Extraction

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  amount     DECIMAL(10,2),
  tax_rate   DECIMAL(5,4) DEFAULT 0.08,
  -- Virtual: computed on read, zero storage cost
  tax_amount DECIMAL(10,2) GENERATED ALWAYS AS (amount * tax_rate) VIRTUAL,
  -- Stored: computed on write, can be indexed
  total      DECIMAL(10,2) GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED
);

-- Create index on stored generated column
CREATE INDEX idx_orders_total ON orders (total);

CREATE TABLE products (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  metadata JSON,
  -- Extract and index a JSON field
  sku      VARCHAR(50) GENERATED ALWAYS AS (metadata->>'$.sku') STORED,
  category VARCHAR(50) GENERATED ALWAYS AS (metadata->>'$.category') STORED
);

CREATE INDEX idx_products_sku ON products (sku);
CREATE INDEX idx_products_category ON products (category);

-- Now these queries use indexes efficiently
SELECT * FROM products WHERE sku = 'WP-001';
SELECT * FROM products WHERE category = 'electronics';

CREATE TABLE events (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_time DATETIME NOT NULL,
  event_year SMALLINT GENERATED ALWAYS AS (YEAR(event_time)) STORED,
  event_month TINYINT GENERATED ALWAYS AS (MONTH(event_time)) STORED
);

CREATE INDEX idx_events_year_month ON events (event_year, event_month);

-- Fast query by year/month without function on indexed column
SELECT count(*) FROM events WHERE event_year = 2025 AND event_month = 10;