MySQL Functional Index and use cases.

Jun 30, 2019

Mins to Read

All

Database administrators (DBAs) constantly strive to optimize query performance. Traditional indexing on raw column values often falls short when queries involve functions like month(). This blog delves into a revolutionary feature introduced in MySQL 8.0.13 - functional indexes. We'll explore how functional indexes overcome limitations and unlock significant query speedups, even when using functions in your WHERE clauses.

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example:

 
mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` varchar(32) DEFAULT NULL,
`createdOn` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_source` (`source`), -------> Index on Column
KEY `idx_medium` (`medium`(5)) -----> Index on Column Prefix
) ENGINE=InnoDB AUTO_INCREMENT=9349478 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

In MySQL 5.7, We can create an index on generated columns (Computed based on Expressions)

From MySQL 8.0.13, It is even easier by using the feature Functional Indexing. It is also implemented as a hidden virtual column. It inherits all restrictions that apply to generated columns,

Let’s see how it can ease DBA’s life.

The app_user’s table has around 9M records and data from Sep 2018.

 
mysql>select count(*) from app_user;
+----------+
| count(*) |
+----------+
| 9280573  |
+----------+
1 row in set (1.96 sec)

mysql>select * from app_user limit 1\G
*************************** 1. row ***************************
id: 1
ad_id: 787977
source: google-play
medium: organic
campaign:
timestamp: 2018-09-04T17:39:16+05:30
createdOn: 2018-09-04 12:09:20
1 row in set (0.00 sec)

Now Let us consider a case to query the records which are created in the month of May. I have used pager md5sum for easier result verification.

 
mysql>pager md5sum
PAGER set to 'md5sum'
mysql>select * from app_user where month(createdOn)=5;
7e9e2b7bc2e9bde15504f6c5658458ab -
74322 rows in set (5.01 sec)

It took 5 seconds to fetch 74322 records. Here is the explain plan for the above query

 
mysql>explain select * from app_user where month(createdOn)=5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9176706
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

No index is used. Let us try adding an index on column createdOn to speed up this query.

 
mysql>alter table app_user add index idx_createdon(createdOn);
Query OK, 0 rows affected (44.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

Explain plan post indexing

 
mysql>explain select * from app_user where month(createdOn)=5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9176706
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)

Even after adding index, it goes for full table scan (Indexing is not used) because we are using month() function in WHERE clause it masks the index usage. And there is no improvement in query performance.

 
mysql> select * from app_user where month(createdOn)=5;
7e9e2b7bc2e9bde15504f6c5658458ab -
74322 rows in set (5.01 sec)

In this case, we need to rewrite the query replacing the date functions to use the index or else we can create a virtual column for the functional column in where clause of the query and create an index on top of it. But in MySQL 8.0, it makes our work even Simpler. We can create a functional index.

 
mysql>alter table app_user add index idx_month_createdon((month(createdOn)));
Query OK, 0 rows affected (1 min 17.37 sec)
Records: 0 Duplicates: 0 Warnings: 0

 
mysql>explain select * from app_user where month(createdOn)=5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_month_createdon
key: idx_month_createdon
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

Now it is using an optimal (functional) index and query execution time is also reduced significantly.

 
mysql>select * from app_user where month(createdOn)=5;
7e9e2b7bc2e9bde15504f6c5658458ab -
74322 rows in set (0.29 sec)

But there are a few restrictions on creating a functional index.

1) Only functions permitted for generated columns(5.7) are permitted for functional key parts.

2) Primary key cannot be included in functional key parts.

3) Spatial and full text indexes cannot have functional key parts

To drop the columns containing functional index, we need to remove index first before dropping the column else it will throw an error.

Let us try to drop the column createdOn (contains functional index).

 
mysql> alter table app_user drop column createdOn;
ERROR 3755 (HY000): Cannot drop column 'createdOn' because it is used by a functional index. In order to drop the column, you must remove the functional index.

The functional index is an interesting feature in MySQL 8.0 and a must to tried out by DBA’s.

Ready to experience the power of functional indexes and optimize your MySQL queries? Let Mydbops Manage Your MySQL Performance: Free Consultation! Contact Us Today !

Featured Image Courtesy Photo by Antoine Dautry on Unsplash

No items found.