Complex Search Query
Hey all. I'm trying to get used to this whole FULLTEXT index thing. I have a search field where a user looks for music. He can either enter in the name of a song, the artists name, etc. (This is a MySQL database).
I have 2 tables.
The first is the table of artists (tblArtists) that looks something like:
ArtistId, Name, etc.
The second is the table of songs (tblSongs) that looks something like:
SongId, UserId, Title, etc.
Now, if a user types in 'Kanye West', obviously it wouldn't match any song titles, but Kanye West's songs should have greater relevance than those songs that contain either words 'Kanye', or 'West'. I know MATCH searches can't be done with multiple tables. So really I need to make my own sort of scoring system. Any best practices on how I should go about doing this?
I also have a need to do the opposite. I need to display the artist most relevant to the search. For example, 'Gold Digger' wouldn't match any artist's name, but Kanye West would obviously be relevant to the search (there's a Plays column in tblSongs that keeps track of # of plays on the site that would be a help in scoring this).