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