Page 1 of 1

Order by Relevance

Posted: Fri Mar 26, 2010 6:36 pm
by eagles396
I was wondering if anyone knows how to order a mysql search result by relevance?

For example I have a database that contains a list of over 1000 retailers and if someone was to search for 'sky' I would like sky to appear before skype, kaspersky on the results page.

Another example would be if someone searched for 'Play' then I would expect Play to appear before Gameplay, Powerplay Direct etc

Any help would be appreciated

Thanks

Re: Order by Relevance

Posted: Fri Mar 26, 2010 6:52 pm
by Eran
http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html
Check the comments for sorting by relevance

Re: Order by Relevance

Posted: Fri Mar 26, 2010 6:53 pm
by eagles396
Managed to get it partly working using the following mysql. Only problem is it does not have gameplay in the results?? Is that because gameplay is all one word?

Code: Select all

SELECT *, ( (1.3 * (MATCH(retailer) AGAINST ('play' IN BOOLEAN MODE))) + (0.6 * (MATCH(description) AGAINST ('play' IN BOOLEAN MODE))) ) AS relevance FROM retailers WHERE ( MATCH(retailer,description) AGAINST ('play' IN BOOLEAN MODE) ) HAVING relevance > 0 ORDER BY relevance DESC

Re: Order by Relevance

Posted: Fri Mar 26, 2010 7:03 pm
by josh
You'd have to post the contents of your table. When the table is small it can pick up random things as stop words.

Re: Order by Relevance

Posted: Fri Mar 26, 2010 7:15 pm
by eagles396
It seems if I put a space between gameplay (i.e game play) it will display but doesn't display without a space??