CODEWITHSUNDEEP
mysqlfull-text-search
BenOfTheNorth
BenOfTheNorth

Reputation: 2872

MySQL Fulltext rank rows higher if keyword exists, but not part of the search terms

I'm trying to tweak a full text (boolean) query. Is it possible to rank a row higher if a term is contained in a row, but not use that term as part of the search query?

So I'd like to rank a row higher if it contains the keyword cheese, but not actively search for the term cheese?

Currently I use something along the lines of searchterm >cheese*, but I believe that will still actually search for the term cheese also?

The problem this creates is if there are no results, it'll still show results based on cheese. I only want it to be used as a method of improving results, but not as something to be searched against.

black >cheese* might return rows that don't contain black in any way at all, but do contain the word cheese.

Upvotes: 1

Views: 223

Answers (1)

Bojan Dević
Bojan Dević

Reputation: 1875

You could try using +test cheese. Documentation states that the results must contain test but if they also contain cheese they are ranked higher.

An example query:

SELECT 
  MATCH (name) AGAINST ('+test cheese' IN BOOLEAN MODE) AS relevance, 
  t.* 
FROM 
  test AS t 
WHERE 
  MATCH (name) AGAINST ('+test' IN BOOLEAN MODE) 
ORDER BY relevance DESC;

You can check it at http://sqlfiddle.com/#!2/6e5f8/4

Documentation: http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

Upvotes: 1

Related Questions