What is a Functional Index?
The manual says MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table.
There are some cool examples in the documentation on setting up some functional indexes, as can seen below.
CREATE TABLE t1 (
col1 INT, col2 INT,
INDEX func_index ((ABS(col1)))
);
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
But there are no example queries or examples with query plans provided. So let us add some data.
select * from t1 where (col1 + col2) ;
+------+------+
| col1 | col2 |
+------+------+
| 10 | 10 |
| 20 | 11 |
| 30 | 12 |
| 40 | 15 |
| 50 | 18 |
+------+------+
5 rows in set (0.0008 sec)
And then lets look at a query plan.
explain select * from t1 where (col1 + col2) > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx1
key: idx1
key_len: 9
ref: NULL
rows: 3
filtered: 100
Extra: Using where
1 row in set, 1 warning (0.0006 sec)
Note (code 1003): /* select#1 */
select `so`.`t1`.`col1` AS `col1`,
`so`.`t1`.`col2` AS `col2`
from `so`.`t1`
where ((`so`.`t1`.`col1` + `so`.`t1`.`col2`) > 40)
explain select * from t1 where (col1 * 40) > 90\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: functional_index
key: functional_index
key_len: 9
ref: NULL
rows: 5
filtered: 100
Extra: Using where
1 row in set, 1 warning (0.0006 sec)
Note (code 1003): /* select#1 */
select `so`.`t1`.`col1` AS `col1`,`so`.`t1`.`col2` AS `col2`
from `so`.`t1` where ((`so`.`t1`.`col1` * 40) > 90)
It is interesting to note that in the above case we are told the key's name is 'functional_index' (this is the one created by the ALTER TABLE command and not explicitly given a name).
Implementation
Functional indexes are implemented as hidden virtual generated columns which means you will have to follow the rules for virtual generated columns And while this functional index takes up no space because virtual generated columns are only computed at select time the index itself does take up space.
Be sure to read the manual for all the restrictions and limitations.
Do They Work With JSON Data?
Well, yes, functional indexes do work with JSON data. Once again the manual provides an interesting example.
CREATE TABLE employees (
data JSON, INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin)) ); INSERT INTO employees VALUES ('{ "name": "james", "salary": 9000 }'), ('{ "name": "James", "salary": 10000 }'), ('{ "name": "Mary", "salary": 12000 }'), ('{ "name": "Peter", "salary": 8000 }');
EXPLAIN SELECT * FROM employees WHERE data->>'$.name' = 'James';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: ref
possible_keys: idx
key: idx
key_len: 123
ref: const
rows: 1
filtered: 100
Extra: NULL
1 row in set, 1 warning (0.0008 sec)
Note (code 1003): /* select#1 */
select `so`.`employees`.`data` AS `data`
from `so`.`employees`
where ((cast(json_unquote(json_extract(`so`.`employees`.`data`,_utf8mb4'$.name'))
as char(30) charset utf8mb4) collate utf8mb4_bin) = 'James')
And we can see that it does use the index idx that was defined.
States
One of the examples you will see from other databases for functional indexes include
forcing lower case names. So I created a table with US state names.
create table states (id int, name char(30), primary key (id));
I did find that the syntax was a little fussy on creating the index as it needed an extra set of
parenthesis more than I originally thought it would
create index state_name_lower on states ((lower(name)));
explain select name from states where name = 'texas'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: states
partitions: NULL
type: ref
possible_keys: state_name
key: state_name
key_len: 121
ref: const
rows: 1
filtered: 100
Extra: Using where; Using index
1 row in set, 1 warning (0.0008 sec)
Note (code 1003): /* select#1 */
select `so`.`states`.`name` AS `name`
from `so`.`states` where (`so`.`states`.`name` = 'texas')
So please give functional indexes a try and please let me know if you find an interesting or unusual way to use them.
