MySQL Function Indexes: Scenarios, Implementation, and Performance Comparison

MySQL function indexes, also known as expression indexes, are built on virtual columns introduced in MySQL 5.7 and fully supported in MySQL 8.0, allowing indexed expressions without altering table structures.

Typical scenarios covered:

Date part filtering: WHERE DATE(log_time) = CURRENT_DATE

Arithmetic on two integer fields: WHERE rank1 + rank2 = 121

Extracting a substring from a column: WHERE SUBSTR(field4,5,9) = 'actionsky'

Matching the suffix of a string: WHERE RIGHT(field4,9) = 'actionsky'

Retrieving a JSON value: WHERE CAST(field4->>'$.name' AS CHAR(30)) = 'actionsky'

Without function indexes, each scenario would require either complex WHERE clauses, additional redundant columns, or full‑table scans, leading to higher maintenance and slower queries.

Index creation examples (MySQL 8.0):

ALTER TABLE t_func ADD INDEX idx_log_time ((DATE(log_time)));
ALTER TABLE t_func ADD INDEX idx_u1 ((rank1 + rank2));
ALTER TABLE t_func ADD INDEX idx_suffix_str3 ((RIGHT(str3,9)));
ALTER TABLE t_func ADD INDEX idx_substr_str1 ((SUBSTR(str1,5,9)));
ALTER TABLE t_func ADD INDEX idx_str2 ((CAST(str2->>'$.name' AS CHAR(9))));

These indexes automatically generate hidden virtual columns, which can be inspected with SHOW EXTENDED CREATE TABLE .

Execution plan comparison:

For the arithmetic scenario, the plan using the function index shows type: ref and possible_keys: idx_u1 , scanning only 878 rows, whereas a rewritten query that does not match the index definition falls back to type: ALL (full scan) with 16,089 rows examined.

Similarly, a date‑range query using a normal index on log_time results in a range access type, while the function index version uses a ref on the virtual column CAST(log_time AS DATE) . The CPU cost is slightly lower for the function index.

Key observations:

Function indexes simplify query rewriting and improve performance for expression‑based filters.

The optimizer can only use them when the query exactly matches the indexed expression.

Regular indexes may still be useful for simple column predicates, but function indexes often provide better CPU efficiency.

Overall, MySQL 8.0’s function indexes make complex query optimizations easier, though developers must adhere strictly to the defined expression syntax to benefit from them.