Fastest way to alter a large MySQL table
In general, you should always be forward thinking when creating your database schema, but it’s impossible to foresee every business need for every aspect of every project. In some cases, you’ll have to alter your table structure – maybe add, drop, or alter a column.
I’ve run into this a few times. Most recently, I had to add a column to a table that was roughly 25 GB. I tried running a simple ALTER TABLE … ADD COLUMN new_column, but discovered after waiting some 18 hours for it to update, that it was no quick task. I didn’t even finish waiting for it to finish.
Here’s what I came up as the fastest way:
- Lock the table for reading and writing. This won’t always be easy, but it’s a good idea to set up a switch in your application for this.
- Copy the table structure to a new table. If your table is foo_bar, run:
CREATE TABLE foo_bar_new LIKE foo_bar;
- Alter the structure on the new table (the one with no records). Clearly this should be instant.
- Export the contents of the old table and import into the new table. You’ll need to grab and explicity set the column names (there’s a few ways to do this).
INSERT INTO foo_bar_new (col1, col2, col3) SELECT (col1, col2, col3) FROM foo_bar;
- Rename the two tables. Renaming is near instant, as the MySQL engine is just renaming file names. This will make your new table live.
RENAME TABLE foo_bar TO foo_bar_old; RENAME TABLE foo_bar_new TO foo_bar;
- Unlock the table for reading and writing.
This took about 45 minutes in my case opposed to 18+ hours.