Order by Relevance

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eagles396
Forum Newbie
Posts: 3
Joined: Fri Mar 26, 2010 6:31 pm

Order by Relevance

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Order by Relevance

Post by Eran »

http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html
Check the comments for sorting by relevance
eagles396
Forum Newbie
Posts: 3
Joined: Fri Mar 26, 2010 6:31 pm

Re: Order by Relevance

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Order by Relevance

Post 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.
eagles396
Forum Newbie
Posts: 3
Joined: Fri Mar 26, 2010 6:31 pm

Re: Order by Relevance

Post 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??
Post Reply