How Functional Index in MySQL 8.0.13+ Improves Query Performance

MySQLmysql-8.0

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

Best Answer

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.

Related Solutions

Thesql dumthe where check to utilize multi column index

It is doubtful that the optimizer would choose to use this index, and if it did, MySQL would need to do a complete index scan in order to match the second entry. This would be less efficient than a table scan.

Add an index on col2.

EDIT 7/11/2012

It occurs to me that an analogy might help you understand why this can't possibly work.

Consider that an index is sort of like a phone book from ye olde times. Phone books have a compound index on (lastname, firstname).

This is a phone book:

CREATE TABLE phonebook (
  lastname varchar(255) NOT NULL,
  firstname varchar(255) NOT NULL,
  phone varchar(11) NOT NULL,
  PRIMARY KEY (lastname,firstname)
)

If you want to find my number in the book, you would find Brown, Aaron in the book which would then have the data that you want (my number). If you want to find all people with the last name 'Brown', it's also easy because they are organized by lastname - you just find the first 'Brown' entry and keep going until you are no longer looking at "Brown's." So, this works:

SELECT phone FROM phonebook WHERE lastname = 'Brown';

or even

SELECT phone FROM phonebook WHERE lastname LIKE 'B%';

What if you want to find everyone named "Aaron?" You can't use the index that the phone book provides - you have to look at every single entry in the book in order to get all the Aarons.

-- this will still have to scan every record in the table
SELECT phone FROM phonebook WHERE lastname >= 'A' AND firstname = 'Aaron';

In your example, col1 is lastname and col2 is firstname. You are trying to trick MySQL into using a phone book index by saying "Give me all the people with the first name of Aaron that also have a last name that starts with a letter." Knowing that the last name starts with a letter doesn't help you find all the Aarons, just like knowing that col1 > 0 doesn't help. You still have to look at every entry in the table.

MySQL: What points should we consider when create composite index

But, how we will decide the ordering of columns if we have many columns and many different SQL queries.

More selective columns should go first.

What are the other points which should be focused when create composite index?

Not necessary all fields on an index may be used. For example, if you have index (a, b, c) and WHERE clause looks like

WHERE a = 5 and b < 10 and c = 6

then only a and b fields may be used. A field with < or > will be the last one that's used.

What is the role of cardinality and modality of a column in creating index and composite indices?

Cardinality of a set is number of element in the set. The higher cardinality of a field values set the closer the fields should come in the composite index.

Best Answer

Related Solutions

Thesql dumthe where check to utilize multi column index

MySQL: What points should we consider when create composite index

Related Question