thickpaddy.com

Edit: this helper has now evolved into a plugin. See https://github.com/thickpaddy/mysql_big_table_migration.

With MySQL 5, altering large tables, including adding or dropping indexes, can be painfully slow. In order to alter the table, MySQL normally creates a temporary table with the new structure and copies rows into it, one-by-one, updating the indexes as it goes. Searches for “mysql slow index creation”, “mysql copy to tmp table” and “mysql alter table slow” will reveal all the gory details, but I'm going to concentrate on a workaround for Ruby on Rails applications.

The problem with slow index creation on large tables hit me very hard when I started to review one of our production databases with a view to improving performance, primarily by adding appropriate indexes where missing, and dropping some unused indexes too. I needed to add an index to one particular table that had in excess of 50 million rows. Creating a new index on this table resulted in the system seeming to hang in state “copy to tmp table”. SHOW PROCESSLIST had one thread in this state overnight, until I eventually gave up and killed it.

After some research, it seemed the best way to work around this (as in something that would work for various storage engines and server configurations), was to create the new table manually, and copy the data into it in bulk rather than row-by-row. I wrote a one-off migration to test this and it worked well.

It wasn't long before I needed to add indexes on other large tables, so I ended up creating a migration helper for altering large tables, allowing indexes to be created and dropped, and columns added and removed, in minutes rather than hours (or days in some cases).

I saved the code in a file called mysql_big_table_migration_helper.rb and dumped it directly into the lib directory. To use it from a migration, extend your migration with the helper module. This adds 4 new class methods, for adding and removing indexes and columns by bulk copying into a temp table rather than relying on MySQL's row-by-row copying. I did toy with the idea of creating a plugin that does all of this automatically, but I'm wary of that kind of voodoo, and decided it was wiser (for the moment at least) to have developers make their intentions explicit.

An example migration that adds and removes indexes...

Some notes/warnings...

• The migration helper only works with the standard MySQL adapter (i.e not MySQL2).
• If you use another adapter, it will still add and remove indexes and columns, but without creating and bulk copying into the temp table. So, it won't break if, for example, you use the MySQL adapter in production, but SQLite in development.
• Although the code does try to handle updates that occur after the migration has started, it just locks tables at the end of the bulk copy and checks for new or updated rows using the id, or updated_at, column. This is absolutely not the same as an ACID compliant transaction! It does the job for us, but it may not be suitable for you.