Here's more info on the table that needs to be altered:
- 15GB size MyISAM table with Index around 5GB
- Over 100 million rows
- MySQL 5.1 on RHEL 5 with 4GB RAM
- Currently live online
What's the best way to add a couple of new column (minimum 2 columns) to a large table like this? With minimum downtime. Just using direct ALTER table commands would take hours if not a day or two to finish.
Additional question: Will it affect the length of time needed to add new columns if the new column default value are set to NULL instead of having a 'pre-defined' value?
Best Answer
To make online changes to a table you can use pt-online-schema-change
The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.