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 , and show some tricks for getting the most out of them. MySQL 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 , , and in functional indexes. This is because the result of these functions is not deterministic and, therefore, cannot be used to create an index. NOW() , UUID() RAND() How to use functional indexes in MySQL Creating a functional index To create a functional index, you need to use the statement. CREATE INDEX CREATE INDEX index_name ON table_name ((function_name(column_name))); Or you can define the index as part of the statement. CREATE TABLE 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 statement. In order to view such columns, you can perform the following query: SHOW CREATE TABLE table_name SHOW EXTENDED COLUMNS FROM table_name; And the result will look something like this. You can see column with the name . VIRTUAL GENERATED !hidden!index_name!0!0 Field Type Null Key Default Extra id bigint unsigned NO PRI null auto_increment column_name int NO 0 !hidden!index_name!0!0 bigint NO MUL 0 VIRTUAL GENERATED DB_TRX_ID NO null DB_ROLL_PTR NO null 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 in order to obtain data for that query because the condition fully matches the definition of our functional index. my_functional_index explain select * from table_name where abs(column_name) + 1 = 42; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE table_name null ref index_name index_name 8 const 1 100 null But if we try to use different conditions, for example, , MySQL will not be able to use our index. abs(column_name) + 2 Dropping a functional index To drop a functional index, you need to use the statement. It's exactly the same as for the regular index. DROP INDEX DROP INDEX index_name ON table_name; Useful tricks with functional indexes. 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)); 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))); 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)))); 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')))); 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 function. For a books table with a title column: LOWER() CREATE INDEX idx_title_lower ON books((LOWER(title))); 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 and columns: first_name last_name CREATE INDEX idx_full_name ON people((CONCAT(first_name, ' ', last_name))); 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))); 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))); 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))); 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!
