Text Search across multiple fields MySQL

Question

I have a MySQL table laid out like this:

ID | Artist | Title

Which contains song information obtained from MP3 ID3 tags.

Now, I would like to be able to run a search on this, however the incoming data could be in a variety of formats. I'm sure you all know how ID3 tags can end up after a couple of people have had hold of them.

I have the following query which I have tried, but looking at it now, I understand completely why it doesnt bring back a result set. I'm fresh out of ideas.

SELECT CONCAT(artist, ' ',title) as trackname 
FROM sound_tracklist 
WHERE CONCAT(artist,' ',title) LIKE '%[JunkTags]Artist - Title[More Junk - www.junkwebsite.com]%'

I'd appreciate any input/direction on this.

Thanks

Answer 1

If you're in MyISAM type tables, try using a FULLTEXT index:

ALTER TABLE sound_tracklist ADD FULLTEXT INDEX (artist, title);

then you can do

SELECT artist, title FROM sound_tracklist WHERE MATCH (artist, title) AGAINST ('bach brandenburg concerto')

it wouldn't catch misspellings ("konshertoe", anyone?), but it would search for your specified keyboards across all the fields in the index, and catch them in any order.