I recently found myself wanting to move my MySQL database from MyISAM tables to InnoDB tables. The primary reasons for this are:

1. InnoDB supports transactions
2. InnoDB supports row-level locking, as opposed to table-level locking
3. InnoDB supports foreign key constraints

All very, very good things to have. Unfortunately, InnoDB does not support MyISAM's FULLTEXT indexes. For those of you unfamiliar with MySQL's fulltext searching, it's a really slick little piece of work, especially the boolean mode fulltext searches. I wanted to have this functionality available across my database, but it seemed I couldn't have my transactional, row-level constrained cake and search it, too.

Enter acts_as_fulltext_indexed.

The idea is this: You have a model, and you specify which fields you want indexed. after_save and before_destroy hooks are installed on the model which creates and indexable string and inserts it into a MyISAM table, and then helper methods are provided that let you perform searches for matched content.

Before I go any further, let me specify that this breaks Rails' database-agnostic approach - it's a solution specifically tailored for MySQL. Don't try using this with non-MySQL databases. It just ain't gonna work. Also, I've licensed it under an MIT license, so use, extend, and distribute it at will.

First, install the plugin:

script/plugin install http://svn.digitalsentience.com/svn/rails/acts_as_fulltext_indexed/

I don't have a migration for it yet, but you'll need a table like so:

CREATE TABLE `fulltext_indices` (
  `id` int(11) NOT NULL auto_increment,
  `indexable_type` varchar(60) NOT NULL,
  `indexable_id` int(11) NOT NULL,
  `tokens` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `indexable_type` (`indexable_type`,`indexable_id`),
  FULLTEXT KEY `tokens` (`tokens`)
) ENGINE=MyISAM;

Then, install acts_as_fulltext_indexed on your model:

class Post < ActiveRecord::Base
    # This causes the :title and :body fields on this model to automatically be indexed
    acts_as_fulltext_indexed [:title, :body]
end

Now you can perform the following operations:

# Will return all posts that have words matching "foobar*"
Post.search("foobar")

# Will return all posts that have words matching "foo* AND bar*"
Post.search("foo bar")

If you need more complex indexing - say you want to be able to index association data on the parent - then you need to override build_index_string. In this example, I'm setting the indexed string for the thread to the concatenation of all the bodies of the posts on the thread, as well as the thread title.

class Thread < ActiveRecord::Base
    has_many :posts
    acts_as_fulltext_indexed

    def build_index_string
        self.title + posts.collect {|post| post.body}.join(" ")
    end
end

Now we can perform:

Thread.search("foo bar")

Which will return all Threads that contain posts whose bodies contain "foo* AND bar*", or whose title matches "foo* AND bar*".

Search also takes options such as :order, :limit, and :conditions

Thread.search("foo bar", {:include => [:posts], :order => "threads.created_at asc", :limit => 10})

Finally, search() takes a third parameter. A boolean "transform" specifies whether or not to transform your search string into MySQL boolean search syntax. It's true by default, but you can turn it off if you want to specify a mode complex search.

# Finds all Threads that contain "foo*" but do NOT contain "bar"
Thread.search("+foo* -bar", {:include => [:posts]}, false)

Happy searching!