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.

  1. Set the table options to "ENGINE=MyISAM"; the default InnoDB doesn't support FULL TEXT.
  2. Execute the native DML to create the INDEX.

Here's a complete example:

PLAIN TEXT
RUBY:
  1. create_table("stories", :force => true, : options=> "ENGINE=MyISAM") do |t|
  2. t.column("subject", :string, :limit => 150)
  3. t.column("created_at", :timestamp)
  4. t.column("created_by_member_id", :integer, :limit => 10)
  5. t.column("happened_on", :date)   t.column("body", :text)
  6. end
  7. add_index "stories", ["created_by_member_id"]
  8. #Create MySQL specific full-text index
  9. 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:

PLAIN TEXT
RUBY:
  1. def indexes(table, stream)
  2. indexes = @connection.indexes(table)
  3. indexes.each do |index|
  4. if index.name=~/FullText_/ and @connection.is_a?(ActiveRecord::ConnectionAdapters::MysqlAdapter)
  5. stream.puts <
  6. execute "ALTER TABLE #{index.table} ENGINE = MyISAM"
  7. execute "CREATE FULLTEXT INDEX #{index.name} ON #{index.table} (#{index.columns.join(',')})"
  8. RUBY
  9. else
  10. stream.print "  add_index #{index.table.inspect}, #{index.columns.inspect}, :name => #{index.name.inspect}"
  11. stream.print ", :unique => true" if index.unique
  12. stream.puts
  13. end
  14. end
  15. stream.puts unless indexes.empty?
  16. 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.

ActiveRecord MySQL Full Text Plugin

Nov 20 2006 04:40 pm | rails and ruby |

6 Responses to “Using MySQL Full Text Search with Rails”

  1. on 26 Jan 2007 at 3:01 pm   Tessa Lau

    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?

  2. on 26 Jan 2007 at 3:48 pm   Tessa Lau

    Nevermind — I had forgotten to rename my index to have the FullText_ prefix.

  3. on 16 Apr 2007 at 12:43 pm   Niko

    Well written plugin, but why not just dump the schema in native sql?

    # File: config/environment.rb
    config.active_record.schema_format = :sql

  4. on 21 Apr 2007 at 10:24 am   Zak Mandhro

    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.

  5. on 10 Dec 2007 at 3:53 am   coolpositive

    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?

  6. on 28 Mar 2008 at 10:51 pm   Mysql Download

    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.

Leave a Reply