Make change_table execute changes in batch #662

Closed
lighthouse-import opened this Issue May 16, 2011 · 3 comments

Comments

Projects
None yet
1 participant

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/4176
Created by Sai Emrys - 2010-03-14 18:49:20 UTC

Currently, change_table executes every change as an ALTER TABLE in sequence.

This is extremely inefficient if making multiple changes, especially for a large table. (E.g. right now I'm adding ~25 columns to a table with ~1M rows.) Instead of just e.g. reallocating the table space for the full new size, it reallocates it for every single increment. That's lame.

Instead, change_table should batch together the operations requested (if compatible) and execute them as a single ALTER TABLE command, which would allow the database to optimize its operation.

cf. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for the mysql version; this is probably true of all other vendors also.

Thanks!

Imported from Lighthouse.
Comment by Kenn Ejima - 2011-01-19 07:03:53 UTC

+1

As our tables have grown to 10M+ rows, almost all significant migrations have raw ALTER TABLE statements in it due to this exact problem. It would be awesome if change_table runs as a single ALTER TABLE statement.

Imported from Lighthouse.
Comment by Morgan Christiansson - 2011-02-28 17:41:41 UTC

http://m.onkey.org/bulk-alter-table-with-rails-3-and-mysql

Imported from Lighthouse.
Comment by Kenn Ejima - 2011-03-08 16:54:32 UTC

Awesome - IMHO :bulk => true should be the default behavior for MySQL, once it's battle-tested in the wild.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment