mySQL stick with fulltext search or go with alternative?

Question

I'd like some suggestions on how to best handle this. I've got a table with about 2500 records (not many I know and only growing at about 500 per year). It has 3 text fields I currently use fulltext search on. The minimum character limit is causing problems though.

Would yall suggest just reducing the minimum character limit to accept 3 character words (which is most of the problem) and continue using fulltext searching or should I look into other options like Sphinx or Lucene? Basically I'm unfamiliar with sphinx/lucene/etc and don't know if they would offer me any big advantages on searching with the type and amount of data I'm searching through.

Thanks

Answer 1

For now, I would suggest you just reduce the minimum character limit. However you should start looking into Lucene/SOLR, as they provide many more benefits such as faceted results and spatial searches (as of 3.5+), but the ramp-up time can be painful. If you start researching and practicing with SOLR now, you will be ready to shift when a new feature request comes in that requires it.

Answer 2

I would suggest you to have a look at OpenSearchServer a search engine based on lucene.As you described your data's are in MYSQL OpenSearchServer has a feature that you can import your data's directly from MYSQL and POSTGRESQL database. please check the documentation to import values from MYSQL to OpenSearchServer index. http://www.open-search-server.com/documentation/index.php/User_guide_-_Database