Generated Columns and Functional Indexes in MySQL

2020-03-02 · mysql, indexing, schema

I've lost count of how many times I've looked at a slow WHERE LOWER(email) = 'x', WHERE YEAR(created_at) = 2019 or WHERE data->>'$.sku' = 'A1' and had to explain that an index on the underlying column can't help, because the optimizer sees a function, not a column. The old fix was a real column filled by a trigger or application code. MySQL 5.7 introduced generated columns, which the server maintains for you, and MySQL 8.0.13 added functional indexes, which are generated columns you don't even have to name.

The syntax below works on 5.7, with the shorter form for 8.0.13+. Generated columns need 5.7.6+ and InnoDB. MariaDB 10.2+ has them too (called "virtual" and "persistent") with slightly different syntax, but no functional key parts.

A virtual generated column

A VIRTUAL column is computed on read and takes no space in the row:

ALTER TABLE customers
  ADD COLUMN email_lower VARCHAR(191) GENERATED ALWAYS AS (LOWER(email)) VIRTUAL;

The ALTER is an in-place metadata change on 5.7 because nothing is stored. Now index it:

ALTER TABLE customers ADD INDEX idx_email_lower (email_lower);

InnoDB stores the computed value in the secondary index, so the lookup is a regular index seek. The application does not have to change its query as long as the expression matches exactly:

EXPLAIN SELECT id FROM customers WHERE LOWER(email) = 'jane@example.com';

On 5.7 and 8.0, the optimizer recognises that LOWER(email) is the expression behind email_lower and uses idx_email_lower. key in the EXPLAIN output should show the index name.

Stored columns, when they make sense

A STORED column is computed on write and occupies space, like a normal column. Use it when the expression is expensive and read far more often than written, or when you need it in a PRIMARY KEY or as a partitioning key, which virtual columns cannot be:

ALTER TABLE orders
  ADD COLUMN order_year SMALLINT GENERATED ALWAYS AS (YEAR(created_at)) STORED,
  ADD INDEX idx_year_customer (order_year, customer_id);

Adding a STORED column rebuilds the table, so on large tables do it with pt-online-schema-change or during a maintenance window.

Indexing JSON attributes

This is where I use generated columns most since 5.7's JSON type arrived. To find products by an attribute stored inside a JSON document:

ALTER TABLE products
  ADD COLUMN sku VARCHAR(32) GENERATED ALWAYS AS (attrs->>'$.sku') VIRTUAL,
  ADD UNIQUE INDEX idx_sku (sku);

SELECT id FROM products WHERE attrs->>'$.sku' = 'A1-778';

The ->> operator (unquoting extract) was added in 5.7.13; on earlier 5.7 write JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.sku')). Note the cast: ->> returns a string, so the generated column is VARCHAR. For a numeric attribute cast explicitly, CAST(attrs->>'$.price' AS DECIMAL(10,2)), otherwise the comparison in WHERE will not match the column's type and the index goes unused.

Functional indexes on MySQL 8.0.13+

On 8.0.13 and later, the generated column can be skipped and the expression put directly into the index definition, in parentheses:

ALTER TABLE customers ADD INDEX idx_email_lower ((LOWER(email)));
ALTER TABLE orders ADD INDEX idx_month ((DATE_FORMAT(created_at, '%Y-%m')));
ALTER TABLE products ADD UNIQUE INDEX idx_sku ((CAST(attrs->>'$.sku' AS CHAR(32)) COLLATE utf8mb4_bin));

Behind the scenes MySQL creates a hidden virtual column with a generated name (!hidden!idx_email_lower!0!0) and indexes it, so the behaviour and the restrictions are identical to a virtual column. The double parentheses are required to distinguish an expression from a column name. For JSON on 8.0.17 and later, CAST(... AS ARRAY) multi-valued indexes allow indexing every element of a JSON array, but that is a separate topic.

Is the index really used?

SHOW CREATE TABLE customers\G
EXPLAIN FORMAT=JSON SELECT id FROM customers WHERE LOWER(email) = 'jane@example.com'\G

SHOW CREATE TABLE lists the generated column with its expression, or on 8.0 the functional key part. In the EXPLAIN output look for "key": "idx_email_lower" and "used_key_parts". Confirm with handler counters that no scan happens:

FLUSH STATUS;
SELECT id FROM customers WHERE LOWER(email) = 'jane@example.com';
SHOW SESSION STATUS LIKE 'Handler_read%';

Handler_read_key should be 1 and Handler_read_rnd_next 0.

Errors and traps

ERROR 3102 (HY000): Expression of generated column 'email_lower' contains a disallowed function.: the expression is non-deterministic (NOW(), RAND(), UUID()) or uses a user variable or subquery. Generated columns must be deterministic and depend only on the row.
ERROR 3105 (HY000): The value specified for generated column 'sku' in table 'products' is not allowed.: an INSERT or UPDATE tried to set the column. Either omit it, or use DEFAULT as the value.
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.: virtual columns cannot be part of the primary key. Make it STORED.
The index exists but EXPLAIN still shows a full scan: the query expression differs from the column's expression. LOWER(email) and LCASE(email) are treated as the same, but LOWER(TRIM(email)) is not; the collation or type of a CAST must also match exactly. When in doubt, query the generated column by name.
Replicating to an older slave: a 5.7 master with generated columns cannot replicate the CREATE TABLE to a 5.6 slave, which fails with a syntax error. Upgrade slaves first, as always.

I default to VIRTUAL and only go STORED when I have a reason. And keep the WHERE expression identical to the definition; that alone fixes most "why isn't it using my index" questions.