How does functional index in MySQL 8.0.13+ improve query performance?
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?


Please notice that columns are virtual, the indexes are as real ("materialized") as any other index. That means that indeed, if you request data from the table from a virtual column, it is generated at the moment, but if you use the index, it will be pregenerated on creation, and updated on every insert/update/delete.

That means that if you have a query such as this one:

SELECT * FROM revision WHERE substr(rev_timestamp, 5, 2) == '09'


(get all rows generated in september of any year)

You don't need to create a separate real column with substr(rev_timestamp, 5, 2), you can create a virtual one (which will take no more table space) and generate a real index on it, so you can run:

SELECT * FROM revision WHERE virtual_col == '09'


which should use the new index. Of course the index will take some space, but any index does (and modifying non-unique indexes after UPDATEs is very fast on MySQL). Also think that in most cases you don't need to index the whole column- this is very useful and optimized to index optional fields on JSON type columns without duplicating the whole, large content field.