Sorting results by Most Relevant terms, using MATCH AGAINST

Zoroz 0
Viewed 30

I'm trying to get this query to order the results so that important terms (like in the example 'Google' are considered in the ordering. See screenshots at the bottom: these show that although the search term is "Google news", the actual Google result is at the bottom, not high in the list. How do i get these relevant terms to be considered so they are higher in the results? By relevant i mean considering the separate words in conjunction so that the correct ordering is maintained, or at least consider them together, so that those with the highest number of occurrences is at the top.

   SELECT * FROM links, bridge, keywords WHERE links.links_id = bridge.link_id AND bridge.keyw_id = keywords.keyw_id AND MATCH(description) AGAINST('google news') AND MATCH(terms) AGAINST('google NEWS') OR MATCH(title) AGAINST('google NEWS') GROUP BY title

Screenshots (for illustration): enter image description here

mysql sql
1 Answers

While MySQL will auto-sort by rank when you have a full-text match, that only applies to queries with a single MATCH(...) AGAINST(...). Your query includes multiple full-text matches, which means the database has no idea which to use to order by, likely falling back to it's default sorting.

You need to combine the ranks and order by the sum to get the results ranked by relevance:

SELECT *, MATCH(description) AGAINST('google news') +
    MATCH(terms) AGAINST('google news') +
    MATCH(title) AGAINST('google news') as `rank`
FROM links, bridge, keywords
WHERE links.links_id = bridge.link_id AND
    bridge.keyw_id = keywords.keyw_id AND
    MATCH(description) AGAINST('google news') AND
    MATCH(terms) AGAINST('google news') OR
    MATCH(title) AGAINST('google news')
ORDER BY `rank` DESC;
userJQ660
Related