Full Text Search

Question

I have a little problem (what I hope) with ranking my full-text search results in mysql database. I tried to write 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 searching in natural language mode, but how can I prevent someone from entering the “bag with bag for bags” as the product name to get good search results?

So, I wrote a logical way to fix this, but 1. it is slower and 2. I do not get another relevance indexing as "compared to the number of words."

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

Answer 1

How about writing a custom function that will remove duplicate keywords? So, what your request 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; 

Answer 2

Quite simply, use Lucene instead, it is much more advanced and certainly has options for handling what you want.