David Rusenko
  • Blog
  • Photography
  • About
  • Contact
David Rusenko

How to add an index to a large MyISAM table

10/25/2010

 
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.
Jerry
11/5/2010 12:31:12 am

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

Thomas
11/5/2010 12:27:15 pm

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

James link
11/28/2010 07:41:36 am

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

drew link
12/2/2010 05:04:41 am

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.

GameOni link
12/24/2010 12:31:19 am

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

Joel Stern link
2/1/2011 06:24:51 pm

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


Comments are closed.
    Picture
    David co-founded Weebly, an incredibly easy to use tool that helps millions of people create a professional web site, blog or online store.

    He was named to Forbes'  30 under 30 list, is a part-time DJ and has traveled to over 20 countries.

    Investments include Cue, Parse, Exec, Churchkey, Streak, Incident Technologies, Adioso and Zenefits.

    RSS Feed


    Categories

    All
    Bobbyore
    Day To Day
    Misc
    Music
    Open Source
    Product Reviews
    Raising Money
    Rant
    San Francisco
    Scaling
    Startups
    Troubleshooting

    Blogroll

    Jessica Livingston
    Robby Walker
    Adam Smith

    Justin.tv
    Venture Hacks
    Uncrate
    Juno Day

    Flickr Photos

Proudly powered by Weebly