mysql_full-text_with_rails.markdown

You may be asking yourself "why didn't this idiot just use Sphinx, Xapian or Hyper Estraier?". The decision to use MySQL Full-Text Search was based on the fact that this is a smallish project with minimal full-text searching needs. I felt that the requirements didn't warrant downloading/compiling/installing software, monitoring daemons/processes, rebuilding indices, and all the other crap that comes with the big boys of full-text indexing. Don't get me wrong I love Sphinx and Xapian, having used both on several projects, but I wanted something with a little less ceremony. Plus I'm always up for learning something new.

migration

The first thing you need to do is convert the storage engine from the Rails default InnoDB to MyISAM on any tables you want to do full-text searching on.

You then need to create a full-text index on all the fields you want to index on the table.

class FullTextIndex < ActiveRecord::Migration
  def self.up
    execute('ALTER TABLE projects ENGINE = MyISAM')
    execute('ALTER TABLE customers ENGINE = MyISAM')
    execute('CREATE FULLTEXT INDEX full_text_project ON project (name)')
    execute('CREATE FULLTEXT INDEX full_text_customer ON customers (last_name, organization)')
  end

  def self.down
    execute('ALTER TABLE projects ENGINE = innodb')
    execute('ALTER TABLE customers ENGINE = innodb')
    execute('DROP INDEX full_text_project ON projects')
    execute('DROP INDEX full_text_customer ON customers')
  end
end

environment.rb

After running the migration you'll find you get this error when you run rake db:test:prepare :

Mysql::Error: BLOB/TEXT column 'text' used in key specification without a key length: CREATE  INDEX 'fulltext_project' ON 'projects' ('name')

To fix this add config.active_record.schema_format = :sql to your environment.rb. This will have the rake task use clone_structure instead of clone.

mysql conf

By default the MySQL Full-Text has a minimum word length of 4 which means words like 'dog' and 'cat' will be ignored. You can change this by adding this to an option file:

[mysqld]
ft_min_word_len=3

If you change any of the full-text indexing options you'll need to rebuild the full-text indexes on all your tables with REPAIR TABLE tblname QUICK

test db transactions

This was the one true gotcha as a my tests started to randomly fail. After poking around a bit I discovered that the test data was not being deleted from any of the tables using MyISAM. The MyISAM engine is not transactional and as a result data was not being cleaned out of the test db with self.use_transaction_fixtures = true. To get around this I added a teardown method to ActiveSupport::TestCase :

class ActiveSupport::TestCase
  def teardown
    ['projects', 'customers'].each do |m| 
      ActiveRecord::Base.connection.delete("delete from #{m}")
    end
  end
end