Disclaimer: This worked for me, it might not work for you. Please back up your data before trying any of this.
You usually discover the breaking points of a particular schema once the table reaches a certain size. Unfortunately, at that point, ALTER-ing the table to add the appropriate index can take a really, really, really long time, especially if you get stuck in MySQL's dreaded "Repair With Keycache" phase.
I recently needed to alter a few tables with >50M rows in as little as time as possible. Here's one solution that goes reasonably fast if you have access to a machine with lots of memory:
You usually discover the breaking points of a particular schema once the table reaches a certain size. Unfortunately, at that point, ALTER-ing the table to add the appropriate index can take a really, really, really long time, especially if you get stuck in MySQL's dreaded "Repair With Keycache" phase.
I recently needed to alter a few tables with >50M rows in as little as time as possible. Here's one solution that goes reasonably fast if you have access to a machine with lots of memory:
- Backup your data.
- Shut down the MySQL server you're copying data from.
- Copy the MyISAM table files (large_table.frm, large_table.MYD, large_table.MYI) over to the machine you'll do the ALTER on.
(Make sure you set the permissions properly or MySQL won't be able to read the new table) - Run a SHOW CREATE TABLE large_table.
- Copy the table definition, change the name to something like large_table2 and add your new index at the end.
- Shut down this MySQL server.
- Overwrite the large_table.frm and large_table.MYI files with the large_table2.frm and large_table2.MYI files. (You remembered to backup your data, right?)
- You can safely remove the large_table2.MYD file (it should be zero bytes)
- Next, run the following command:
myisamchk -r --key_buffer_size=1024M --sort_buffer_size=6000M --read_buffer_size=1024M --write_buffer_size=1024M large_table.MYI
(Change the memory numbers to match what you have available) - Once the table has finished rebuilding, copy the large_table.frm, large_table.MYD and large_table.MYI files back to the original server.
- Start up the original MySQL server and your new schema should be in place.
