MySQL Modifying Fulltext Search

Question

I'm working in the fulltext search of mysql. So, here is my table:

id   title
1    iphone 4
2    iphone
3    4 iphone 3g
4    iphone 3gs

..and many more

I'm trying to get rows that have specific keywords in the title field with fulltext search. Let's imaging i'm searching for "iphone 4". The fulltext search has a minimum word length of 4, by default. So the number 4 would be ignored.

There would be the possibility to set the minimum length of the fulltext index to one, but that would'nt be performant.

How to achieve that? Thanks for your help!

Answer 1

The FullText search works reasonable well out of the box, but tweaks like this are hard to make. Setting the minimum length to one is an option. It may slow down the search a bit, but until your site is large and/or under heavy load, this performance hit may be neglectable.

If you need more performance but still this freedom, you may consider using a search engine like Sphinx.

Another 'solution' would be to do a FullText search first, and then filter the results yourself by searching for those shorter keywords. But that's a lot of work for something Sphinx can solve more or less out of the box.