Hi Malik,
MySQL and PostgreSql provide fairly competent text search support through SQL statements, that are more efficient and accurate than LIKE operator.
Typically, the only changes required in existing applications are:
- Create appropriate DB indexes.
- Modify SQL queries sent by application
While PostgreSql provides
tsquery and tssupport SQL operators, MySql provides the
MATCH...AGAINST SQL clause.
Between these two, PostgreSql's support is more powerful and feature rich than MySql. MySql unfortunately imposes a lot of
conditions to support text search ( the worst of which is that storage engine for table being searched has to be non-transactional MyISAM). Neither is as powerful and accurate as a dedicated search framework like lucene/solr, but then maybe you don't need that level of accuracy.
Another option, if you're using Hibernate or can integrate it, is the
Hibernate Search addon. It integrates Lucene transparently with DB updates, which means your app design doesn't have to change much (other than adding some annotations and modifying Hibernate queries).