1

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 13.1.20.8 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)?

  • 1
    Short answer: indexes always will be stored even on virtual generated columns. – Raymond Nijland Nov 28 '18 at 15:57
2

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.

  • After reading your answer and Raymond’s comment, I’m wondering maybe this line “functional indexes are implemented as hidden virtual generated columns” in the doc is trying to say when you CREATE INDEX i ON tb ((col1 + col2)), a hidden virtual generated column representing col1+col2 is created along with the real (stored) index of the same expression. Is that right? – Naitree Nov 28 '18 at 17:04
  • 1
    Sure, consider sending feedback through a mysql bug report bugs.mysql.com about the documentation, they are extremely responsive there and happy to get sent documentation bugs if they are not 100% clear! I personally understood it but I guess it can be confusing? – jynus Nov 28 '18 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.