MySQL: An alternative to alter
Comments(0) A while ago I was involved in the technical part of an interview with a PHP developer. Obviously some questions were about MySQL. For instance – how to add a new column to existing table. The candidate tried to convince us that there was no way to do that except creating another table with new column and copy data from old table there. Sure thing that we advised him to read about ALTER command.
However it turned out that suggested ‘method’ could have sense. According to Chris at Everything MySQL blog a full dump and reload to a table with new column may be much more faster than alter table! For instance there is shown a test with 5Gb table and alter table is 47% slower. Of course such ‘alternative alter’ should be used only against slave as well as some other restrictions may apply.
Below is the suggested process.
1. If you have a slave, AND YOU SHOULD AT THIS POINT, run stop slave
2. Run the SELECT INTO OUTFILE local, if you have the space, or over nfs (not very fast)
3. DROP or RENAME THE TABLE you are trying to ALTER
— RENAME only if you have the space and you don’t trust THE PROCESS!
4. CREATE the same table with the ALTERATIONS, if you dropped, or a new table with the ALTERATIONS
5. IMPORT the file from step 2
6. Either RENAME the two tables, if you renamed, or start slave
7. Fail over the writes to the newly altered slave
8. Repeat steps 1 – 7 for the old master