Is it possible to have function-based index in MySQL?

Question

I recall in Oracle it is possible to index based on a function, e.g. SUBSTRING(id,1,8).

Does MySQL support this? If not, is there is any alternative?

Answer 1

No, not in a general sense, I don't believe even 5.6 (the latest version when this answer was first written) has this functionality. It's worth noting that 8.0.13 and above now support functional indexes, allowing you to achieve what you need without the trigger method described below.

If you are running an older version of mysql, it is possible to only use the leading part of a column (this functionality has been around for a long time), but not one starting at the second or subsequent characters, or any other more complex function.

For example, the following creates an index using the first five characters of a name:

create index name_first_five on cust_table (name(5));

For more complex expressions, you can achieve a similar effect by having another column with the indexable data in it, then using insert/update triggers to ensure it's populated correctly.

Other than the wasted space for redundant data, that's pretty much the same thing.

And, although it technically violates 3NF, that's mitigated by the use of triggers to keep the data in sync (this is something that's often done for added performance).

Answer 2

MySQL supports this since 8.0.13

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

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. Examples:

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);

An index with multiple key parts can mix nonfunctional and functional key parts.

For versions before 8.0.13 there are the following alternatives:

1. Since MySQL 5.7.6

You can use an auto generated column to hold the substring with an index on it:

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) AS SUBSTRING(id, 1, 8) STORED, INDEX(sub_id)
)

As Benjamin noted, InnoDB supports secondary indexes on virtual columns so the STORED keyword can be ommitted. In fact secondary indexes on virtual columns may be preferable. More info here: Secondary Indexes and Generated Columns

2. Before MySQL 5.7.6

You can use a column updated by a trigger with an index on it:

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) , INDEX(sub_id)
);

CREATE TRIGGER TR_SomeTable_INSERT_sub_id
    BEFORE INSERT
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

CREATE TRIGGER TR_SomeTable_UPDATE_sub_id
    BEFORE UPDATE
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

Answer 3

This is possible as of MySQL 5.7.5 using the new Generated Columns.

Answer 4

Yes, we can create functional index in MySQL. This feature is available from MySQL 8.0.13. (Other RDBMS have this feature in its earlier releases But MySQL introduced it in its release 8.0.13). Functional Index

Here I have given an example for creating functional index in MySQL8.0

This is the query

SELECT * FROM app_user WHERE month(createdOn) = 5;

mysql> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (5.01 sec)

It is executing for more than 5 sec to fetch 74322 records even I am having index on createdOn column.(Indexing on createdOn column is not used by optimiser as it is masked by month() function)

Now I have created a functional index on the column using the below syntax.

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> SELECT * FROM app_user WHERE month(createdOn) = 5;
7e9e2b7bc2e9bde15504f6c5658458ab - 74322 rows in set (0.29 sec)

After creating the functional index, it is executing in 0.29 secs.

It is difficult to find functional index document in Mysql website, it is in the name of functional key parts

And We cannot drop the column which is having functional index. To achieve, we need to drop the functional index in the first place.

mysql> ALTER TABLE app_user DROP COLUMN createdOn;
Cannot drop column 'createdOn' because it is used by a functional
index. In order to drop the column, you must remove the functional
index.

If you are using MySQL > 5.7.5, you can achieve the same using generated columns.