Materializing Expressions in MySQL with Stored Generated Columns

The problem is a function in the WHERE clause

You need case-insensitive email lookups or range scans on a date part, and the query uses LOWER(email) or DATE(created_at). In MySQL 5.7+ that predicate forces a full scan because the optimizer cannot use a normal index on email or created_at when the column is wrapped in a function. The application can normalize before writing, but that pushes consistency into code and drifts over time.

The useful takeaway is to let the schema own the derived value. A stored generated column materializes a deterministic expression on disk, can be indexed, and updates automatically on INSERT and UPDATE. For OLTP tables where the same expression is filtered or ordered repeatedly, that trades a little write cost for predictable read performance.

Stored versus virtual, and why storage matters for OLTP

MySQL has supported generated columns since 5.7. A virtual column is computed on read and occupies no extra on-disk row space. A stored column is physically persisted and participates in the row.

Historically, secondary indexes on virtual columns were limited, so stored is the practical choice when you need an indexable expression. The expression must be deterministic and cannot reference non-deterministic functions, subqueries, or other generated columns in a cycle. Creation fails otherwise.

Stored columns increase row size and write amplification. Every INSERT and UPDATE on a base column rewrites the generated value and maintains any indexes on it. Avoid stored columns for rarely queried expressions or for high-churn tables where the derived value is cheap to compute at read time.

Design pattern: normalize once, index the result

A common OLTP pattern is to add a stored column for a normalized search key and index it. The definition lives in schema, so all clients see the same value without duplicating logic.

Example for case-insensitive email lookup. Run this as a user with ALTER privilege on the target database. Replace placeholders with your schema and table names.

ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(255)
    AS (LOWER(email)) STORED,
  ADD INDEX idx_users_email_lower (email_lower);

After the change, queries can filter on the generated column directly:

SELECT id, email FROM users WHERE email_lower = LOWER(?);

The optimizer can use idx_users_email_lower because the predicate references a real indexed column, not a function applied to email.

A similar pattern works for date parts. If you frequently query by day:

ALTER TABLE events
  ADD COLUMN event_day DATE AS (DATE(event_ts)) STORED,
  ADD INDEX idx_events_day (event_day);

Query with WHERE event_day = '2026-10-01' instead of WHERE DATE(event_ts) = '2026-10-01'.

Trade-off and verification

The main cost is write amplification and larger rows. Each update to email or event_ts recomputes the generated column and updates its index. On tables with heavy write traffic, measure the impact before deploying widely.

Version behavior changes the calculus. MySQL 8.0 added functional secondary indexes in some contexts, which can reduce the need for a stored column in newer deployments. Check your version and test plans accordingly.

Verify the change without assuming it works.

• Confirm definition: run SHOW CREATE TABLE users; look for email_lower AS (LOWER(email)) STORED and the index definition.
• Check plan: run EXPLAIN SELECT ... WHERE email_lower = ... and confirm the key column is idx_users_email_lower.
• Check correctness: insert a row with email = '[contact removed]' and select email_lower. Update email to '[contact removed]' and select again to confirm the generated value updates automatically.

If you need to revert, drop the column and index. This requires ALTER privilege and will rewrite the table in MySQL 5.7.

ALTER TABLE users DROP INDEX idx_users_email_lower, DROP COLUMN email_lower;

Use stored generated columns when a deterministic expression is a hot filter or sort key, the write cost is acceptable, and centralizing the definition reduces drift. For cold or ad-hoc expressions, keep them virtual or compute in the application.