Search code examples
mysqlsearchfull-text-searchrelevance

Mysql require relevancy then sort by date

On a classfieds ads site, I have a query that looks like this:

SELECT * FROM ads WHERE MATCH (ad_title, ad_description) AGAINST ('VW gearbox')

That shows relevant results sorted nicely. But I want to keep relevancy while sorting by ad_date. If I directly sort these by date, then it might show results at the very end, and these results are least relevant.

I need to adjust this query to limit the relevancy of results, and only then sort all by date.

I found many similar questions but I must admit I have troubles understanding the sample queries in the other questions.

I did try to append as relevancy but I got a MySQL error.

Solution

  • if you mean to sort by relevancy first then by date then try

    SELECT * FROM ads WHERE MATCH (ad_title, ad_description) AGAINST ('VW gearbox')
ORDER BY MATCH (ad_title, ad_description) AGAINST ('VW gearbox') DESC, ad_date DESC

    or limit relevance by something like this

    SELECT * FROM ads WHERE MATCH (ad_title, ad_description) AGAINST ('VW gearbox') > 0.3 ORDER BY ad_date DESC

    You can SELECT *, MATCH(ad_title,ad_descriptioN) AGAINST ('VW gearbox') as relevancy FROM ads and get a feel of the numbers and see what number to cut off your results at.