0

After i have normalized my db i am now using fulltext search on myiasm tables.

I have several search scenarios

  1. search "hello" IN "hello friend

  2. search "hell" IN "hello friend,welcome to hell"

  3. search 'fri"ends" in 'come play with fri"ends'

I need the best match and currently using

SELECT cu.ID as UID ,MATCH cu.content AGAINST  ('hell')  as relevance
FROM cht_user cu 
JOIN cht_agent_script cas ON ( cas.UID = cu.ID AND cas.AID = '315') 
WHERE MATCH cu.content AGAINST ('hell')  
ORDER BY relevance DESC

problems are

  1. number 3 wont give a result

  2. number 2 wont give a result

  3. number 1 is the only one that will work

lior r
  • 2,220
  • 7
  • 43
  • 80

1 Answers1

0

Firstly, as they explain in this post, add more data to your table.

Secondly, check out the FULLTEXT Stopwords: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

Mysql ignore a list of words and you can see them in that link. "hello" is ignored by the full text search.

So try adding more records to your table. Then redo your fulltext index:

OPTIMIZE TABLE cht_user;

If you are still experiencing problems let me know

Hope this helps you

Community
  • 1
  • 1
juanreyesv
  • 853
  • 9
  • 22
  • the hello was just for the exmple , can i use user replace with match somehow ? LIKE doing MATCH REPLACE(cu.content,'"',"_") , it is not working for me – lior r Dec 10 '12 at 11:08
  • From what I have read, MySql will ignore the symbols. If you want to search for a specific phrase you can use AGAINST('"this is, silly"') but I am not sure if you can escape the double quote char. This is mainly to search for words or phrases, if you want to look for symbols use `LIKE '%fri"ends%'` instead – juanreyesv Dec 10 '12 at 11:35
  • well my problem is that in hebrew there are quotes in the middle of a word , that means something ... can i do replace ? (REPLACE(cu.content,'"',"_")) it doesnt seems to work – lior r Dec 10 '12 at 11:43
  • Nope, you can't add a REPLACE inside the MATCH. I don't think that MySQL FULLTEXT search is going to give you good results searching in hebrew... Anyway, there is always a workaround, check [this post](http://stackoverflow.com/questions/2010663/lucene-with-php) if you are willing to learn a new technology: Lucene :) – juanreyesv Dec 10 '12 at 12:07