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.
Related Articles
Family2.0Famiva is live, FINALLY! (March 21st, 2007)7 users responded in this post
I can’t seem to get this to do anything. I unzipped this into vendor/plugins, and ran rake db_schema_dump. The db/schema.rb file still has the same add_index call which fails on the TEXT field. Do I need to do something else to install the plugin?
Nevermind — I had forgotten to rename my index to have the FullText_ prefix.
Well written plugin, but why not just dump the schema in native sql?
# File: config/environment.rb
config.active_record.schema_format = :sql
Niko: As of Rails 1.1.6, the SQL generated by SchemaDumper failed to recognize the Full Text index, treating it like a simple index. So if you were to let Rails dump the SQL schema and recreated your database from it, your index would not work as expected.
This may have been resolved in 1.2, I haven’t checked.
I can’t seem to get this to do anything. I unzipped this into vendor/plugins, and ran rake db_schema_dump. The db/schema.rb file still has the same add_index call which fails on the TEXT field. Do I need to do something else to install the plugin?
Hi Finally I found a site the has some good information on Mysql Download. I was searching around and found your post Using MySQL Full Text Search with Rails, thanks for the good info..I’ll be checkin back soon.
Hi Zak,
I implemented a modified version of your schema-dumper patch and then later discovered that it is not needed!
Rails has a built-in configuration option for handling schemas specific to a particular database:
config.active_record.schema_format = :sql
Simply set the above variable in your environment.rb file and everything works like magic!
Note that the schema.rb file will still be generated, but the test database will use a .sql file to reset everything. To get the current database schema in SQL format simply type rake db:structure:dump
-A
Leave A Reply