Functional indexes are found in both of Percona’s relational databases, MySQL and PostgreSQL, but they are probably the least used and most understood index type, aside from geospatial. You may also hear this type of index being called Index on expression.
So, what is a functional index?
Definitions
The examples below use MySQL, but it is helpful to check their competition’s documentation to help explain what a functional index is. PostgreSQL defines them clearly. For a functional index, an index is defined as the result of a function applied to one or more columns of a single table. Functional indexes can be used to obtain fast access to data based on the result of function calls.
MySQL’s definition is a little more convoluted, stating that in MySQL versions 8.0.13 and higher support functional key parts that index expression values rather than column or column prefix values. The use of functional key parts enables the indexing of values not stored directly in the table.
Okay, enough technical manual-ese. Functional indexes allow you to create an index based on a calculation or function to a value of a column in a table. You could index the total price of a good by adding the sales or VAT tax to get the cost of a good. Or add twenty-four hours to the checkout time of a rental item to determine the return time. The possibilities are only limited by your data needs and your imagination.
Why is this a good thing?
PostgreSQL’s manual expresses this succinctly. The index expressions are not recomputed during an indexed search since they are already stored in the index. In both examples below, the system sees the query as just WHERE indexed column = ‘constant’ and so the speed of the search is equivalent to any other simple index query. Thus, indexes on expressions are useful when retrieval speed is more important than insertion and update speed.
What was that about insert and update speed? Well, there is always a drawback to having the server do something complicated for you. In this case, the calculation on the expression must be completed before the data can be inserted into the row. That is a light overhead that could grow into something that overwhelms the server with an extremely heavy write load.
MySQL’s functional indexes are implemented as hidden virtual generated columns The virtual generated column itself requires no storage. The index itself takes up storage space as any other index.
Example one
Anyone who has used Structured Query Language (SQL) for over thirty seconds has run into a situation where the query ‘looks’ good, but its results do not, or it runs much more slowly than expected. Recently, someone contacted me about a functional index that just was not working. To demonstrate, we will start with a simple table with a datetime field. The desired result includes picking all the rows in the table where the month part of the DateTime field matches a particular month.
1 2 3 4 5 6 7 8 9 10 | SQL > create table product (id int, date_available datetime, price double, weight int); Query OK, 0 rows affected (0.0124 sec) SQL > insert into product values (1,now(),10.00,1),(32,now(),7.74,2); Query OK, 2 rows affected (0.0043 sec) Records: 2 Duplicates: 0 Warnings: 0 SQL > alter table product add index((month(date_available))); Query OK, 0 rows affected (0.0178 sec) Records: 0 Duplicates: 0 Warnings: 0 |
If we double-check that index, it looks pretty good.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL > SHOW INDEX FROM product \G *************************** 1. row *************************** Table: product Non_unique: 1 Key_name: functional_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: month(`date_available`) 1 row in set (0.0017 sec) SQL > |
Heck, the system even calls it functional_index for our benefit. Please note that future functional indexes get _n postpended to the name.
We should be good to go, but the wrong thing happens. When the query is run, the optimizer does not use the new index.
1 2 3 4 5 6 7 8 | SQL > explain select id from product where month(date_available) = '8'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | product | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.0014 sec) Note (code 1003): /* select#1 */ select `test`.`product`.`id` AS `id` from `test`.`product` where (month(`test`.`product`.`date_available`) = '8') |
Wrong month?
My first assumption was that I got the value for the month wrong. But when I checked, it was correct.
1 2 3 4 5 6 7 | SQL > select month(now()); +--------------+ | month(now()) | +--------------+ | 8 | +--------------+ 1 row in set (0.0007 sec) |
Some of you may have already guessed that the mistake was mine, but not what I thought it was. Eventually, I retyped the query, but this time, I left off the single quotes around the value of the month.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL > explain select id from product where month(date_available) = 8 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: product partitions: NULL type: ref possible_keys: functional_index key: functional_index key_len: 5 ref: const rows: 4 filtered: 100 Extra: NULL 1 row in set, 1 warning (0.0012 sec) Note (code 1003): /* select#1 */ select `test`.`product`.`id` AS `id` from `test`.`product` where (month(`date_available`) = 8) SQL > |
Casting
Sometimes, you are going to get that data with the quotes, and you need to scrap them off. In these circumstances, you can use the cast operator to do just that. And cast takes of double and single quotes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL > explain select id from product where month(date_available) = cast("8" as unsigned) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: product partitions: NULL type: ref possible_keys: functional_index key: functional_index key_len: 5 ref: const rows: 4 filtered: 100 Extra: NULL 1 row in set, 1 warning (0.0013 sec) Note (code 1003): /* select#1 */ select `test`.`product`.`id` AS `id` from `test`.`product` where (month(`date_available`) = cast('8' as unsigned)) SQL > |
Naming functional indexes
I recommend naming your functions, and, like other MySQL indexing options, you can name your functional indexes. Use a name that describes what the index is doing. This makes it easier to quickly identify than looking up the definition of functional_index_1, functional_index_2, etc., from a SHOW CREATE TABLE statement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL > alter table product add index product_shipping ( (weight * 10) ); Query OK, 0 rows affected (0.0329 sec) Records: 0 Duplicates: 0 Warnings: 0 SQL > explain select * from product where (weight * 10) > 5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: product partitions: NULL type: range possible_keys: product_shipping key: product_shipping key_len: 9 ref: NULL rows: 4 filtered: 100 Extra: Using where 1 row in set, 1 warning (0.0014 sec) Note (code 1003): /* select#1 */ select `test`.`product`.`id` AS `id`,`test`.`product`.`date_available` AS `date_available`,`test`.`product`.`price` AS `price`,`test`.`product`.`weight` AS `weight` from `test`.`product` where ((`weight` * 10) > 5) |
Conclusion
Functional Indexes, like other indexes, are a handy way to speed up queries, but be sure to test them with EXPLAIN to ensure the optimizer knows to use them.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
