Understanding MySQL Functional (Expression) Indexes

This article introduces MySQL functional (expression) indexes, which are indexes built on the result of functions, operators, or expressions rather than raw column values.

Functional indexes are implemented using generated (virtual) columns that are computed on‑the‑fly and stored only as index entries. MySQL 8.0.13 added native support; earlier versions require work‑arounds.

Typical use case: indexing parts of a datetime column. By creating an index on date(log_time) , a query filtering by date can use the index and reduce scanned rows from the whole table to a single row.

Example DDL and queries demonstrate the performance improvement, showing EXPLAIN output before and after adding the functional index.

<localhost|mysql>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`log_time` datetime(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_log_time` (`log_time`)
) ENGINE=InnoDB AUTO_INCREMENT=524268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

<localhost|mysql>alter table t1 add key idx_func_index_1((date(log_time)));
Query OK, 0 rows affected (2.76 sec)

<localhost|mysql>explain select * from t1 where date(log_time) = '2100-02-02'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_func_index_1
key: idx_func_index_1
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL

When indexing JSON values, the extraction operator ->> returns a longtext, so it must be cast to a suitable type and a collation specified, e.g., cast(r1->>'$.x' as char(1) collate utf8mb4_bin) . The article shows creating such an index and the resulting execution plan.

<localhost|mysql>alter table t2 add key idx_func_index_2((cast(r1->>'$.x' as char(1)) collate utf8mb4_bin));
Query OK, 0 rows affected (0.07 sec)

<localhost|mysql>explain select * from t2 where r1->>'$.x'='a'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: idx_func_index_2
key: idx_func_index_2
key_len: 7
ref: const
rows: 1
filtered: 100.00
Extra: NULL

The article also compares functional indexes with prefix indexes, illustrating that they are not interchangeable; functional indexes require the query to use the exact expression defined.

For older MySQL versions, functional indexes can be emulated with virtual columns (5.7) or redundant columns updated manually (5.6/5.5), with recommendations on how to maintain them.