Search code examples
mysqlfull-text-searchstemming

MySQL fulltext with stems

I am building a little search function for my site. I am taking my user's query, stemming the keywords and then running a fulltext MySQL search against the stemmed keywords.

The problem is that MySQL is treating the stems as literal. Here is the process that is happening:

  1. user searches for a word like "baseballs"
  2. my stemming algorithm (Porter Stemmer) turns "baseballs" into "basebal"
  3. fulltext does not find anything matching "basebal", even though there SHOULD be matches for "baseball" and "baseballs"

How do I do the equivalent of LIKE 'basebal%' with fulltext?

EDIT:

Here is my current query:

SELECT MATCH (`title`,`body`) AGAINST ('basebal') AS `relevance`,`id` FROM `blogs` WHERE MATCH (`title`,`body`) AGAINST ('basebal') ORDER BY `relevance` DESC
Solution

  • I think it will work with an asterisk at the end: basebal*. See the * operator on this page for more info.