Functional/Expression and Generated-Column Indexing Patterns

Learning outcomes

ServiceHub stores user email exactly as entered, but login lookups must be case-normalized. Another table stores a small JSON document whose severity field is frequently filtered. A developer coming from MySQL 8 tries CREATE INDEX idx_lower_email ON users ((LOWER(email))) and assumes MariaDB accepts the same functional-index DDL. On the current MariaDB line, that is the wrong portability assumption.

MariaDB’s practical pattern is to define a generated column whose value is a deterministic expression and index that column. From MariaDB 11.8, the optimizer can also recognize the exact indexed virtual-column expression in a WHERE condition and substitute the virtual column; from MariaDB 12.1, related recognition was improved for ORDER BY/GROUP BY. MariaDB still requires the generated-column object rather than MySQL-style direct expression-index syntax.

1. Prove the MySQL-style assumption wrong first

USE servicehub_index_lab;CREATE TABLE service_users (  user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  email VARCHAR(254) NOT NULL,  profile JSON NOT NULL) ENGINE=InnoDB;-- Intentionally use syntax seen in another product/version family:CREATE INDEX idx_lower_email_directON service_users ((LOWER(email)));

Do not memorize a specific parser error number here; exact diagnostics can evolve. The learning point is the contract: current MariaDB documentation says to create a generated/virtual column and index that column. Compatibility claims must be tested against the exact product and version rather than inferred from shared SQL heritage.

2. Generated columns turn an expression into an indexable schema object

ALTER TABLE service_users  ADD COLUMN email_norm VARCHAR(254)    COLLATE utf8mb4_uca1400_ai_ci    AS (LOWER(email)) VIRTUAL,  ADD COLUMN severity INT    AS (CAST(JSON_VALUE(profile,'$.severity') AS INTEGER)) VIRTUAL,  ADD INDEX idx_email_norm (email_norm),  ADD INDEX idx_severity (severity);INSERT INTO service_users(email,profile) VALUES ('Alice.Example@Example.COM','{"severity":2,"team":"network"}'), ('bob@example.com','{"severity":5,"team":"field"}'), ('carol@example.com','{"severity":2,"team":"network"}');SHOW CREATE TABLE service_users\GSHOW INDEX FROM service_users;

A VIRTUAL generated column computes its value when needed rather than storing the derived value as a normal field. A PERSISTENT (also accepted as STORED) generated column stores the generated result. MariaDB can index both virtual and persistent generated columns when the storage engine supports them. The index itself still consumes storage and must be maintained on writes even if the generated column is virtual.

3. On 11.8+, the optimizer can substitute the exact virtual expression in WHERE

EXPLAINSELECT user_id,emailFROM service_usersWHERE severity=2;EXPLAINSELECT user_id,emailFROM service_usersWHERE CAST(JSON_VALUE(profile,'$.severity') AS INTEGER)=2;ANALYZE FORMAT=JSONSELECT user_id,emailFROM service_usersWHERE CAST(JSON_VALUE(profile,'$.severity') AS INTEGER)=2;

On MariaDB 11.8 and later, the optimizer can recognize the exact expression used by an indexed virtual column and rewrite the condition to use that virtual column for range/ref access. This is a targeted optimizer transformation, not a general theorem prover. The expression must match the generated-column definition exactly enough for substitution.

4. Deliberately wrong: change the expression or collation and expect the same index

-- The indexed definition casts to INTEGER.EXPLAIN SELECT user_idFROM service_usersWHERE JSON_VALUE(profile,'$.severity') = '2';-- The indexed definition uses LOWER(email) with a declared collation.EXPLAIN SELECT user_idFROM service_usersWHERE UCASE(email) = UCASE('alice.example@example.com');

These predicates can be semantically related to the generated values but are not the same indexed expression. Type conversion and collation are part of the expression contract. The safe repair is either to query the generated column directly—WHERE severity=2, WHERE email_norm=LOWER(?)—or to use the exact supported expression and verify the plan on the target MariaDB version.

For string extraction from JSON, MariaDB documentation specifically warns that JSON_VALUE inherits a binary-style collation context from JSON text. If your application expects a Unicode case-insensitive collation, declare that collation on the generated column and keep query comparison semantics aligned with it.

5. Determinism is a correctness requirement, not only an optimizer preference

Indexed or persistent generated values must remain consistent when the row is written and later searched. Expressions depending on nondeterministic state, external data, session modes or mutable configuration can make that promise unsafe. MariaDB permits many generated expressions but restricts nondeterministic functions for indexed virtual or persistent generated columns.

SELECT COLUMN_NAME,DATA_TYPE,COLLATION_NAME,IS_GENERATED,GENERATION_EXPRESSION,EXTRAFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_index_lab'  AND TABLE_NAME='service_users'ORDER BY ORDINAL_POSITION;

Treat GENERATION_EXPRESSION, data type and collation as deployable schema. A migration that silently changes the expression, SQL mode or collation can change lookup semantics even when application SQL is unchanged. Keep generated-index definitions under schema migration control and validate them during upgrades.

6. VIRTUAL versus PERSISTENT is a workload decision

Do not select PERSISTENT because it sounds “faster,” or VIRTUAL because it sounds “free.” Build a representative dataset, measure write rate and query rate, and compare execution evidence. The same expression can have very different economics on a read-heavy catalog versus a write-heavy event stream.

7. Reproducible lab, verification, and production judgment

1. Create service_users and observe the intentionally unsupported direct-expression-index attempt.
2. Add virtual generated columns and indexes with explicit type/collation.
3. Use EXPLAIN on direct generated-column predicates.
4. On MariaDB 11.8+, test the exact expression and verify whether the optimizer chooses the generated-column index.
5. Change the expression or collation and observe the plan difference.
6. Record schema metadata so the generated expression is part of migration review.

The next lesson applies indexing to tokenized text search. FULLTEXT is not a B-tree over strings; it has its own token, stopword, relevance and rebuild semantics.