Multi-column indexes in Ruby on Rails with MySQL

Nothing special, but I wanted to share two things that I learned yesterday while adding a multi-column index for one of my Ruby on Rails projects:

1. The book I use didn't show an example of what the declaration of such index could be in a migrations file. So in case you were looking for such an example, here it is:
   

   
   add_index   :table_name,  [:column1, :column2, :column3], :unique => true, :name => 'some_name_here'
   

   
   So, just to clarify, add_index takes 3 parameters: name of the table you are adding index to, an array of column names to include in the index (could be just 1), and a hash of options that could specify whether the index is unique and assign a specific name to it (only these two options are supported by default).


2. When creating an index using a number of columns with long names I ran across an issue with limits on index name in MySQL. Here is the error message:
   
   
   Mysql::Error: #33000Identifier name 'xxxx_xxx_xxx_xxx' is too long
   
   
   Where xxx were names of columns. When creating an index, MySQL joins together the names of the columns to create index name; however, there is a limit of 100 characters so if all names together in one string are longer you will get that error when trying to create an index.
   

   
   The solution is simple. Just use the name => 'some_custom_short_name' as an option to add_index function call and supply your own short name for the index.

Hope this helps someone.



Here is this info in the context of a migration:

class CreateTagLinks < ActiveRecord::Migration
def self.up
 create_table :tag_links do |t|
   t.integer :tag_id
   t.integer :tagger_id
   t.integer :url_id
   t.boolean :is_accepted
   t.timestamps
 end

 add_index :tag_links, [:tag_id, :tagger_id, :url_id], :unique => true
end

def self.down
 drop_table :tag_links
end
end