mySQL searching / MATCH() AGAINST() help

Question

Hello,

I have a database of song titles. Each song title has an assigned integer score. The higher the score the higher it ranks on my website.

So if "Lady Gaga - Poker Face" has a score of 23039, and "Eminem - Not Afraid" has a score of 13400, Lady Gaga will be above Eminem.

This is of course accomplished by a simple ORDER BY score DESC

However, my website also has a search function where people may input a keyword phrase to return a list of song titles. I do this with a MATCH() AGAINST() query like so:

Method #1

SELECT * FROM links MATCH(songtitle) AGAINST({the keyword}) LIMIT 5';

This works, but I want to display these 5 results in order of their score. However, if I perform this query instead:

Method #2

SELECT * FROM links MATCH(songtitle) AGAINST({the keyword}) ORDER BY score DESC LIMIT 5';

Then the order of relevancy is completely lost. I'm wanting to order the returned results both by score AND by relevance.

Take this example dataset and scenarios:

Data Set:

song title                  |       score
pink - sober                |        2002
pink - funhouse             |        2001
pink floyd - high hopes     |        2000
pink floyd - on the run     |        1999
pink floyd - brain damage   |        1998
pink floyd - money          |        1997
pink floyd - time           |        1996

Scenarios:

• When you search for "Pink Floyd" using the first method above, you receive 5 Pink Floyd songs in seemingly random order. This is great, but, I want the songs that are returned to be ordered by their score in descending order. So you add "ORDER BY score DESC" to the first method and that gives you the second method above...

• When you search for "Pink Floyd" using the second method above, you receive Pink Floyd songs in the proper order (ordered by score DESC), but now you also receive two songs by pop artist "Pink", because there are 2 songs in the database by Pink with a higher score than any of the Pink Floyd songs.

Desired results:

• Searching for "Pink Floyd" returns all 5 pink floyd songs, ordered by their assigned score in descending order.

• Searching for "Pink" returns the two songs by Pink in the descending order of their assigned score. If Pink Floyd songs showed up after both of the Pink songs, that'd be fine too.

See what I mean? Let me know if you need any clarification!

Dave

Answer 1

Looks like you might be able to get the 'relevance' from the match - check out this link. Given this, you can order first by the relevance and then by the score.