Generated columns and functional indexes

A generated column is defined by an expression over other columns of the same row. You never write it; MySQL computes it.

price_gross DECIMAL(10,2) AS (ROUND(price * (100 + vat_pct) / 100, 2)) STORED,
name_upper  VARCHAR(30)   AS (UPPER(name)) VIRTUAL

• VIRTUAL (the default) is computed when the row is read and takes no space in the row.
• STORED is computed when the row is inserted or updated and stored like a normal column.

Both can be indexed. For a VIRTUAL column, the index itself stores the computed values, so a lookup through it does not recompute anything.

1CREATE TABLE products (2  id          INT PRIMARY KEY,3  name        VARCHAR(30) NOT NULL,4  price       DECIMAL(10,2) NOT NULL,5  vat_pct     INT NOT NULL,6  price_gross DECIMAL(10,2) AS (ROUND(price * (100 + vat_pct) / 100, 2)) STORED,7  name_upper  VARCHAR(30)   AS (UPPER(name)) VIRTUAL8);9INSERT INTO products (id, name, price, vat_pct) VALUES10  (1, 'pen', 10.00, 18), (2, 'book', 25.00, 0);

1UPDATE products SET price = 12.50 WHERE id = 1;2 3SELECT p.id, p.price, p.price_gross, p.name_upper,4       gross.EXTRA AS price_gross_kind,5       upper_.EXTRA AS name_upper_kind6FROM products p7JOIN information_schema.COLUMNS gross8  ON gross.TABLE_SCHEMA = DATABASE() AND gross.TABLE_NAME = 'products'9 AND gross.COLUMN_NAME = 'price_gross'10JOIN information_schema.COLUMNS upper_11  ON upper_.TABLE_SCHEMA = DATABASE() AND upper_.TABLE_NAME = 'products'12 AND upper_.COLUMN_NAME = 'name_upper'13ORDER BY p.id;

Changing price recomputed price_gross; nobody had to remember to. information_schema.COLUMNS.EXTRA reports which kind each column is.

Making an expression searchable

The data-types lesson showed, and the dates lesson warned, that converting or wrapping a column in a function stops an index on that column from being searched. A generated column turns the expression into a column you can index. MySQL 8.0.13 added a shortcut, the functional index, which is an index on an expression with a hidden generated column behind it:

KEY idx_lower_email ((LOWER(email)))      -- note the double parentheses

1CREATE TABLE users (2  id           INT PRIMARY KEY,3  email        VARCHAR(100) NOT NULL,4  profile      JSON NOT NULL,5  email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,6  country      CHAR(2)      AS (profile->>'$.country') STORED,7  KEY idx_domain (email_domain),8  KEY idx_country (country),9  KEY idx_lower_email ((LOWER(email)))10);11INSERT INTO users (id, email, profile) VALUES12  (1, 'Ann@Example.com', '{"country": "GE"}'), (2, 'bob@test.io', '{"country": "DE"}'),13  (3, 'cy@example.com',  '{"country": "GE"}'), (4, 'dee@foo.org', '{"country": "PL"}'),14  (5, 'eve@bar.net',     '{"country": "FR"}'), (6, 'fay@baz.com', '{"country": "IT"}'),15  (7, 'gus@qux.com',     '{"country": "ES"}'), (8, 'hal@zed.com', '{"country": "US"}');16ANALYZE TABLE users;17-- Helper: show_plan(query) stores EXPLAIN FORMAT=JSON for each query.18CREATE TABLE plans (n INT AUTO_INCREMENT PRIMARY KEY, query TEXT, plan JSON);19CREATE PROCEDURE show_plan(IN q TEXT)20BEGIN21  SET @sql = CONCAT('EXPLAIN FORMAT=JSON INTO @p ', q);22  PREPARE s FROM @sql;23  EXECUTE s;24  DEALLOCATE PREPARE s;25  INSERT INTO plans (query, plan) VALUES (q, @p);26END;

1CALL show_plan('SELECT id FROM users WHERE email_domain = ''example.com''');2CALL show_plan('SELECT id FROM users WHERE SUBSTRING_INDEX(email, ''@'', -1) = ''example.com''');3CALL show_plan('SELECT id FROM users WHERE profile->>''$.country'' = ''GE''');4CALL show_plan('SELECT id FROM users WHERE LOWER(email) = ''ann@example.com''');5CALL show_plan('SELECT id FROM users WHERE email = ''ann@example.com''');6 7SELECT n, query,8       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.access_type'), '$[0]')) AS access_type,9       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.key'), '$[0]'))         AS index_used10FROM plans11ORDER BY n;

• Filtering on the generated column by name (email_domain = ...) uses its index.
• Writing the same SUBSTRING_INDEX(...) expression instead was not matched to that column here, and the table was scanned.
• The JSON path profile->>'$.country' was matched to the stored country column and its index.
• The functional index served LOWER(email) = ..., and the plain email = ... could not use it.

MySQL can substitute a generated column for an identical expression, but whether it does depends on the expression and its exact result type. The dependable habit is to query the generated column by name.

What a generated column may not do

1CREATE TABLE users (2  id      INT PRIMARY KEY,3  email   VARCHAR(100) NOT NULL,4  profile JSON NOT NULL,5  country CHAR(2) AS (profile->>'$.country') STORED6);7-- A helper that runs a statement and records the error instead of stopping.8CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);9CREATE PROCEDURE try_sql(IN stmt TEXT)10BEGIN11  DECLARE EXIT HANDLER FOR SQLEXCEPTION12  BEGIN13    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;14    INSERT INTO attempts (statement, outcome)15    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));16  END;17  SET @sql = stmt;18  PREPARE s FROM @sql;19  EXECUTE s;20  DEALLOCATE PREPARE s;21  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');22END;

1CALL try_sql('INSERT INTO users (id, email, profile, country) VALUES (1, ''a@x.io'', ''{}'', ''XX'')');2CALL try_sql('ALTER TABLE users ADD COLUMN created_day DATE AS (CURDATE())');3CALL try_sql('INSERT INTO users (id, email, profile) VALUES (2, ''b@x.io'', ''{"country": "Georgia"}'')');4CALL try_sql('INSERT INTO users (id, email, profile) VALUES (3, ''c@x.io'', ''{"country": "GE"}'')');5CALL try_sql('CREATE INDEX idx_lower ON users (LOWER(email))');6CALL try_sql('CREATE INDEX idx_lower ON users ((LOWER(email)))');7 8SELECT n, outcome FROM attempts ORDER BY n;

• You cannot supply a value for it (error 3105); leave it out or use DEFAULT.
• The expression must be deterministic and self-contained: NOW(), CURDATE() and RAND() are refused (error 3763 for CURDATE() above), and so are user variables and subqueries, with their own error numbers.
• The computed value must fit the declared type. In strict mode a JSON value too long for CHAR(2) makes the insert fail (error 1406), which is one way to enforce the shape of a JSON field.

Common mistakes

• Indexing a generated column and then filtering on the expression. It may or may not be matched; the SUBSTRING_INDEX query above scanned the table.
• Forgetting the double parentheses of a functional index. (LOWER(email)) is a syntax error (attempt 5); ((LOWER(email))) works.
• Trying to UPDATE a generated column. Update the columns it is computed from.
• Using STORED by default. VIRTUAL costs no row space; choose STORED when the expression is expensive or the column must be part of the primary key.