Database searching and displaying results by relevance

Question

Hello,

Given the following tables, how can I search for a term in each of them and order results by relevance? Thank you.

bands
- band_id
- band_name

albums  
- album_id 
- album_name      

songs
- song_id
- song_name

Answer 1

something like:

SELECT song_id, MATCH(band_name, album_name, song_name)
       AGAINST ('search term')
  FROM bands join albums using (band_id) JOIN songs USING (album_id)
 ORDER BY MATCH(band_name, album_name, song_name) AGAINST ('search term') DESC

This assumes, of course, that you also have a band_id column in the albums table, and an album_id in songs.

The MATCH...AGAINST function returns a 'relevancy' score, computed by MySQL. Ordering by this function will sort your results by relevancy.

Also, this will work much faster if you have a full text index created against the _name columns.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html