Text search options for a Web app?

Question

I'm going to be implementing a Web app using Rails with MySQL running on a small CentOS VPS. (Small server, limited user base to start out.) It's going to store a lot of text, sort of like a blog. I'd like to offer strong search options -- parens, AND, OR, exact phrase.

The other piece of it is that the data is private, so using Google or some other externally-hosted search provider is probably off the table.

I have some experience with PostgreSQL's built in full text search capabilities, but for this thing it's going to be MySQL. I've checked out the MySQL docs and I don't think MySQL's full text indexes sound too great.

What about just using %LIKE% queries? Is that ever done? If I did that, would it mean the DB engine would have to examine every character in the column for every search? I'm not sure about that.

Any thoughts or advice would be appreciated.

Answer 1

MySQL's full-text search functionality is pretty limited (MyISAM-only, and many other limitations besides) -- but, Sphinx is very strong, and it can speak MySQL's own protocol so it can "sit" right between your client and your MySQL server, passing most requests right on but dealing with the full-text ones, if that's how you want to install and use it (there are other options of course!).

Answer 2

Ferret is a ruby port of Lucene. That site appears to be having issues right now.
You can read a little more over at O'Reilly.

Answer 3

Some previous discussion on this. I've personally used Ferret in production, Sphinx in almost-production, and Xapian to screw around with. Unless I absolutely required easy result text highlighting, I'd choose Sphinx for a new project today.

http://stackoverflow.com/questions/73527/whats-the-best-option-for-searching-in-ruby-on-rails

http://stackoverflow.com/questions/47656/how-do-i-do-full-text-searching-in-ruby-on-rails

http://stackoverflow.com/questions/737275/pros-cons-of-full-text-search-engine-lucene-sphinx-postgresql-full-text-searc