I have a web service which returns results to a jquery auto-complete.
For the query I must use four full blown like clauses
LIKE('%SOME_TERM%')
The reason being that the users need to be able to return results from sub strings as well as proper words. I have also tried full-text indexes with their many options in this case in both Natural and Boolean mode but they just does not work as well and their results leave a lot to be desired in this case.
The database is highly optimized with indexes and even with the LIKE clauses returning results from a query with multiple joins and with one of the tables having 200,000 rows takes ~ 0.2/0.3 seconds on first run. Once its cached by the server then obviously the time taken is miniscule.
I was wondering if there is anything else that would be worth trying here. I had looked at some standalone search providers but I'm a little tight time-wise on this project(nearly done and ready to launch) so can't afford any large setups or large-scale refactoring time and funding wise.
Its possible that it's as good as it gets but no harm in letting SO have its say is my attitude.
I think apache solr is they way to go for you. For full text searches. http://lucene.apache.org/solr/
But like you said, if you don't have much time left, and you are sure your queries are performing at their best. I don't see much you can do.