Add fields to a MySQL table without doing an ALTER TABLE
I have a database table that was created about 2 years ago and has been filling up quite quickly over the years. These days, it’s massive. Our database dumps are 68gb uncompressed, and 60gb of that is this table. It’s used quite regularly, as it contains all of the error reports we receive, but to call it “unwieldy” is an understatement.
I was content to just let sleeping dogs lie, but alas — one of my devs needs a couple extra fields added to the table for more data and sorting and whatnot. If this wasn’t a 60gb table in our production database, I’d happily run an ALTER TABLE and call it a day. (In fact, I attempted to do this — and then the site went down because the whole db was locked. oops)
Instead, I discovered a better way to add fields while retaining both uptime and data (!). MySQL’s CREATE TABLE command actually has a lot of interesting functionality that allows me to do this:
CREATE TABLE errors2 ( keywords VARCHAR(255), errorid VARCHAR(64), stacktrace TEXT, is_silent BOOL, id INT(10) AUTO_INCREMENT, PRIMARY KEY (id), KEY playerid (playerid,datecreate), KEY datecreate (datecreate), KEY hidden (hidden,datecreate), KEY hidden_debug (hidden,is_debug,datecreate) ) ENGINE=InnoDB AUTO_INCREMENT=2417067 DEFAULT CHARSET=utf8 SELECT * from errors;
What this CREATE TABLE statement does is create a new table with 5 explicitly-specified fields (keywords, errorid, stacktrace, is_silent, and id). Four of these are what I wanted to add; ‘id’ exists in the original table, but I specify it here because I need to make it AUTO_INCREMENT (as this is a table setting, not a bit of data or schema that can be copied). Additional keys are specified verbatim from a SHOW CREATE TABLE errors (the original table), as is the AUTO_INCREMENT value.
After specifying my table creation variables, I perform a SELECT on the original table. MySQL is smart enough to know that if I’m SELECTing during a CREATE TABLE, I probably want any applicable table schema copied as well, so it does exactly that — copies over any columns missing from the schema I specified in my CREATE statement. Even better, because the various keys were specified, the indexes get copied over as well.
The result? An exact copy of the original table — with four additional fields added. All that’s left is to clean up:
DROP TABLE errors; RENAME TABLE errors2 TO errors;
And that, as they say, is that.