11

I am working with MySQL full text search but find it lacking in situations where your string is part of a word within a field. If my field is "New York Times" and I search for "Time" I get no results. The hackish way to solve this is to set up two queries, one that does a full text search and the other that does:

SELECT * FROM ___ WHERE 'string' LIKE %searchterm%

Is there any way that I can set up my full text search to solve this issue so I don't have to run the extra query?

JYelton
  • 35,664
  • 27
  • 132
  • 191

2 Answers2

8

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
  • 465
  • 4
  • 8
  • 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. –  Sep 18 '09 at 16:35
  • It also doesn't help when a search needs to go the other way. For example, the data includes the word "ipad" but the user searches for "ipads". The wildcard will not help in this case. – Vincent Jul 06 '14 at 17:16
  • 2
    @Arnold `LIKE %...%` will kill your performance, because SQL won't use indexes. If you go for that approach, you probably won't need the fulltext search. – PeerBr Mar 31 '15 at 14:03
2

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
  • 854,459
  • 170
  • 1,222
  • 1,395
  • 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 –  Sep 17 '09 at 15:04
  • @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 Sep 17 '09 at 15:07
  • 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? –  Sep 17 '09 at 22:06
  • 1
    @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 Sep 18 '09 at 03:43