MySQL Functional Index

Summary: in this tutorial, you will learn how to use the MySQL functional index to increase the query speed that involves functions and expressions.

Introduction to MySQL Functional Index

A functional index is created based on the result of an expression or function applied to one or more columns in a table.

A functional index allows you to optimize queries by indexing computed values of specific columns. It can be useful when you have queries that involve functions applied to your data.

To create a functional index, you use the CREATE INDEX statement:

CREATE INDEX index_name
ON table_name ((fn));Code language: SQL (Structured Query Language) (sql)

In this syntax:

• index_name: This is the name of the index.
• fn: This is a function or expression involving the table’s columns.

Note the double parentheses. The syntax is correct as the double parentheses are used to enclose the expression, distinguishing it from columns or column prefixes

Besides the CREATE INDEX statement, you can use the ALTER TABLE statement to add a functional index to a table:

ALTER TABLE table_name
ADD INDEX index_name((fn));Code language: SQL (Structured Query Language) (sql)

MySQL functional index example

We’ll use the orders table from the sample database:

The following finds the total orders with the order date in 2004:

SELECT 
  COUNT(*)
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| COUNT(*) |
+----------+
|      151 |
+----------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

It returns 151 orders.

The following statement uses the EXPLAIN to show how the query optimizer executes the query:

EXPLAIN 
SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  326 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the query does not use an index.

To address this, you can do two things:

First, create an index for the orderDate column:

CREATE INDEX idx_order_date 
ON orders(orderDate);Code language: SQL (Structured Query Language) (sql)

Second, change the query not to use the YEAR() function:

SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  orderDate BETWEEN '2004-01-01' 
  AND '2004-12-31';Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| COUNT(*) |
+----------+
|      151 |
+----------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The new query uses the index idx_order_date:

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_order_date | idx_order_date | 3       | NULL |  151 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The query now uses the index. However, if you use the YEAR() function, the query still cannot utilize the index.

To fix it permanently, you can create a functional index on the orderDate column that uses the YEAR() function like this:

CREATE INDEX idx_year 
ON orders((YEAR(orderDate)));Code language: SQL (Structured Query Language) (sql)

After having the functional index idx_year, the query that uses the YEAR() function will utilize the index as shown in the following query:

EXPLAIN 
SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_year      | idx_year | 5       | const |  151 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use a functional index to optimize queries by indexing the computed values of specific columns, making it faster to retrieve data.