Prepending an * (asterisk) to a Fulltext Search in MySQL

Question

I understand that the asterisk is a wildcard that can be appended to the end of fulltext search words, but what if my searched keyword is a suffix? For example, I want to be able to search for "ames" and have a result that contains the name "james" returned. Here is my current query which does not work because you cannot prepend asterisks to fulltext searches.

SELECT * FROM table WHERE MATCH(name, about, address) AGAINST ("*$key*" IN BOOLEAN MODE)

I would simply switch to using LIKE, but it would be way too slow for the size of my database.

Answer 1

What you could do is create another column in your database with full-text search index, this new column should have the reversed string of the column you are trying to search on, and you will reverse the search query and use it to search on the reversed column, here is how the query will look like:

SELECT * FROM table WHERE MATCH(column1) AGAINST ("$key*" IN BOOLEAN MODE) OR MATCH(reversedColumn1) AGAINST ("$reveresedkey*" IN BOOLEAN MODE)

• the first condition MATCH(column1) AGAINST ("$key*" IN BOOLEAN MODE) example: reversedColumn1==>Jmaes $reveresedkey*==>ames* will search for words that start with ames ==> no match

• the seconds condition MATCH(reversedColumn1) AGAINST ("$reveresedkey*" IN BOOLEAN MODE) example: reversedColumn1==>semaJ $reveresedkey*==>sema* will search for words that end with ames ==> we have a match

This might not be a bad idea if your text is short:

Answer 2

Can't be done due to limitation of MySQL. Values are indexed left-to-right, not right-to-left. You'll have to stick with LIKE if you want wildcards prepended to search string.