November 2006
Using MySQL Full Text Search with Rails
MySQL offers powerful out-of-the-box full text search capability. It allows you to do advanced search on text columns such as:
apple +computer -fruit
As of Rails 1.1.6, working with MySQL Full Text search is not that straight forward. The first problem is that Migrations doesn't support the SQL syntax for full text indexes. Your create table class has to to two things.
- Set the table options to "ENGINE=MyISAM"; the default InnoDB doesn't support FULL TEXT.
- Execute the native DML to create the INDEX.
Here's a complete example:
-
create_table("stories", :force => true, : options=> "ENGINE=MyISAM") do |t|
-
t.column("subject", :string, :limit => 150)
-
t.column("created_at", :timestamp)
-
t.column("created_by_member_id", :integer, :limit => 10)
-
t.column("happened_on", :date) t.column("body", :text)
-
end
-
add_index "stories", ["created_by_member_id"]
-
#Create MySQL specific full-text index
-
execute "CREATE FULLTEXT INDEX FullText_Stories ON stories (subject, body)"
That takes care of the Migration part. No big deal, you can't expect Migrations to support all the specific features of all database engines. The second problem has to do with Unit Testing. Rails schema dumper will fail to recognize the full text index and will try to generate a regular index. However, MySQL will not like creating an index on the TEXT data type, and your full text tests will fail as well.To resolve this problem, we will have to fixenhance the schema dumper located at ./active-record-1.*.*/lib/active_record/schema_dumper.rb under your Ruby gems or vendor (if you've frozen Rails) directory. The adapter can not tell us if we are using full text index, so we'll have to improvise and use a naming convention. Assuming all your full text indexes are prefixed with "FullText_", we can check for it and generate the appropriate schema.rb.
Here's the replacement code for the indexes method in schema_dumper.rb:
-
def indexes(table, stream)
-
indexes = @connection.indexes(table)
-
indexes.each do |index|
-
if index.name=~/FullText_/ and @connection.is_a?(ActiveRecord::ConnectionAdapters::MysqlAdapter)
-
stream.puts <
-
execute "ALTER TABLE #{index.table} ENGINE = MyISAM"
-
execute "CREATE FULLTEXT INDEX #{index.name} ON #{index.table} (#{index.columns.join(',')})"
-
RUBY
-
else
-
stream.print " add_index #{index.table.inspect}, #{index.columns.inspect}, :name => #{index.name.inspect}"
-
stream.print ", :unique => true" if index.unique
-
stream.puts
-
end
-
end
-
stream.puts unless indexes.empty?
-
end
We check to make sure the database adapter is MySQL for good measure. That should do the trick. But wait! Don't modify the Rails code, use the plug-in instead! You can download the Rails 1.1.6 plug-in here. Drop it in your Rails vendor directory and enjoy full-text search with MySQL.
