I have been tasked with creating a site wide search feature. The search needs to look at articles, events and page content
I've used MATCH()/AGAINST() in MySQL before and know how to get the relevance of a result but as far as I know the relevance is unique to the search (contents, number of rows etc) the relevance of results from the articles table wont match the relevance of results from the events table.
Is there anyway to unify the relevance so that results from all three tables have a comparable relevance?
Best Answer
Yes, you can unify them very well using a search engine such as Apache Lucene and Solr.
http://lucene.apache.org/solr/
If you need to do it only in MySQL, you can do this with a UNION. You'll probably want to suppress any zero-relevant results.
You'll need to decide how you want to affect the relevance depending on which table matches.
For example, suppose you want articles to be most important, events to be medium important, and pages to be least important. You can use multipliers like this:
Here's a working example you can try that demonstrates some of these techniques:
Create sample data:
Make it searchable:
Use a UNION to search all these tables:
The result: