Mysql count matches for each row and show result order by relevance

Question

I want to show result in order that rows have more matches should come on top.

Like if i search for keyword 'abc' in title and content, then it count the total matches for 'abc' in title and content for each row.

Suppose it found

3 matches in 1st row,
5 matches in 2nd row,
2 matches in 3rd row

Then result will show 5 matches row first, after that 3 matches row, and so on in DESC order

I used this query but it didn't sort correct result.

SELECT title, MATCH(title, content) AGAINST ('>("abc def") (abc def) <(abc* def*)' IN BOOLEAN MODE) AS relevance
FROM articles_template
WHERE 1=1
      AND MATCH(title, content) AGAINST ('>("abc def") (abc def) <(abc* def*)' IN BOOLEAN MODE)
      AND date BETWEEN '2015-02-18 00:00:00' AND '2016-02-18 00:00:00'
ORDER BY relevance DESC
LIMIT 0,10

Fulltext seraches do take number of occurances into account, but lot of other things as well. However, if fulltext searches do not produce the expected outcome, then you need to look for alternative solutions, such as sphynx to achieve what you want. — Shadow, Feb 18 '16 at 13:51

score -1 · Answer 1 · answered Feb 18 '16 at 13:46

-1

select count(title) as count from table_name group by title order by count

answered Feb 18 '16 at 13:46

F4bio16

79
3

thanks man for response, I already try this. count will not work here – manpreet singh Feb 18 '16 at 13:50
As i have to match multiple words and these words can be anywhere in content – manpreet singh Feb 18 '16 at 13:52
Do you have any other solution – manpreet singh Feb 18 '16 at 13:53

Mysql count matches for each row and show result order by relevance

1 Answers1