Search code examples
mysqlrelevance

mysql - Order by relevance and views

I have seen there are a lot of 'order by relevance' posts on here... I have worked that part out... What I would like to achieve is search results that are first ordered by relevance and then by views.

For example if I searched for 'dog'

and my results both had the same level of relevance to 'dog' then I would like it to order them by views.

Here is my SQL so far - feel free to take a different approach if you can think of a better way.

select * from articles where match(title, description, tags)
  against ('dog' in boolean mode) ORDER BY match(title, description, tags)
  against ('dog' in boolean mode) DESC
Solution

  • Make views field in articles, increment it when the record is viewed (update articles set views = views + 1 where id=..., and then add it into order by clause:

    ...order by  match(...) against (...) desc, views desc

    If you have logs tables, you may use them, but MySQL doesn't work well with big log tables.