You’re reading Signal v. Noise, a publication about the web by Basecamp since 1999. Happy !


About John

Taking the Pain Out of MySQL Schema Changes

John wrote this on 19 comments

A common obstacle we face when releasing new features is making production schema changes in MySQL. Many new features require additional columns or indexes. Running an “ALTER TABLE” in MySQL to add the needed columns and indexes locks the table, hanging the application. We need a better solution.

Option 1: Schema Change in Downtime
This is the simplest option. Put the application into downtime and perform the schema change. It requires us to have the application down for the duration of the “ALTER TABLE”. We’ve successfully used this option for smaller tables that can be altered in seconds or minutes. However, for large tables the alter can take hours making it less than desirable.