CODEWITHSUNDEEP
mysqlfull-text-searchstemming
johnnietheblack
johnnietheblack

Reputation: 13340

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

Upvotes: 4

Views: 4481

Answers (3)

Mavelo
Mavelo

Reputation: 1259

IN NATURAL LANGUAGE MODE is the default mode and not compatible with stemming. Try IN BOOLEAN MODE with wildcards...

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

Example above provides clarity for people stumbling onto this question 10 years after it was asked. Topic is still relevant and benefits from complete examples 😉

Upvotes: 0

casper123
casper123

Reputation: 1766

See This link.. Stemming is not installed BY default in MySQL but you can install it your self..

http://oksoft.blogspot.com/2009/05/stemming-words-in-mysql.html

Upvotes: 0

Kaleb Brasee
Kaleb Brasee

Reputation: 51955

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

Upvotes: 6

Related Questions