The query was honest and hopeless: WHERE JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.carrier')) = 'dhl' against forty million shipment rows, scanning every one of them and parsing JSON per row. Eighteen seconds at peak, and the table grew a million rows a week. The fix was one generated column and one index, and the query dropped to single-digit milliseconds. Generated columns are the most underused feature in MariaDB, and the difference between VIRTUAL and PERSISTENT is the difference between a cheap trick and a production index.
What is the difference between VIRTUAL and PERSISTENT columns?
A generated column computes its value from an expression over other columns in the same row. Declared VIRTUAL — the default — the value is computed when the row is read and stored nowhere. Declared PERSISTENT, which is MariaDB's keyword where MySQL says STORED, the value is computed when the row is written and materialized on disk like any normal column. The trade is exactly that: VIRTUAL costs CPU at read time and zero storage; PERSISTENT costs storage and write-time CPU, and gives reads a plain column back. Neither is universally better. A dimension you read once in a monthly report is virtual material. A key you filter or join on every request wants to be persistent — or at least indexed, which changes the calculus in the next section.
ALTER TABLE shipments
ADD COLUMN carrier VARCHAR(32)
AS (JSON_VALUE(attrs, '$.carrier')) PERSISTENT,
ADD INDEX idx_shipments_carrier (carrier);
-- same idea, zero storage, recomputed on every read:
ALTER TABLE shipments
ADD COLUMN item_count INT
AS (JSON_LENGTH(attrs, '$.items')) VIRTUAL;When can you index a generated column in MariaDB?
More often than the folklore says. InnoDB supports secondary indexes on VIRTUAL generated columns, not just persistent ones — the index itself stores the computed values and maintains them on every write, so you get index-speed lookups without paying table storage for the column. That makes indexed-VIRTUAL the sweet spot for most JSON extraction: the row stays lean, the index does the work, and queries resolve through the index without re-parsing anything. One version caveat with real teeth: only MariaDB 11.8 taught the optimizer to match a repeated copy of the expression to the indexed column, so on 10.11 and the earlier 11.x releases the query must reference the column by name — WHERE carrier = 'dhl', not the JSON expression — or the index sits unused. PERSISTENT earns its keep when the engine is not InnoDB, when you want the values physically present for tools that read raw tables, or when you simply prefer that the read path never evaluates the expression at all.
The hard limits are worth memorizing before you design around them. A generated column cannot be the primary key — try it and you get ERROR 1903, primary key cannot be defined upon a computed column — so synthetic keys still need a real column underneath. Foreign keys are pickier than the folklore says in the other direction: VIRTUAL generated columns cannot participate at all, while PERSISTENT ones are supported on either side of a foreign key, minus a few referential actions — no ON UPDATE CASCADE, and no SET NULL on update or delete. And the expression may reference only columns of the same row: no subqueries, no data outside the row. It may build on another generated column as long as that column is defined earlier in the table, and it may even call nondeterministic functions like NOW() or RAND() in a VIRTUAL column you never index — but PERSISTENT and indexed columns must be deterministic, full stop. Design inside that fence and the feature is dependable; lean on the fence and you find out at DDL time, which is the polite outcome.
How do you extract JSON into a fast indexed column?
The pattern that has paid for itself across three schemas now: add a persistent or indexed-virtual column whose expression pulls one scalar out of the JSON document, type it deliberately, and index it. Use JSON_VALUE for scalars — it extracts and unquotes in one call, which matters far more than it looks. JSON_EXTRACT returns the value with its JSON quotes still on, so an index built on it will not match a WHERE clause comparing against a bare string; half the "my generated column index is being ignored" reports I have debugged were exactly that quote mismatch. Cast the result to the type you will actually compare with, keep it short enough to index comfortably, and make the column's collation match how the application queries.
Do it surgically: one column per real access path, not a dumped mirror of the entire document. Every persistent generated column is write amplification — it is recomputed and stored on every INSERT and every UPDATE that touches its source — and every index on top is more write cost still. The shipments table got two extracted columns, not twelve, and the two were chosen from the slow query log, not from a whiteboard.
How does MariaDB compare to MySQL's functional indexes?
MySQL 8.0.13 added functional key parts — CREATE INDEX on an expression directly, no column required. As of the current MariaDB 11.x releases there is no equivalent; the generated column is the functional index. That is a genuine ergonomics win for MySQL: one statement instead of two, and no extra column in the table definition. But the MariaDB route has an underappreciated advantage — the extracted column is addressable in SQL. You can SELECT carrier, GROUP BY carrier, and let application code read the value without repeating the JSON expression in every query and hoping the optimizer recognizes each repetition as the same indexable thing. Having lived with both, I now prefer the explicit column even where both engines are on the table, because ORMs and analysts alike handle "a column" far better than "an expression the optimizer might match".
What does adding a generated column cost, and what breaks on replicas?
The ALTER cost splits cleanly by type. Adding a VIRTUAL column touches no row data — it returns as fast as the metadata lock allows, even on billion-row tables. Adding a PERSISTENT column materializes a value for every existing row, which means a full table rebuild with the lock and duration that implies; on large tables you schedule it like any other big ALTER, off-peak, with a rehearsed estimate from a restored copy. Adding an index to an existing virtual column is an index build — online in the INPLACE sense, but never free on a hot table. The cheap sequence for a production JSON extraction is therefore: virtual column first, which is instant; index second, which is bounded; convert to persistent only if profiling shows the read-time evaluation actually hurting.
Replication adds one non-obvious constraint: determinism is not just a DDL-time rule, it is a correctness rule downstream. With statement-based binlogging the replica re-evaluates the expression itself; with row-based binlogging the computed values of persistent columns travel inside the row events. Both are safe only if the expression truly is deterministic. The server enforces this for built-in functions at definition time, but it cannot inspect a user-defined function — loadable UDFs carry no DETERMINISTIC declaration to begin with, so the server simply trusts you — and a UDF that secretly is not deterministic will make statement-based replicas compute different values than the primary wrote, silently, until a checksum or a confused analyst finds the drift. Keep generated-column expressions to plain built-ins, and if statement-based or mixed binlogging runs anywhere in the chain, run a periodic CHECKSUM TABLE across primary and replica on the generated columns specifically.
Where MonPG fits
The signals that matter around generated columns are the same ones that matter around any index: write latency on the base table, slow-query share still doing full scans, and replication lag on chains that recompute expressions. Disclosure, as always in this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and query-shape regressions like the eighteen-second scan that opened this article are precisely the class of problem it is being built to surface early. Until that ships, the slow query log plus the indexing pattern above will carry you. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview and the blog.