How to Add MySQL Full Text Indexes in Rails

Posted on January 10, 2008

Rails by default uses the InnoDB engine, but MySQL full-text indexing is only supported by the MyISAM table type.

So, first we’ll have to convert our target table over to MyISAM. Next we add the full-text index on several columns. 
class FullTextSearch < ActiveRecord::Migration
  def self.up
    execute 'ALTER TABLE torrents ENGINE = MyISAM'
    execute 'CREATE FULLTEXT INDEX ft_idx_torrents ON torrents(name,filename,description)'
  end

  def self.down
    execute 'ALTER TABLE torrents DROP INDEX ft_idx_torrents'
    execute 'ALTER TABLE torrents ENGINE = InnoDB'
  end
end

This is the actual migration used in The Hydra Project to add full-text searching support of torrents.

Tagged with: migrations mysql searching  |

Comments
  1. Dave – January 11, 2008 @ 06:00 AM

    This will work, but the fulltext index will not make it to the schema.rb. To do that you need to extend/override the the def indexes() method in ActiveRecord::SchemaDumper

  2. Evandro – January 17, 2008 @ 03:18 PM

    Great!