Quoting from 13.1.15 CREATE INDEX Syntax:
MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values.
See "Functional Key Parts" section of that manual page for detail. Basically, it enables us to create an index consisting of complex expression values derived from columns rather than mere column values (or their prefixes).
However, on a following part, that page also states:
Functional indexes are implemented as hidden virtual generated columns [...]
In 22.214.171.124 CREATE TABLE and Generated Columns, it says:
The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:
VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.
STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.
So it looks like (at least to me) the functional index is not stored at all, and only built on the fly during executing a query. Is this how functional index is used? If this is true, how could this "virtual" index improve query performance (I mean even the index itself has to be built during query)?