CODEWITHSUNDEEP
phpmysqlsql
sss999
sss999

Reputation: 528

How to sort the results from full text index search based on relevance

Hi i have problems in sorting the results of my sql query based on the relevance.

Here is my query at present.

SELECT author FROM search WHERE MATCH(author,title,series) AGAINST('Anna Selby' in BOOLEAN MODE)

Can anyone tell me the solution as to how to sort the results based on relevance

Upvotes: 1

Views: 702

Answers (1)

Arkitecht
Arkitecht

Reputation: 166

You can select the match as part of your query, and it comes back as a "score". You can then sort on that. IE:

SELECT 
  author,
  MATCH(author,title,series) AGAINST('Anna Selby' in BOOLEAN MODE) as score
FROM 
  search 
WHERE 
  MATCH(author,title,series) AGAINST('Anna Selby' in BOOLEAN MODE)
ORDER BY
  score

UPDATE: Here is a weighted example

SELECT 
  author,
  (
   (MATCH(author) AGAINST('Anna Selby' in BOOLEAN MODE) * 20) +
   (MATCH(title) AGAINST('Anna Selby' in BOOLEAN MODE) * 10) +
   (MATCH(series) AGAINST('Anna Selby' in BOOLEAN MODE) * 5)) as score
FROM 
  search 
WHERE 
  MATCH(author,title,series) AGAINST('Anna Selby' in BOOLEAN MODE)
ORDER BY
  score

Upvotes: 4

Related Questions