Alex Rivera | Logout

Full text search ranking

Asked 2011-09-16T00:04:40.060
9

I have a small problem (that's what I hope) with ranking my search results of full text search in mysql database. I have tried writing it in two ways:

NATURAL WAY:

SELECT SQL_CALC_FOUND_ROWS *,
MATCH(productname,keywords) AGAINST('$cl_search') AS score 
FROM products 
WHERE MATCH(productname,keywords) AGAINST('$cl_search') 
ORDER BY score DESC,lastupdated DESC;

BOOLEAN WAY:

SELECT SQL_CALC_FOUND_ROWS *,
((MATCH(productname) AGAINST('$cl_search' IN BOOLEAN MODE))+
 (MATCH(keywords) AGAINST('\"$cl_search\"' IN BOOLEAN MODE))) AS score 
FROM products
WHERE MATCH(productname,keywords) AGAINST('$cl_search')
ORDER BY score DESC,lastupdated DESC;

I like the indexing I get when I search in natural language mode but how do I prevent someone from entering i.e. "bag bag bag bag bag" as productname to get good search results?

So I wrote the boolean way to fix that but 1. it is slower and 2. I don't get the other relevancy indexing like 'compared to word count'.

Any thoughts on how to get the best of both worlds?

Edit
Report

1 Answer

-7

Quite easy, use Lucene instead, it's much more advanced and surely has options to handle what you want.

answered 2011-09-23T09:19:05.397

Your Answer