A Guide to Generated Columns in MySQL 8: Query Optimization and Smart Indexing

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Quick start: Get running in 5 minutes

Whenever you need to calculate an order total, concatenate a full name, or extract a specific field from JSON, where do you usually do it? Offload it to your backend services, or write database triggers? Both approaches add unnecessary complexity. MySQL provides an elegant built-in feature for this exact use case: Generated Columns.

Put simply, MySQL automatically calculates a column’s value using an expression based on other columns in the same row. Take a look at the sample order items table below:

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    discount_percent INT DEFAULT 0,
    -- Generated column calculating total amount after discount
    total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (
        (price * quantity) * (1 - discount_percent / 100)
    ) VIRTUAL
);

-- Insert test record, omitting total_amount
INSERT INTO order_items (item_name, price, quantity, discount_percent)
VALUES ('Mechanical Keyboard', 1500000, 2, 10);

-- Verify result
SELECT id, item_name, total_amount FROM order_items;

Once executed, total_amount automatically yields 2700000.00. You don’t have to handle the arithmetic yourself; MySQL takes care of everything under the hood.

The Core Mechanics: Virtual Columns vs. Stored Columns

When creating a Generated Column, you must select one of two modes: VIRTUAL or STORED. Picking the wrong one can bloat your storage or exhaust your CPU resources.

1. Virtual Column (Default Mode)

With VIRTUAL, MySQL does not persist column data to disk. Whenever a SELECT statement requests this column, MySQL computes the value on the fly directly in RAM.

  • Pros: Consumes zero additional disk space. INSERT and UPDATE throughput remains unaffected because the storage engine doesn’t write extra column data to disk.
  • Cons: Incurs CPU overhead at query time. Scanning millions of rows without an appropriate index can put significant strain on your CPU.
  • Performance Leverage: MySQL allows you to create secondary B-Tree indexes directly on VIRTUAL columns. The calculated values are stored only within the index tree rather than the primary table structure. This approach saves disk capacity while drastically accelerating query performance.

2. Stored Column

Conversely, STORED evaluates the expression and writes the value directly to disk whenever a row is inserted or updated.

  • Pros: Fast read operations. The data resides on disk just like any standard physical column, eliminating redundant CPU recalculations on query.
  • Cons: Increases disk storage footprint. Write operations (INSERT/UPDATE) are slower due to the additional I/O overhead.
ALTER TABLE users 
ADD COLUMN full_name VARCHAR(150) 
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

Real-World Practice: JSON Indexing and Composite Indexes

On a 50GB production logging setup I previously managed, the audit_events table expanded to 12 million records. At that scale, every query filtering by JSON attributes became a nightmare: taking anywhere from 2.8s to 4s per execution due to full table scans.

Accelerating JSON Queries with Functional Indexes

MySQL doesn’t support direct B-Tree indexing on raw JSON columns. To make queries lightning fast, the standard best practice is creating a Virtual Column to extract the target attribute, then building an index on that virtual column:

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_data JSON NOT NULL,
    -- Extract email from JSON into a virtual column
    user_email VARCHAR(255) GENERATED ALWAYS AS (
        profile_data->>'$.contact.email'
    ) VIRTUAL,
    -- Add index on the virtual column
    INDEX idx_user_email (user_email)
);

-- Or quickly patch an existing table
ALTER TABLE user_profiles 
ADD COLUMN phone_number VARCHAR(20) 
GENERATED ALWAYS AS (profile_data->>'$.contact.phone') VIRTUAL,
ADD INDEX idx_phone (phone_number);

Let’s inspect the execution plan using EXPLAIN:

EXPLAIN SELECT id FROM user_profiles WHERE user_email = '[email protected]';

The type column in the EXPLAIN output transitions from ALL to ref. Instead of scanning millions of rows, MySQL only looks up a handful of index tree entries. Query execution time drops from 2.8 seconds down to a mere 4 milliseconds.

Building Composite Indexes for Complex Filters

You can seamlessly pair Generated Columns alongside conventional physical columns within a single composite index:

ALTER TABLE order_items 
ADD INDEX idx_discount_amount (discount_percent, total_amount);

Production Pitfalls to Avoid

Despite their convenience, misusing Generated Columns can introduce unexpected production incidents if you ignore these critical principles:

  1. Functions Must Be Deterministic: The generation expression must be completely deterministic: identical inputs must always yield the exact same output. You cannot use non-deterministic or time-dependent functions such as NOW(), RAND(), UUID(), or subqueries referencing other tables.
  2. Default to VIRTUAL First: Don’t jump straight to STORED unless the underlying expression is exceptionally computation-heavy and read under high concurrency. Pairing VIRTUAL with an index is almost always the optimal trade-off for both storage footprint and disk I/O.
  3. Right-Size Data Types: When parsing strings from JSON, define precise field boundaries, such as VARCHAR(100) rather than lazily defaulting to VARCHAR(255) or TEXT. This optimization conserves buffer pool memory and keeps index sizes compact.
  4. Exercise Caution with Large ALTER Operations: Adding a VIRTUAL column in MySQL 8 is an Instant/In-Place DDL operation that executes almost instantaneously without locking the table. However, adding a STORED column to a table with tens of millions of rows forces a full rewrite on disk, triggering severe I/O bottlenecks and table locks. Thoroughly validate your migrations on a staging environment before pushing to production.
Share: