Search implementation dilemma: full text vs. plain SQL

Posted by Ethan on Stack Overflow See other posts from Stack Overflow or by Ethan
Published on 2010-03-25T21:06:25Z Indexed on 2010/03/25 21:23 UTC
Read the original article Hit count: 354

I have a MySQL/Rails app that needs search. Here's some info about the data:

  • Users search within their own data only, so searches are narrowed down by user_id to begin with.

  • Each user will have up to about five thousand records (they accumulate over time).

  • I wrote out a typical user's records to a text file. The file size is 2.9 MB.

  • Search has to cover two columns: title and body. title is a varchar(255) column. body is column type text.

  • This will be lightly used. If I average a few searches per second that would be surprising.

  • It's running an a 500 MB CentOS 5 VPS machine.

  • I don't want relevance ranking or any kind of fuzziness. Searches should be for exact strings and reliably return all records containing the string. Simple date order -- newest to oldest.

  • I'm using the InnoDB table type.

I'm looking at plain SQL search (through the searchlogic gem) or full text search using Sphinx and the Thinking Sphinx gem.

Sphinx is very fast and Thinking Sphinx is cool, but it adds complexity, a daemon to maintain, cron jobs to maintain the index.

Can I get away with plain SQL search for a small scale app?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sphinx