MySQL Full Text Search with Partial Words

MySQL - Efficient search with partial word match and relevancy score (FULLTEXT)

I obtained a good solution in this (somewhat) duplicate question a year later:

MySQL - How to get search results with accurate relevance

MySQL full text search with partial words

My understanding is that MySQL FULLTEXT indexes support searching for prefixes (MATCH (a.article_name) AGAINST ('MySQL*' IN BOOLEAN MODE)) only.

Can MySQL fulltext search be adapted to search for partial words?

Did you hear of Sphinx? It integrates nicely with MySQL, and is a powerful (and fast!) full text indexer.

You will love it.

(no they don't pay me)

How to make a multiple column mysql fulltext search where partial words are matched

In boolean mode, requiring strings to be present (instead of just scoring higher), is done with +. prefix matching is done with an ending *. This seems to be what you want, so search for:

+John* +S*
+John* +Smith*
+Smith* +J*
+Jo* +S*

Note that Full Text indexes cannot help you searching 'anywhere in a word'. so something like *mith* is bound to fail: they're meant to match from character 1 in an index.

If you also want to order them by match values, and for instance, need John Smith before Johnny Smithson, you'd do this:

 SELECT * FROM user 
 WHERE MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE)
 ORDER BY MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE) DESC;

Which you will see will get you nowhere unless you add all the words >= ft_min_word_len again separately:

+John* +S* John
+John* +Smith* John Smith
+Smith* +J* Smith
+Jo* +S*

For the last one, both are < the default 4 characters, so we can't add sorting params for that in default mysql, but you could set ft_min_world_len differently is desired.

MySQL Fulltext search query matching ALL words still returns partial matches

Well, I feel silly now. My table uses MyISAM, and according to the documentation, "more" is on the stopwords list. So that's why that search is picking up on partial matches. Thanks everyone for the help.

EDIT

If anyone is curious how to "go around" a stopwords list on shared hosting when programming your own search engine on your website, I recommend a similar technique that I used to get around my "ft_min_word_len" setting. Create a separate search column that saves a duplicate all of the values in the column or columns you wish to be searched via Fulltext. Create an include file that stores all the stopwords listed for your database type into an array. Before saving the values into your dedicated search column, loop through each individual word in your column values and check if any exist in the stopwords array using the include file. If any values include stopwords, add a character onto the stopword at the end (I chose "z"). Then when a search is triggered, loop the search terms through the same stopwords array and check to see if any include stopwords. If any search words are in the stopwords array, once again add the same character you chose to add to the end of the stopwords in your search column ("z" in this case). After looping through the array and making the necessary alterations to the search terms, you may search your dedicated search column without fear of your stopwords being ignored. Of course, I don't use my search column for any display purposes, only searching.

Fulltext search on partial word (like prefix%)

In boolean mode, you can use * as a wildcard:

select * from pdxa where match(_search ) against ('every*' in boolean mode)

Full Text Search Using Multiple Partial Words

So it was a weird syntax issue that didn't cause an error, but stopped the search from working.

I changed it to

SELECT * FROM Icd10Codes where CONTAINS(description, '"hyper*" NEAR "hea*"')

The key here being I needed double quotes " and not to single quotes. I assumed it was two single quotes, the first to escape the second, but it was actually double quotes. The above query returns the results exactly as expected.

---

Related Topics