Index MySQL Stored Generated Columns for Faster Expression Queries

Problem: computed expressions block index use

When a query filters or sorts on an expression such as CONCAT(first_name, ' ', last_name), MySQL must evaluate that expression for every candidate row. Because the result is not stored in a column, a standard B-tree index on first_name or last_name cannot serve the lookup, so the optimizer often chooses a full table scan. On large tables this raises latency and CPU usage.

Useful takeaway

Add a stored generated column—a column whose value MySQL computes from an expression and physically stores on insert or update—and index that column. Queries that reference the column can then use the index instead of recalculating the expression at runtime.

Prerequisites

• MySQL 5.7.6 or later, the release that introduced generated columns.
• The target table uses the InnoDB storage engine.
• Access to a MySQL client such as the mysql command-line tool.
• A database user with ALTER, INDEX, and SELECT privileges on the table.
• A staging or test copy of the table to validate behavior before changing production.

Stored means the computed value is written to disk. A virtual generated column, by contrast, is computed only when read and does not consume storage for the value itself.

Procedure: add and index a stored generated column

1. Connect to the test database

Run this in your MySQL client, connected to the database that contains the table:

mysql -u db_user -p database_name

Replace db_user with your username and database_name with the target database. You will be prompted for a password.

2. Add the stored generated column

This example uses an employees table with first_name and last_name columns. The new column full_name stores the concatenation:

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(100)
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

Required permission: ALTER on the table. Risk: on a large InnoDB table, this statement rebuilds the table and can take minutes or longer while holding metadata locks. Run it during a maintenance window or use an online schema-change tool.

3. Create an index on the generated column

CREATE INDEX idx_employees_full_name ON employees(full_name);

Required permission: INDEX on the table. Risk: index creation scans and writes to disk. Monitor disk space and replication lag if the server is a replica.

4. Rewrite queries to reference the stored column

Change application queries from the expression form to the column form:

-- Before
SELECT id, first_name, last_name
FROM employees
WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

-- After
SELECT id, first_name, last_name
FROM employees
WHERE full_name = 'John Doe';

Expected checks

Confirm the column definition

Query information_schema.COLUMNS to verify the column exists, is stored, and carries the expected expression:

SELECT COLUMN_NAME, GENERATION_EXPRESSION, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'employees'
  AND COLUMN_NAME = 'full_name';

EXTRA should contain STORED GENERATED, and GENERATION_EXPRESSION should show the concatenation expression.

Confirm the index exists

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'employees'
  AND INDEX_NAME = 'idx_employees_full_name';

Verify the optimizer uses the index

Run EXPLAIN FORMAT=JSON on the rewritten query:

EXPLAIN FORMAT=JSON
SELECT id, first_name, last_name
FROM employees
WHERE full_name = 'John Doe';

Look for the generated column index name in the access path. The older expression-based query should show a scan on base columns or a full table scan, while the column-based query should show the new index.

Validate stored values match the expression

SELECT COUNT(*) AS mismatches
FROM employees
WHERE full_name <> CONCAT(first_name, ' ', last_name)
   OR (full_name IS NULL AND CONCAT(first_name, ' ', last_name) IS NOT NULL)
   OR (full_name IS NOT NULL AND CONCAT(first_name, ' ', last_name) IS NULL);

A result of zero means every stored value agrees with the expression.

Limitations

• Storage: each stored generated column consumes disk space for every row.
• Write overhead: MySQL recomputes the expression on every INSERT and UPDATE that touches the source columns, which can slow write-heavy workloads.
• Expression changes: altering the generation expression requires a table rebuild with ALTER TABLE ... MODIFY COLUMN, which can be costly on large tables.
• Index matching: queries that still use the raw expression will not automatically use the generated-column index unless the optimizer can match the expression, which is not guaranteed.

Recovery and rollback

Because ALTER TABLE changes table state, you can reverse the change:

ALTER TABLE employees
DROP INDEX idx_employees_full_name,
DROP COLUMN full_name;

Required permission: ALTER and INDEX. Risk: dropping the column also drops any dependent indexes, views, or application code that references it. Confirm no stored procedures, triggers, or applications depend on full_name before dropping.

Practical verification after rollback

Run the original expression-based query and confirm it still returns the same rows as before the experiment. Compare query latency against your baseline to decide whether to retry the optimization with a different expression or indexing strategy.