Just a couple of weeks into being a rails developer, I made the one really, really, really big blunder — I used a find_by_sql in a search page. (Oh, please… stop booing. I was a newbie then.) Now, I’ve realized that I might have to modify the search to add yet another column, and well… extending a find_by_sql query for a fourth column (yeah, it was searching 3 columns already) would be stupid and incredibly ugly.

I came across Thinking Sphinx plugin for full text search over MySQL and Postgres databases. It connects ActiveRecord to the Sphinx search engine and lets you perform searches over multiple columns and even multiple models easily.

Things went rather smoothly when I tried using the plugin and I managed to set it up and running within a few minutes. Here’s how I set up an example application and tested sphinx.

  1. Download sphinx from here. If you’re on windows, you just have to extract the exe files to ruby/bin directory. On, Linux/Mac, you’ll have to compile the source. (See instructions here.)
  2. Install the thinking_sphinx plugin like this: 
    script/plugin install git://github.com/freelancing-god/thinking-sphinx.git

    You might run into trouble on Windows with the script/plugin install, so you can use this instead.

    ruby script/plugin install http://github.com/freelancing-god/thinking-sphinx.git/
  3. For this example, let’s quickly set up a Post model through scaffolding. 
    ruby script/generate scaffold Post title:string body:text
  4. Now we’ll set up the indexes for the posts model like this: 
      define_index do
    indexes title, body
  end
  5. In the index method in PostsController, change Post.all to this: 
    Post.search(params[:search])
  6. Add a simple search form in articles/index. 
    <% form_tag posts_path, :method => 'get' do %>
  <p>
    <%= text_field_tag :search, params[:search] %>
    <%= submit_tag "Search", :name => nil %>
  </p>
<% end %>
  7. Now we have to run a couple of rake commands to get sphinx to work. To get sphinx to process the data, run this rake task: 
    rake thinking_sphinx:index

    To start the sphinx server:

    rake thinking_sphinx:start
  8. Now if you search for something using the form, the page returns the posts matching your query.

It also allows you to search across multiple models that are related. For example, if a post has many comments, you could change define_index to search comments as well.

  define_index do
    indexes title, body
    indexes comments.body :as => :comments_body
  end

Sphinx also has support for delta indexing, which means that rather than index the entire table, it can index only those rows that have been added since the last index. You need to schedule the indexing according to your application’s requirement. There’s a nice little gem called whenever that will allow you to schedule the rake tasks in ruby.

What plugins have you used for full text search in rails? Have you tried Sphinx? Or ferret or solr? I’ve mostly heard people saying sphnx is better than solr or ferret. What do you think?

Update. I’ve put the source code for this example on github.