MySQL fulltext search problem

software development

#1

I’m can not get the fulltext search function to work properly in MySQL when I search for a phrase. For example, I have a fulltext index on the field title in the table biz. When I execute this simple select statement:

select biz_id,title from biz where match (title) against (“Jimmy World”)

I get back records that have ‘Jimmy’ OR ‘World’ in the title instead of just titles with ‘Jimmy World’. I have tried multiple variations of this based on the syntax in the MySQL online manual, with single quotes, double quotes, parens and plus symbols. And I’ve searched the MySQL mailing lists for some clue. No luck so far.

I know that Dreamhost is using MySQL 3.23.55 and perhaps this is a bug in that version.

Any help would be appreciated. Thanks!


#2

From my interpretation, you have to use the boolean mode search in order to use the double quotes. Second, the double quotes are part of the search string value, so you should enclose it in single quotes. Did you see this on the MySQL doc comments?

[quote]Note: When trying to search for an exact phrase in
boolean mode you have to use a construct like
MATCH(…) AGAINST(’“my phrase”’ IN BOOLEAN
MODE). Note the outer single-quotes!! The
documentation is not very clear in this point and it
took my a few hours to figure this out. If you don’t
enclose it in single quotes the query becomes
a ‘normal’ search.[/quote]
In other words,

SELECT biz_id,title FROM biz WHERE MATCH (title) AGAINST (<single><double>Johnny World<double><single> IN BOOLEAN MODE)Should work…


#3

Thanks for responding. I had previously tried enclosing match phrases in single then double quotes, e.g. ‘“jimmy world”’. Also, the MySQL docs note that the “in boolean mode” option is available for MySQL 4.0.1 . When I use it on 3.23.55 (both at Dreamhost and my local development box) I get an error:

select biz_id,title from biz where match (title) against (’“jimmy world”’ in boolean mode);
ERROR 1064: You have an error in your SQL syntax near ‘boolean mode)’ at line 1

I may just have to wait for DH to upgrade to MySQL 4 and use a workaround in the meantime.


#4

Now I remember the reason why I haven’t tried fulltext out myself yet! Seems pointless without the boolean mode to me.

:cool: Perl / MySQL / HTML+CSS


#5

[quote]I may just have to wait for DH to upgrade to MySQL 4 and use a
workaround in the meantime.

[/quote]

We have a working package now that’s installed on a few machines… I imagine the new db machine may be MySQL 4, and I suspect an upgrade isn’t too far away.

Don’t hold me to that, of course. :>


#6

I had the same issue with Boolean searches so I gave up for now, but I have a bigger problem and most likely it is because I have a very slim grasp of indexing. I have a fulltext index on two columns. When I try a search:

SELECT id, title from recipes where course IN (‘entrees’) AND MATCH(title,description) AGAINST(‘keyword’)

I only get results for some of the words, but not all (most importantly some words in the title). Could someone please either explain to me why or how I can make the index work better for me or point me to a place I can learn more about it? I started on the mySQL site, but the descriptions are a little lofty for me.

Thanks.