best way to handle complex mysql search and measure how good of a match each result is

Question

Sorry for the long title but couldn't think of a good way to put it really - i'm currently working on a large web app project and one of the main features is the detailed search, without saying too much about the project it is used to find business related deals - the search function is spread over 3 pages currently and offers pretty much every option you'd want if you were in the industry...

But the problem i've got now is that is a lot of fields and so when it comes to searching for matches in the db i don't really know the best way forward i don't think a standard mysql like is going to cut it here also i need to be able figure out how much of a fit (good match) each result is and then display that in the results (search result 1 is a 90% fit etc)

Does anyone know which is the best way to tackle this ? i know there are external search engines etc out there but don't know anything about them really to make any sort of logical choice...

Thanks !

Answer 1

Finding relevance in search is a complex topic that deals with many parameters. The MySQL match() search itself is pretty complex as you can see here. Perhaps you could use this score itself as your measure. You can customize this to some extent.

Another option as you mentioned is to use external search engines, something on the lines of Solr. It has all the requirements you are looking for. Its fast, scalable and able to provide customizing options to improve "relevance" for your specific needs.