This is for Ruby on Rails 2.5.x, and MySQL 5, and the MySQL2 gem (there is a problem with mysql2-0.3.2. and rails 3.0.x, but using 0.2.7 for Ruby 2.5.x and 3.0.x is good). I do hope to test some of this with other versions of Rails and gems and DB’s. (I expect the problem is in guts of ActiveRecord)
Ruby on Rails best practices calls for adding indexes to your databases, and of course the place to do that is in your migrations (see Always add DB index). Why? Performance!
A related SQL Ninja trick is to create indexes based on fields you filter on (where clauses), or sort on (order by, group by) in your code, not just on the fields you join on (with is what the above link is referring to with reject to foreign keys). The next next level is compound indexes! Compound indexes are called composite keys in some database systems.
If you’re new to this stuff look at A grand piano for your violin on the Giant Robots Smashing Into Other Giant Robots blog, and also Mysql Performance analysis Improve performance by removing unused indexes from the database (on John McCaffrey blog) before you index all the columns – which is almost as bad as no indexes!
The basic syntax for adding a compound composite key index is at it most simplest (you can also require that the compound is unique, or has a certain name) looks like this
1 | add_index :accounts, [:branch_id, :party_id] |
The issue comes in when you do a rollback and then rake db:migrate, the migrate complains that a index with that name already exists!
1 | Mysql2::Error: Duplicate key name 'index_accounts_on_branch_id_and_party_id': |
Bummer 🙁
For regular – single column – indexes, when you drop the column, it’s dropping the indexes for that single column and up don’t have to do anything more that drop that column in the normal Rails migration automagical way:
1 2 3 | def self.down remove_column :accounts, :branch_id end |
but for these fancy dancy compound composite key index thingyes you need to explicitly remove the indexes
1 2 3 4 | def self.down remove_index :accounts, [:branch_id, :party_id] remove_column :accounts, :branch_id end |
So the lessons from this are:
- index
- explicitly remove those indexes
- test your migrations and rollback, before you need them
that way when you have to do a rollback or do a “rake db:migrate:redo” you’ll won’t lose any (more) hair/sleep over this 🙂