Saturday, April 13, 2019

Putting the 'Fun' In Functional Indexes

I gave a thirty minute talk at Drupalcon this week on the features in MySQL 8.0 that would be of interest to developers and for such a short talk (and I cut slides to allow for audience questions) I could only cover the highlights. One attendee gently chastised me over no including their favorite new MySQL 8.0 feature -- functional indexes.


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.