How to Sort MySQL Fulltext Search Results by Relevancy

How to sort MYSQL fulltext search results by relevancy

SELECT * from vocabulary 
WHERE translation like 'word'  
union all
SELECT * from vocabulary 
WHERE translation LIKE '%word%' and translation not like 'word'  

will list exact matches first

MySQL fulltext search and sort by relevance + TIME

You could change to an aggregate score... something like this:

---

SELECT *, 
  (
    MATCH(title, content) AGAINST('search string')
    - 
    (ABS(DATEDIFF(`timestampfield`, NOW())) / 365)
  ) AS score 
FROM news_items 
WHERE 
  MATCH(title, content) AGAINST('search string') > 4 
ORDER BY score DESC LIMIT 4

In that there's one kinda funky addition, which you'd want to clean up:

- (ABS(DATEDIFF(`timestampfield`, NOW())) / 365)

This is your age component of the score... currently scaled by <year> = 1 point

To get that, we start by getting the number of days between the timestamp field and now (absolute value):

ABS(DATEDIFF(`timestampfield`, NOW()))

Then we scale...

I decided you probably didn't want to loose score based on number of days, because if something was 30 days old it would be -30... seems too harsh. So I chose years... if you want to scale on number of weeks, divide by 52 instead of 365... and so forth.

This scaling factor will be how you control value between scoring matching and age.

So it ends up being something like: <match score> - <yearsAgo>

---

If you do that:

1. 5 (match score) - 0.1 (<1 year ago) = 4.9 (ok match, but newest)
2. 5 (match score) - 0.01 (<1 year ago) = 4.99
3. 5 (match score) - 1 (1 year ago) = 4
4. 6 (match score) - 2 (2 years ago) = 4
5. 9 (match score) - 5 (5 years ago) = 4 (best match, but old)
6. 7 (match score) - 10 (10 years ago) = -3

---

NOTE this assumes your timestamp field is a full date-time field... if otherwise, you will need to re-cast to a date, or the logic to manipulate the unix timestamp directly.

And here's a debugging version of the query:

SELECT
    `created`,
    MATCH(title, content) AGAINST('awesome') as match_score,
    (ABS(DATEDIFF(`created`, NOW())) / 365) as years_ago,
  (
    MATCH(title, content) AGAINST('awesome')
    - 
    (ABS(DATEDIFF(`created`, NOW())) / 365)
  ) AS score 
FROM news_items 
WHERE 
  MATCH(title, content) AGAINST('awesome') > 4 
ORDER BY score DESC LIMIT 4

MySQL full text search in boolean mode order by relevance

What is your MySQL version?

I do remember that it was no no relevance in boolean mode. It was just TRUE/FALSE (1/0). I used to use boolean mode in WHERE clause and natural language mode in SELECT/ORDER clause.

You can also try to do something like this:

SET @search = 'red car';

SELECT
      *
    , MATCH(word1) AGAINST (@search IN BOOLEAN MODE) AS relevance
FROM
    translations
WHERE
    MATCH(word1) AGAINST (@search IN BOOLEAN MODE)
ORDER BY
      relevance DESC
    , LENGTH(word1) ASC
;

So if two records have the same relevance, the shorter one would be first.

Mysql fulltext boolean search - sort by relevance and one more field

You can do Left Join for it and check SQLFiddle here.

SELECT
  p.*,
       MATCH (title) AGAINST ('text' IN BOOLEAN MODE) AS score ,
       COUNT(li.id) AS total_likes 
FROM photos p
  LEFT JOIN likes li
    ON p.id = li.photo_id
     WHERE  MATCH (p.title) AGAINST ('text' IN BOOLEAN MODE) 
    GROUP BY 
      li.photo_id

ORDER BY 
      score , total_likes DESC

Give some fields more relevance and sort by relevance in mysql full text search

First, create three FULLTEXT indexes:

* one on the title column
* one on the body column
* one on both title and body columns

Then, build your query in the following manner:

SELECT field1, field2, field3, title, body,
MATCH (title) AGAINST ('word_to_search') AS rel_title,
MATCH (body) AGAINST ('word_to_search') AS rel_body
FROM table_to_use
WHERE MATCH (title,body) AGAINST ('word_to_search')
ORDER BY (rel_title*2)+(rel_body)

This will give the title 2 times more relevance than the body.

This is quite handy when you need to allow the content to be sorted, for instance, by tags (which are not viewed by the users) because it allows you to tweak the results from behind the scenes.

How to sort mysql search result by relevance?

Not the prettiest but should work!

select a.* from (
SELECT add1, add2, add3, post_town, post_code, 1 as rank FROM address_mst 
WHERE add1 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 2 as rank FROM address_mst 
WHERE add2 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 3 as rank FROM address_mst 
WHERE add3 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 4 as rank FROM address_mst 
WHERE post_town LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 5 as rank FROM address_mst 
WHERE post_code LIKE '%".$keyword."%'
) a
order by a.rank asc;

---

Related Topics