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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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.
Yes, in MySQL 5.7 and beyond you can create generated columns and index them.
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0