Is there a way to optimally index reversed varchars in MySQL-CodePudding

I know that databases are able to optimally use an index for queries WHERE x like "foo%".
But in theory, if you will reverse the text and store the reversed string in the index,
then that index could be used to optimally search WHERE x like "%foo"

Is there any way to create such inversed index in MySQL or any other database?

CodePudding user response：

In theory (ie I haven't done this) the first step would be to create a derived column for the reversed text:

reversed varchar(128) GENERATED ALWAYS AS REVERSE(normal)

Then you would declare an index on it. I'm not certain MySQL allows indexes on derived columns (I know MsSQL does) but try it and see.

CodePudding user response：

Yes, in MySQL 5.7 and beyond you can create generated columns and index them.

For example:

CREATE TABLE phrase (
  word VARCHAR(128),
  reversed_word VARCHAR(128) GENERATED ALWAYS AS (REVERSE(word)) VIRTUAL,
  INDEX worddex (word),
  INDEX reversedex (reversed_word)
)
COLLATE=utf8mb4_general_ci;

Then you can search something like this. Try it.

SET @search_for := 'hello';
SELECT word FROM phrase
WHERE (     word LIKE CONCAT(@search_for, '%')
         OR reversed_word LIKE CONCAT(REVERSE(@search_for), '%')
      );

This can be quite a helpful trick for a general search feature. For example, if a users knows the last few digits of an account number this will find it.