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.
 


Comments

Jerry
11/05/2010 07:31

Just a suggestion.
It would be easier to read the text, if the grayscale of the text becomes darker.

Reply
Thomas
11/05/2010 19:27

I don't know who you are but, nice site.

Reply
James link
11/28/2010 15:41

David, welcome back - great to see you posting again. If it were not for weebly, I would still be at the markets!

Thanks,
James
Bay Beans coffee
http://www.baybeans.com.au

Reply
drew link
12/02/2010 13:04

Hi this is drew i signed up for weebly and I think and weebly is awesome And i know that three quarters of every body that posted weebly is awesome just to get the year of weebly pro I truly mean it.

Reply
GameOni link
12/24/2010 08:31

Hey there david your page looks great i was just wondering could you give me a few pointers to make my page a little bit more succesful Happy Xmas From GameOni http://gameoni.weebly.com/index.html

Reply
Joel Stern link
02/02/2011 02:24

Well, the shema will only appear if the Mysql goes threw Index.php/.html and reacts to the database.

Reply

Comments are closed.