Boosting MySQL Query Speed with STORED Generated Columns and Secondary Indexes

Problem: Full table scans kill performance

When a query filters on a column that isn’t indexed, MySQL must read every row, which is slow on large tables. A scan can take seconds or minutes on tables with millions of rows, causing application latency and poor user experience.

Thesis: STORED generated columns combined with secondary indexes let MySQL use index access for computed lookups

The key idea is to materialize the result of a deterministic expression once per row, store it on disk, and then build a secondary index on that materialized column. With the index in place, the optimizer can satisfy a WHERE clause that references the generated column by performing a range or ref scan, avoiding a full table scan.

What is a generated column?

A generated column is a column whose value is derived from an expression that involves other columns of the same table. MySQL offers two types:

• VIRTUAL columns are computed on the fly whenever the row is read; they do not occupy extra storage.
• STORED columns are calculated once when the row is inserted or updated, and the result is written to the table’s data pages. This extra storage enables indexing.

Only STORED generated columns can have a secondary index in MySQL 8.0, because the index needs a physical value to store in its B‑tree structure.

Why STORED matters for indexing

When you create a secondary index on a STORED generated column, MySQL builds the index from the persisted values. The optimizer can then use that index for queries that filter, sort, or group by the generated column. For example, a query that searches for a lower‑cased name can use the index directly, turning an O(N) scan into an O(log N) lookup.

Because the value is stored, the index can be built once and maintained incrementally, which improves query planner choices, especially when the generated column has high selectivity (e.g., many distinct values).

Worked example: case‑insensitive name lookup

Consider a table that stores user names and you frequently search for a name without regard to case:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

The naive approach is to write the predicate as WHERE LOWER(name) = 'john'. MySQL would have to evaluate LOWER() for every row, which prevents the use of an index on name.

To eliminate the scan, add a STORED generated column that stores the lower‑cased version of the name:

ALTER TABLE users
ADD COLUMN name_lc VARCHAR(50)
GENERATED ALWAYS AS (LOWER(name)) STORED,
ADD INDEX idx_name_lc (name_lc);

The ALTER TABLE statement creates the column, computes the expression for existing rows, and then builds the index idx_name_lc in a single operation (provided the table is not extremely large and the MySQL version supports online DDL).

Now the query can be written without calling LOWER():

SELECT * FROM users WHERE name_lc = 'john';

Running EXPLAIN SELECT * FROM users WHERE name_lc = 'john' typically shows an access type of ref using idx_name_lc, confirming that the index is used instead of a full scan. This reduces I/O dramatically, especially on tables with millions of rows.

Additional practical uses

STORED generated columns are useful beyond simple case‑insensitive lookups. Common patterns include:

• Deriving a numeric total such as price * quantity for each order line, allowing index‑based range queries on the total.
• Extracting a scalar value from a JSON document, for example JSON_EXTRACT(details, '$.country'), and indexing that extracted field to speed up filters on JSON data.
• Creating a deterministic hash of a string for deduplication or sharding logic, where the hash is stored once and indexed.
• Computing a normalized version of a column (e.g., upper‑casing a varchar) to enable case‑insensitive joins without using collation tricks.

Each of these patterns benefits from the same principle: the expression is evaluated once per row, the result is persisted, and a secondary index can be built on the persisted value.

Trade‑offs and limitations

• Storage overhead: each row stores the generated value, so the table size grows proportionally to the column’s data type and length. For wide columns this can double or triple storage requirements.
• Write amplification: every INSERT or UPDATE that modifies any base column causes MySQL to recompute the generated expression and rewrite the row, increasing I/O and CPU load. The cost is proportional to the size of the generated column.
• Expression restrictions: the generated expression must be deterministic, cannot reference non‑deterministic functions (NOW(), UUID(), RAND()), cannot contain subqueries, stored functions, or user‑defined functions, and must be immutable.
• ALTER TABLE changes to the expression may trigger a table rebuild on MySQL 5.7, leading to downtime for large tables. MySQL 8.0 supports online DDL for many alterations, but the algorithm used (INPLACE vs REBUILD) still affects performance.
• Replication considerations: generated columns are replicated as part of the row image, so changes to the expression must be compatible with the replication format (ROW or STATEMENT).

Verification steps

1. Check the column definition: run SHOW CREATE TABLE users;. The output should contain GENERATED ALWAYS AS (LOWER(name)) STORED for the name_lc column.
2. Confirm the secondary index exists: execute SHOW INDEX FROM users;. Look for an entry with Column_name = name_lc and Index_type = BTREE.
3. Validate query usage: run EXPLAIN SELECT * FROM users WHERE name_lc = 'john'. The key column should show idx_name_lc and the type should be ref or range, not ALL.
4. Optionally, inspect index cardinality: SHOW INDEX STATUS FROM users; to verify that the index contains a reasonable number of distinct values, which helps the optimizer choose the index.

If any of these checks fail, the index is not being used and you may need to revisit the column definition or the query syntax.

Version considerations

MySQL 8.0 introduced full support for secondary indexes on STORED generated columns, including online DDL for adding or dropping the index. MySQL 5.7 also allows secondary indexes on STORED columns but imposes stricter syntax rules and may require a table rebuild when the generated column is added or modified. Therefore, it is safest to test the workflow on the exact MySQL version you run in production.

Monitoring and maintenance

Because STORED generated columns increase storage, monitor table size growth with SHOW TABLE STATUS or the Performance Schema. Periodically run OPTIMIZE TABLE if the table experiences heavy deletes, as this reclaims space from the generated column values. Also, consider the impact on backup size: logical backups (mysqldump) include the generated column data, so backup windows may extend.

When altering the generated expression, use ALTER TABLE ... ALGORITHM=INPLACE if possible, and test the change on a staging copy of the table. For tables larger than a few million rows, a REBUILD may be unavoidable and should be scheduled during a maintenance window.

Actionable closing

If your workload involves computed lookups, derived totals, or JSON scalar extraction, create a STORED generated column for the expression, add a secondary index on that column, and verify with EXPLAIN that the optimizer uses the index. Monitor storage growth and test any ALTER operations on a staging copy before applying them to production, especially for tables larger than a few million rows. This approach can dramatically reduce query latency while keeping the schema simple and maintainable.