I recently had a situation where i had to alter huge tables(having >3 Million records). Normally we go with standard process of altering the table, you go to SQL editor, type in your SQL command, execute it and table is altered. But when it comes to huge tables, you may want to alter your approach.
There are 2 ways to alter big table:
- Create a new table(copy of original table), make necessary changes to schema, stop the original table to store new data, copy the original table data to new table, rename tables. And we are done.
Note : when you have table constantly changing then going with this option is not ideal. It will result in data loss and inconvenience for the users.
- Use Percona toolkit
Percona is a reliable tool to alter your tables without any data loss and minimum downtime(almost 0 minutes). Percona doesn’t stop alter, modify options while running, and takes into account the changes done while it is performing the operation.