CODEWITHSUNDEEP
mysqlfull-text-search
Sharmila
Sharmila

Reputation: 261

Mysql Full text searching not workign as intended

I have following table structure, 

keyword          keyword_type
---------------------------------
Membership       Renew
Membership       New            
Membership       Lost
Membership       Damage

Both the columns are indexed for full text search. And I have set ft_min_word_len = 3 in mysql configuration file, so that the word less than 4 characters can be searched. (MYSQL SHOW VARIABLES confirms that variable is set correctly). But the problem I m facing is whenever I execute the following query, Membership Renew comes first, Membership New should come first as I have ordered by relevancy.

SELECT  *, MATCH(keyword_name, keyword_type) 
AGAINST ('+membership new' IN BOOLEAN MODE) AS relevancy  
FROM table_keywords 
 WHERE MATCH(keyword_name, keyword_type) 
AGAINST ('+membership new' IN BOOLEAN MODE) ORDER BY relevancy DESC

As instructed in other posts I tried

REPAIR TABLE table_keywrods QUICK

This didn't worked either. Any help?

Thanks Sharmila

Upvotes: 0

Views: 26

Answers (1)

O. Jones
O. Jones

Reputation: 108641

My experiments with this show that all the samples in your table throw back the same relevancy value with your query.

One of the facts about FULLTEXT search is its dependence on human disambiguation. It's often hard to make the very first row of the result set be the perfect row.

You could try putting in this ORDER BY clause, to show the shortest results first. It might do the trick; it did for me.

ORDER BY relevancy DESC, LENGTH(keyword)+LENGTH(keyword_type) ASC

Here's a SQL fiddle. http://sqlfiddle.com/#!2/ab9e5/2/0

Upvotes: 1

Related Questions