Overview
Applications often filter on a value derived from another column: a normalized email, a date extracted from a timestamp, or a JSON attribute. Recomputing the expression in every query can make indexing difficult and duplicate business logic across code paths.
Start with the workload and the invariant
Generated columns let the database calculate a value from other columns. Depending on server version and expression support, a generated column may be virtual or stored. Virtual values are computed when read; stored values occupy space but can reduce repeated computation. Indexing capabilities vary, so test the exact server release.
Practical example
ALTER TABLE customers
ADD COLUMN email_normalized VARCHAR(255)
GENERATED ALWAYS AS (LOWER(TRIM(email))) STORED;
CREATE INDEX idx_customers_email_normalized
ON customers (email_normalized);
Use generated columns for deterministic transformations whose semantics belong in the database. Be cautious with locale-sensitive normalization, Unicode case folding, and changing business rules. SQL LOWER() is not necessarily equivalent to the application's full email or text normalization policy.
Trade-offs and failure modes
Generated columns can also expose JSON properties as typed values. Define behavior for missing keys, nulls, and invalid data. If the attribute is central to joins or uniqueness, a regular relational column may be clearer and easier to constrain.
Verification and rollout
Before adding one to a large table, estimate the table rebuild and index creation cost. Test inserts and updates because generated values may add write work. Confirm that the optimizer uses the index with EXPLAIN and that the expression matches application expectations.
Engineering checklist
Document the source expression and treat changes to it as schema migrations. Test backfills, null behavior, collation, and duplicate handling. A generated column is most valuable when it removes repeated, inconsistent derivation while enabling a measured query improvement.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.