Speeding Up Aggregates with MySQL 8.0 Stored Generated Columns

Problem: slow aggregate queries on computed values

When a report needs the sum of quantity * unit_price for millions of sales rows, MySQL must evaluate that multiplication for every row during the query. The CPU cost adds up, especially in read‑heavy workloads, and the query can become a bottleneck even when the underlying tables are well‑indexed.

Takeaway

By declaring a stored generated column that materializes the expression quantity * unit_price, you can index the result and let MySQL read a pre‑computed value instead of recalculating it each time. This reduces per‑query CPU at the cost of modest write overhead and extra storage.

What are stored generated columns?

A stored generated column is defined as part of the table definition. MySQL evaluates the supplied deterministic expression on every INSERT and UPDATE, stores the result on disk, and treats the column like any other column for indexing and retrieval. Non‑deterministic functions such as NOW() or RAND() are not allowed.

Worked example: a sales table

Assume a simple sales schema:

CREATE TABLE sales (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    sale_date DATE NOT NULL
) ENGINE=InnoDB;

To materialize the line‑total, add a stored generated column:

-- Run with a user that has ALTER privilege on the table
ALTER TABLE sales
    ADD COLUMN line_total DECIMAL(12,2) AS (quantity * unit_price) STORED;

Now create an index to speed up aggregates:

CREATE INDEX idx_line_total ON sales(line_total);

Verifying the benefit

You can check that the optimizer uses the new index with EXPLAIN:

EXPLAIN SELECT SUM(line_total) FROM sales WHERE quantity > 0;

Look for type: range or type: ref on idx_line_total in the output. If the plan shows a full table scan (type: ALL), the index is not being used—verify that the column is indeed stored (SHOW COLUMNS FROM sales WHERE Field = 'line_total' shows EXTRA: STORED GENERATED) and that statistics are up‑to‑date (ANALYZE TABLE sales).

To gauge storage impact, compare table size before and after:

SHOW TABLE STATUS LIKE 'sales'\G

The Data_length value will increase roughly by sizeof(line_total) * row_count. For a DECIMAL(12,2) column, that is about 6 bytes per row plus any overhead.

Trade‑offs and limitations

• Write overhead: Each INSERT or UPDATE now evaluates the expression and writes the result, which adds a small CPU and I/O cost.
• Storage cost: The column occupies space proportional to its data type and the number of rows.
• Altering the expression: Changing the generated column definition requires ALTER TABLE ... MODIFY COLUMN, which rebuilds the column and can be expensive on large tables.
• Deterministic only: Expressions must be deterministic; you cannot use NOW(), RAND()

Practical rollback

If you decide the generated column is not worth the cost, you can drop it:

ALTER TABLE sales DROP COLUMN line_total;

This operation removes the column and its index (if any) and rewrites the table without the stored data. Because it changes the table structure, a rollback is only possible by restoring from a backup or using ALTER TABLE to re‑add the column.

Closing

Stored generated columns let you shift repetitive computation from query time to write time, enabling fast indexed access to derived values. Measure your workload’s read/write ratio, verify the index usage with EXPLAIN, and monitor storage growth to ensure the trade‑off aligns with your performance goals.