How to add an index to a large MyISAM table

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:

1. Backup your data.
2. Shut down the MySQL server you're copying data from.
3. 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)
4. Run a SHOW CREATE TABLE large_table.
5. Copy the table definition, change the name to something like large_table2 and add your new index at the end.
6. Shut down this MySQL server.
7. 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?)
8. You can safely remove the large_table2.MYD file (it should be zero bytes)
9. 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)
10. Once the table has finished rebuilding, copy the large_table.frm, large_table.MYD and large_table.MYI files back to the original server.
11. Start up the original MySQL server and your new schema should be in place.