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.

Ss

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:

Hasin

The leading wildcard does not work on a full-text search, It's weird that there is no actual solution for that, we have to stick with some workarounds instead! I think it's because of considering performance. Final word is that the wildcard () cannot be used as a prefix. so foo will work foo or *foo won't work. – Flyboat

@Bandar, What if you need asterisk on both sides? – Maccabean

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.

Geodesy

Answer 3

You can use MATCH and LIKE in one query so you can also search substrings in a column of choice:

SELECT * FROM table WHERE col1 LIKE "%string%" OR MATCH (col1, col2, col3) AGAINST ("string");

This allows you to search substrings on some columns and match against your FULLTEXT index as well.

Veal