Search code examples
mysqlindexingprefix

MySQL suffix indexes

searching around trying to find a way to add index on suffix of a column I couldn't find a solution.

The only thing for partial index which I found is on prefix of index which is:

CREATE INDEX part_of_name ON customer (name(10));

from 12.1.13. CREATE INDEX Syntax

I also tried other stuff like:

CREATE INDEX part_of_name ON customer (name(-10));
CREATE INDEX part_of_name ON customer (RIGHT(name,10));
CREATE INDEX part_of_name ON customer (SUBSTRING(name,-10));

ALL with same error:

ERROR 1064 (42000): You have an error in your SQL syntax;

I would like your help! Thank you in advance!

Solution

  • MySQL doesn't support indexes on arbitrary expressions. The prefix index is only a quirk, since it's "free" in a typical implementation of B-Tree indexes.

    You'll need to add another column to the table, populate it with a trigger, and put index on that.