mysql partial indexing, reverse indexing

Question

I have a table with a list of words.

word VARCHAR(16)

I need to create a reverse index. ie. word "apple" would index as "elppa", word "banana" as "ananab" and so on.

Also, is it possible to index, say, a part of the word? Such as, skip the first/last 1 or 2 characters:

pple (apple) anana (banana)

are these things possible?

Answer 1

You can create an index on a field that's limited by prefix-length, meaning that only the first n characters will be considered, you can't do it with an arbitrary start and end position though. Read more about it on mysql's CREATE INDEX documentation page.

In this case I'd just make another column, use mysql's REVERSE function to fill it in and create an index on it, that way you get a field to search on for the reverse of the original word.

Other databases such as Postgresql allow you to index an expression, which would effectively allow you to index reverse(col_name) without creating the extra column. So it it's possible, just not with mysql right now. (since version 9 potgresql has reverse() native I believe)