The Power of Functional Indexes in MySQL: A Comprehensive Guide

Introduction

Functional indexes were introduced in MySQL 8.0

Functional indexes, introduced in MySQL 8.0 in 2018, represent a significant leap forward in database optimization techniques. By allowing indexes to be created on expressions or functions of column values, they provide a very flexible tool for improving query performance, especially in scenarios involving calculated columns or complex data retrieval conditions.

In this article, I will explain what functional indexes are, how they work, how to use them in MySQL, and show some tricks for getting the most out of them.

What is a functional index?

A functional index is an index that is created based on the result of a function or expression. Take a look at the following examples:

# Creating a functional index on the result of a function
CREATE INDEX index_name ON table_name ((function_name(column_name)));

# Creating a functional index on the result of an expression
CREATE INDEX index_name ON table_name ((column_name + 1));

# Creating a functional index on the result of a function and an expression
CREATE INDEX index_name ON table_name ((function_name(column_name) + 1));

How do functional indexes work?

Functional indexes work by creating an index on the result of a function or expression. This means that the index will be created on the result of the function or expression, not on the column itself. One important note is that you cannot use volatile functions like NOW(), , UUID(), and RAND() in functional indexes. This is because the result of these functions is not deterministic and, therefore, cannot be used to create an index.

How to use functional indexes in MySQL

Creating a functional index

To create a functional index, you need to use the CREATE INDEX statement.

CREATE INDEX index_name ON table_name ((function_name(column_name)));

Or you can define the index as part of the CREATE TABLE statement.

CREATE TABLE table_name
(
    column_name int NOT NULL DEFAULT '0',
    KEY `index_name` ((function_name(`column_name`)))
);

Under the hood, MySQL will create a new virtual generated column and create an index on it. This column will not be displayed in the list of columns by default or in the result of SHOW CREATE TABLE table_name statement. In order to view such columns, you can perform the following query:

SHOW EXTENDED COLUMNS FROM table_name;

And the result will look something like this. You can see VIRTUAL GENERATED column with the name !hidden!index_name!0!0.

Using a functional index in a query

Let's use the following table, for example.

CREATE TABLE table_name
(
    id          bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    column_name int             NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    KEY my_functional_index (((ABS(column_name) + 1)))
);

And execute the following query:

select * from table_name where abs(column_name) + 1 = 42;

MySQL will use my_functional_index in order to obtain data for that query because the condition fully matches the definition of our functional index.

explain select * from table_name where abs(column_name) + 1 = 42;

But if we try to use different conditions, for example, abs(column_name) + 2, MySQL will not be able to use our index.

Dropping a functional index

To drop a functional index, you need to use the DROP INDEX statement. It's exactly the same as for the regular index.

DROP INDEX index_name ON table_name;

Useful tricks with functional indexes.

1. Indexing on Calculated Columns

Suppose you have a table of sales with columns price (the price of an item) and quantity (the number of items sold). And you often run queries to calculate the total sale amount (price * quantity). A functional index on this expression can speed up these queries.

CREATE INDEX idx_total_amount ON sales((price * quantity));

2. Indexing on Date Parts

You can use functional indexes to improve the performance of selecting date parts such as month or year. For example, in a table of events with a datetime column event_date, you often filter by the year:

CREATE INDEX idx_event_year ON events((YEAR(event_date)));

3. Indexing on String Manipulation

Functional indexes are handy when you frequently perform operations like extracting substrings. For instance, if you have a users table with an email column and you often search for users based on the domain part of the email, a functional index can be created on the substring after the '@' character:

CREATE INDEX idx_email_domain ON users((SUBSTRING(email FROM LOCATE('@', email))));

4. Indexing on JSON Data

In a table with JSON data, you might frequently access a specific value within the JSON object. For example, in a products table with JSON column attributes, you often need to filter products based on a specific attribute like "color":

CREATE INDEX idx_color ON products((JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))));

5. Indexing on Case-Insensitive Search

If you need to perform case-insensitive searches on a text column frequently, a functional index can be created using the LOWER() function. For a books table with a title column:

CREATE INDEX idx_title_lower ON books((LOWER(title)));

6. Indexing Concatenated Columns

In scenarios where you frequently concatenate two or more columns, a functional index can be created on the concatenated result. For example, in a people table with first_name and last_name columns:

CREATE INDEX idx_full_name ON people((CONCAT(first_name, ' ', last_name)));

7. Indexing on Normalized Data

In situations where you want to search on a normalized version of a string (like removing accents), you can create an index on this normalized version. For a table international_customers with a name column:

CREATE INDEX idx_normalized_name ON international_customers((CONVERT(name USING ascii)));

8. Indexing on Hashed Values

If you query a large text column with exact matches, it can be more efficient to index on a hash of the text:

CREATE INDEX idx_hashed_big_text ON books((MD5(big_text_column)));

This approach might be especially useful in case you need to create a unique index on big text columns.

CREATE UNIQUE INDEX unique_idx_hashed_big_text ON books((MD5(big_text_column)));

10. Indexing for Advanced Date Queries

For complex date queries, like extracting the week number and year, a functional index can improve efficiency:

CREATE INDEX idx_week_year ON events((YEAR(event_date)), (WEEK(event_date)));

11. Optimizing Boolean Expressions

For tables where queries frequently involve boolean expressions, creating a functional index on the expression can improve performance. For example, in a products table where you often filter by items that are both in stock and on sale:

CREATE INDEX idx_in_stock_on_sale ON products(((in_stock = 1 AND on_sale = 1)));

Conclusion

In this article, we have explained what functional indexes are, how they work, and how to use them in MySQL. We have also shown some tricks for getting the most out of them. I hope you found this article helpful and that it will help you improve your database performance. If you have any questions or comments, please leave them below!