PostgreSQL's and MySQL's full text search
PostgreSQL 8.3 has built in full text search which is an integrated version of the "tsearch2"
Here is the documentation: http://www.postgresql.org/docs/8.3/static/textsearch.html
And the example from the documentation:
SELECT titleFROM pgwebWHERE to_tsvector(body) @@ to_tsquery('friend');Where body is a text field. You can index specifically for these types of searches and of course they can become more complex than this simple example. The functionality is very solid and worth diving into as you make your decision.
Best of luck.
Update: Starting in MySQL 5.6, InnoDB supports fulltext search
I'm not well versed in PostgreSQL unfortunately, but if you use the FULL TEXT search in MySQL you're immediately tied to MyISAM. If you want to use InnoDB (and if ACID compliance means anything to you, you should be using InnoDB) you're stuck using other solutions.
Two popular alternatives that are often rolled out are Lucene (an apache project with a Zend module if you're using PHP) and Sphinx.
If your using Hibernate as a ORM I highly recommend using Hibernate search. Its build on top of Lucene so its super fast.
Karl