Boosting Query Performance with MySQL Generated Columns

Introduction

In modern database design, developers often find themselves recalculating the same values repeatedly across different application layers or SQL queries. Whether it is concatenating a user's full name or calculating a tax-inclusive price, these repetitive operations add overhead and clutter to your codebase. MySQL Generated Columns provide a robust, server-side solution to this problem.

What are Generated Columns?

Introduced in MySQL 5.7, generated columns (sometimes called computed columns) allow you to define a column whose value is automatically derived from an expression involving other columns in the same table. This means the database handles the logic, ensuring that the derived data is always in sync with its source.

Virtual vs. Stored Columns

There are two primary types of generated columns: VIRTUAL and STORED.

VIRTUAL: These values are not stored on the physical disk. Instead, MySQL calculates them on the fly whenever a row is read. This approach saves disk space but consumes CPU cycles during the read process.
STORED: These values are calculated when a row is inserted or updated and are written to the disk. While this uses more storage, it allows for faster reads and is often preferred when the column is frequently accessed.

Here is how you can define a table using a stored generated column for a simple e-commerce calculation:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  price DECIMAL(10, 2) NOT NULL,
  tax_rate DECIMAL(4, 2) DEFAULT 0.15,
  total_price DECIMAL(12, 2) AS (price * (1 + tax_rate)) STORED
);

The Indexing Advantage

One of the most powerful features of generated columns is the ability to index them. This is a game-changer for performance. If you frequently filter queries based on a complex expression, you can create a generated column for that expression and add an index to it. This allows MySQL to use a standard B-tree index to find results instantly, rather than performing a full table scan.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  INDEX (full_name)
);

-- This query will now use the index on full_name
SELECT * FROM users WHERE full_name = 'Jane Smith';

Indexing JSON Data

Generated columns are particularly useful when working with the JSON data type. Since you cannot directly index a JSON attribute, you can extract a specific key into a generated column and index that instead.

ALTER TABLE orders 
ADD COLUMN customer_id INT AS (CAST(order_data->>'$.customer_id' AS UNSIGNED)) VIRTUAL,
ADD INDEX (customer_id);

Conclusion

By leveraging MySQL Generated Columns, you move logic closer to the data, ensuring consistency and drastically improving performance through specialized indexing. It is a clean, efficient way to handle derived data without bloating your application code.