Posted inMySQL

MySQL Generated Columns: The Ultimate Query Speed ‘Hack’ Without Changing Code

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

Why Should You Care About Generated Columns?

Writing the (price * quantity) AS total_price clause repeatedly across dozens of queries is not only tedious but also prone to errors. Worse, if you store JSON data and frequently filter by a specific field, the JSON_EXTRACT function will spike CPU usage once your table reaches millions of rows.

In the past, we often used Triggers to update auxiliary columns or performed calculations directly in the Backend. However, Triggers act like a “black box” that is difficult to maintain. Meanwhile, calculating at the App layer makes it impossible to leverage indexes for faster searching.

From my own experience in a previous project: With an orders table of about 10 million records (50GB in size), moving calculations from queries to Generated Columns helped reduce CPU load from 70% to 25%. Generated Columns (available since MySQL 5.7) allow you to create columns that automatically calculate values from existing data within the same row.

You need to distinguish between these two types to avoid wasting resources:

  • Virtual Generated Columns (Default): These don’t consume disk space because the values are calculated on the fly when you read data. The key advantage is that you can still create an Index on these virtual columns.
  • Stored Generated Columns: Values are physically stored on the disk during an INSERT or UPDATE. They consume extra storage but offer extremely fast read speeds since MySQL doesn’t need to recalculate them.

Real-world Implementation: Syntax and Usage

You don’t need to install any extensions because this is a core feature. Here is how to define automatic columns when creating a table:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    quantity INT,
    -- Virtual Column: Memory optimization
    total_price DECIMAL(10, 2) AS (price * quantity) VIRTUAL,
    -- Stored Column: CPU optimization
    total_price_stored DECIMAL(10, 2) AS (price * quantity) STORED
);

If your system is already running, you can use ALTER TABLE. I often use this method to optimize slow queries without disrupting the application’s logical structure:

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) 
AS (original_price * 0.9) VIRTUAL;

Important note: You cannot manually INSERT data into these columns. MySQL takes full control. Any attempt to override them from the Application side will be immediately rejected by the system.

02 “Life-saving” Case Studies for Large Systems

1. Boosting JSON Search Speed by 25x

This is the most practical application. Suppose you store user configurations in a settings column (JSON). You want to find all users who have set theme = 'dark'.

-- This query will perform a Full Table Scan, which is extremely slow!
SELECT * FROM user_profiles WHERE settings->"$.theme" = 'dark';

The solution is to create a Virtual Column that extracts the theme and then index it:

ALTER TABLE user_profiles
ADD COLUMN user_theme VARCHAR(20) AS (settings->>"$.theme") VIRTUAL,
ADD INDEX idx_user_theme (user_theme);

After applying this, MySQL will use a B-Tree Index for searching instead of parsing every JSON file. Response times can drop from 500ms to under 20ms.

2. Searching by Full Name

Instead of using CONCAT(first_name, ' ', last_name) in the WHERE clause (which invalidates the Index), use a Stored Column:

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED,
ADD INDEX idx_full_name (full_name);

When to Choose Virtual vs. Stored?

Consider this based on your server resources:

  • Use Virtual when: You want to save Disk space. This column is highly effective when you need to index fields that rarely change but are frequently used for filtering.
  • Use Stored when: Calculations are too complex and consume significant CPU for string processing. Or when you are using older MySQL versions that don’t support indexing virtual columns well.

Verifying the Optimization

After making changes, always use EXPLAIN to verify. If the key column displays the name of the index you just created, you have succeeded.

EXPLAIN SELECT * FROM user_profiles WHERE user_theme = 'dark';

To manage the list of automatic columns in the system, you can perform a quick query via information_schema:

SELECT table_name, column_name, generation_expression 
FROM information_schema.columns
WHERE is_generated = 'ALWAYS' AND table_schema = 'your_db_name';

Hard-earned lesson: Closely monitor disk usage (Data_length) when using Stored Columns on large tables. Conversely, if using Virtual Columns with complex Regex functions, keep an eye on your server’s CPU graphs.

In summary, Generated Columns are a perfect balance between convenience and performance. They make your SQL code cleaner and leverage the full power of Indexes without requiring significant changes to your Backend logic.

Share:
Tags: