Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Make change_table execute changes in batch

#4176

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!

Reported by Sai Emrys · March 14th, 2010 @ 06:49 PM

State: new
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. Kenn Ejima
    Kenn Ejima

    +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.

    January 19th, 2011 @ 07:03 AM

  2. Morgan Christiansson
    Morgan Christiansson

    February 28th, 2011 @ 05:41 PM

  3. Kenn Ejima
    Kenn Ejima

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

    March 8th, 2011 @ 04:54 PM