Web Development

Generated Columns in MariaDB - Keep Derived Values in One Place

Generated Columns in MariaDB - Keep Derived Values in One Place

A table stores quantity and unit_price, while the application also stores line_total. That third value looks convenient until one code path updates a price without rebuilding the total. Should the application keep coordinating all three values, or should the database derive the third from the first two?

MariaDB generated columns offer one answer. They make a same-row expression part of the table definition, so callers read a named column without independently supplying its value. This can remove one kind of duplicated state, but it does not make every calculation a good database column. The useful question is narrower: does this value have one stable, row-local definition that the schema should enforce?

What a generated column changes

MariaDB defines a generated column as a column whose value comes from an expression. It is not an ordinary writable field. An insert or update supplies the source columns, and MariaDB evaluates the generated expression.

This is different from two nearby schema features:

  • A DEFAULT supplies a value when an insert omits a column. Afterward, that column remains independently writable.
  • A CHECK accepts or rejects a row according to a condition. It does not expose a derived value for queries.
  • A generated column exposes the result of a defined expression and keeps it tied to its source columns.

That distinction matters. A generated total is not a cached number that application code must remember to refresh. It is part of the row's declared shape.

A small example

Consider an intentionally simple order-line table:

CREATE TABLE order_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  line_total DECIMAL(12,2)
    AS (quantity * unit_price) PERSISTENT,
  PRIMARY KEY (id),
  INDEX idx_line_total (line_total)
);

The application writes quantity and unit_price, not an independently calculated line_total:

INSERT INTO order_lines (quantity, unit_price)
VALUES (2, 19.95), (3, 7.50);

SELECT id, quantity, unit_price, line_total
FROM order_lines;

On MariaDB 11.8.6, this syntax was checked in an isolated temporary table. The two generated results were 39.90 and 22.50, and SHOW CREATE TABLE displayed the PERSISTENT definition as STORED. This is a limited syntax and behavior check, not evidence that the design is suitable or fast for every production table.

The example also hides real business questions. Taxes, discounts, currencies, and rounding rules can make a commercial total more complicated than multiplication. A generated column is appropriate only after those semantics are explicit. It should not turn an unresolved accounting policy into an apparently authoritative number.

VIRTUAL and PERSISTENT place the work differently

MariaDB supports two generated-column forms:

  • VIRTUAL is calculated when queried and is not stored as ordinary column data. It is the default if neither form is named.
  • PERSISTENT, for which MariaDB also accepts the alias STORED, stores the generated value in the table.

This is not a simple choice between slow and fast. A virtual column moves expression evaluation toward reads. A persistent column performs and stores the work when source data is written. Storage cost, write frequency, read frequency, expression cost, and indexing all affect the result. Without a representative workload, a universal performance recommendation would be guesswork.

There is also a semantic question before the performance question. If the result is rarely selected and cheap to calculate, a virtual column may avoid unnecessary stored data. If the value is read frequently or the expression is costly, persistent storage may be worth evaluating. In either case, measure the actual schema and workload rather than treating the keyword as an optimization switch.

An index is possible, but verify the plan

Current MariaDB documentation says indexes can be defined on both VIRTUAL and PERSISTENT generated columns. The optimizer can consider such an index in the same way it considers an index on an ordinary column. “Can consider” is important: an available index is not a promise that every query will use it.

Inspect the plan for the query shape that matters:

EXPLAIN
SELECT id, quantity, unit_price, line_total
FROM order_lines
WHERE line_total = 39.90;

The EXPLAIN reference describes fields such as possible_keys, key, access type, and estimated rows. In the small local verification, MariaDB reported access type ref with idx_line_total. With only two rows, that says the index is eligible for this query; it says nothing useful about elapsed time or production benefit.

EXPLAIN shows the optimizer's intended plan without being a workload benchmark. MariaDB's ANALYZE statement goes further by executing the statement and adding observed execution information. On real data, plan inspection and measurement belong together.

Version details can change how an expression is matched

Using the generated column name is the clearest and most portable query within MariaDB versions:

SELECT * FROM order_lines WHERE line_total >= 100.00;

From MariaDB 11.8, the optimizer can also recognize the exact expression behind an indexed virtual column in a WHERE clause and substitute the generated column. The optimizer documentation says the expression must match the column definition exactly. Before 11.8, that expression substitution is not available.

This version boundary is a reason not to hide assumptions in application queries. Naming the generated column communicates the intended schema interface, works on older releases that support the column itself, and avoids relying on expression matching. If an application deliberately uses expression substitution, its minimum MariaDB version and query plans should be part of deployment checks.

The expression has real boundaries

A generated column is designed for a value derivable from its own row. MariaDB does not support subqueries in generated expressions, and data outside the row is not a suitable dependency. Stored functions are also unsupported there. These limits prevent a generated column from becoming a hidden cross-table calculation engine.

Most legal deterministic expressions and many built-in functions are supported, but persistent or indexed results need stronger consistency. MariaDB warns that an expression whose result changes with relevant sql_mode settings can make stored or indexed values disagree with later evaluation. Its documentation gives character padding and unsigned subtraction as concrete cases and recommends explicit operations such as RTRIM() or CAST() where needed.

There are structural restrictions too. A generated column cannot be a MariaDB primary key, although both generated forms can have ordinary indexes. Some foreign-key combinations and some ALTER TABLE operations have additional limitations. The exact reference for the deployed MariaDB release should therefore be checked before a migration, not only after a development schema succeeds.

When an ordinary expression may be better

Generated columns reduce one kind of duplication, but they also add schema coupling. An ordinary expression in a query or application may be clearer when:

  • the value appears in only one report or endpoint;
  • the rule changes often and follows application releases rather than database migrations;
  • the calculation depends on other rows, other tables, external services, or request context;
  • the result must preserve the historical rule used at the time of an event;
  • database portability is a stronger requirement than a named MariaDB schema feature.

The historical case deserves special attention. Suppose a later price update should not alter an old invoice total. A generated value based on the current price columns will faithfully recalculate the current expression, which may be exactly the wrong historical meaning. Immutable transaction fields or a separate ledger design may be more honest.

A cautious decision sequence

  1. Write the rule in plain language and decide whether it depends only on the current row.
  2. Choose data types, null behavior, rounding, and overflow behavior deliberately.
  3. Ask whether the value needs a stable column name across multiple callers.
  4. Choose VIRTUAL or PERSISTENT according to where computation and storage should occur, not according to a slogan.
  5. Add an index only for a real access pattern, then inspect EXPLAIN and measure representative data.
  6. Test the migration against the exact MariaDB version and sql_mode used in deployment.
  7. Document whether the value represents current derivation or historical fact.

Conclusion

A MariaDB generated column can give one row-local rule one schema-level home. It can prevent application paths from writing contradictory source and derived values, and it can expose an indexable name for an expression. Those are useful properties, not automatic proof that the design is simpler or faster.

The safer choice starts with meaning: one stable expression, based on the current row, whose result is useful to several queries or constraints. After that come storage, indexing, version compatibility, and measurement. If the rule is contextual, historical, or cross-table, leaving it outside a generated column may be the more accurate design.

References