February 6th, 2006, 03:42 AM
MYSQL full text search problem
I have created a parser for cv’s and am having problems retrieving records when searching using the keywords ‘c#’, or ‘c++’. I use a boolean full text search and if a cv contains the keyword it should be retrieved.
SELECT cv.id, cv.candidate_id, MATCH (cv.cv_text) AGAINST ('C++') AS score FROM db.cv_test cv, fb.candidates_test c WHERE cv.candidate_id = c.id AND c.hide_from_search = 'N' AND c.active = 'Y' AND MATCH (cv.cv_text) AGAINST (CONVERT( _utf8 '+C++' USING latin1 ) IN BOOLEAN MODE) order by score desc
It is not a minimium letters problem as that is set to 2
But nothing is returned.
Any idea's / suggestions would be appreciated
February 6th, 2006, 09:26 AM
The "problem" is that MySQL doesn't consider certain characters, such as # and +, to be part of a word. This problem was discussed by CNET, IIRC, at last years MySQL User's Conference and AFAIK, they don't have a solution for it yet. However, in a later version of MySQL, they're planning on allowing people to plug in their own full-text parser plugin. It appears to be in version 5.1.
Comments on this post
mysql> SELECT * FROM user WHERE clue > 0;
0 Results Returned.
My Tech Blog
February 6th, 2006, 10:05 AM
Thanks for your help. I must have spent days trying everything to solve this.