Skip to content

CONCEPT Cited by 1 source

Functional index (MySQL)

A functional index (also called an expression index) is an index on the result of an expression rather than on a column directly. MySQL 8.0.13 added functional- index support via a double-parenthesis syntax that distinguishes an expression from a bare column reference:

ALTER TABLE addresses ADD INDEX address_hash_functional ((
  UNHEX(SHA2(
    CONCAT_WS('|', primary_line, secondary_line, urbanization, last_line),
    256
  ))
));
-- note the outer parentheses wrapping the expression

Internally, MySQL implements a functional index as a hidden virtual generated column with an index on it. The on-disk shape is therefore identical to explicitly declaring a generated column and indexing it:

Functional indexes are implemented as virtual generated columns by MySQL, so there is no performance difference. It's merely a preference! — Aaron Francis, Generated Hash Columns in MySQL

(Source: sources/2026-04-21-planetscale-generated-hash-columns-in-mysql.)

When to prefer each

Preference Reason
Named generated column The derived value is useful in its own right (e.g. you SELECT it, display it, join on it from application code). Human-readable schema; appears in SHOW CREATE TABLE.
Functional index The derived value is only a planner fingerprint; nobody references it outside the index. Keeps the logical schema narrow; no column to document.

For generated hash columns specifically, the named-column shape is more common in practice because the hash column is typically referenced by name in application queries (WHERE url_md5 = UNHEX(MD5(?))) — the query needs a name to reference, and an invisible functional index can't be addressed by hint syntax.

Caveat: expression must match exactly

A functional index only helps a query whose WHERE clause expression matches the index's expression byte-for-byte after parsing. Slight rewrites (argument reordering, whitespace differences after function names, alternative equivalent expressions) can silently fail to use the index. A named generated column avoids this class of bug because queries reference the column directly rather than re-stating the expression.

Historical note

Before MySQL 8.0.13, functional indexing could only be achieved by declaring a named generated column and indexing it. Many production schemas predating MySQL 8 therefore carry explicit generated columns for historical reasons even though the same effect is now available via functional indexes. Migration from named-column to functional-index is rarely worth the churn — the on-disk cost is identical and the named-column form is strictly more readable.

Seen in

Last updated · 347 distilled / 1,201 read