ansaurus

Question

Answer 1

+1 A:

I've basically given up on MySql's full text search in favor of Sphinx -- a dedicated full-text search engine which implements MySql's network protocol so you can "interpose" it between your clients and a MySql server, losing nothing and gaining rich, serious full-text capabilities. Maybe it's not suitable for your needs (which you don't fully express), but I think it's at least work checking out!

Alex Martelli 2009-09-17 06:44:30

Cool thanks a lot. Just to clarify my question. I have a field that is the New York Giants. If I add that to the fulltext index and then search "giant" nothing will come up. How can I use fulltext search to return results in cases like these? I'll check out Sphinx. Thanks for the heads up

Arnold 2009-09-17 15:04:32

@Russ, you're looking for **stemming** plugins (for mysql 5.1, 5.0 didn't support them), e.g. http://www.mnogosearch.org/doc/msearch-udmstemmer.html .

Alex Martelli 2009-09-17 15:07:33

Thanks so much Alex, I appreciate the help. Unfortunatley, my hands are bound and I cannot upgrade to mysql 5.1. But I'm a bit confused, i undersatnd that in 5.0 I can't use customer parsers but does this mean that there is no native mysql method to do stemming in 5.0?

Arnold 2009-09-17 22:06:41

@Russ, yep, that's what MySql's docs say -- plugins to full text search, e.g. for stemming, were only introduced in 5.1. I recommend Sphinx, anyway;-).

Alex Martelli 2009-09-18 03:43:38

Answer 2

+1 A:

You can use wild cards in your full text search. More info here

SELECT * FROM _____ WHERE MATCH (title) AGAINST ('time*' IN BOOLEAN MODE);

james.c.funk 2009-09-18 03:52:44

hmmm nice nice. I didn't think of that. Only issue is that its a poor substitute for stemming since it won't match "timing" and words like that. I opted to do both a fulltext search and a LIKE %...% string matching search. Its a bit tedious and is extra querying but generates fairly solid results.

Arnold 2009-09-18 16:35:28

Answer 3

A:

I think it also does not match "quicktime"? am I right?

It looks as tho the fulltext can find the appended text but not the prepended.

For example, if the word in db is "timeline", then fulltext finds it if I search for "time*" but not if I search for "line*".

Any ideas?

Thanks,

chill 2009-12-22 10:21:19

ansaurus

tags:

views:

answers:

MYSQL Fulltext search and LIKE

related questions