Full text search ranking

Question

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?

Answer 1

What about writing a user-defined function, which will remove duplicate keywords? So what your query will look like :

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