Load Data Infile + Disable/Enable Keys Performance

Question

I have a table with approximately 7 million rows. Once a day, I need to bulk import around 200,000 new rows into this table. To do this, I am first disabling keys on the table, using LOAD DATA INFILE, and then re-enabling keys on the table.

The problem I am having is with the ALTER TABLE my_table ENABLE KEYS statement.
It takes around 15 minutes to complete. I am trying to improve the performance by increasing myisam_sort_buffer_size, but it doesn't seem to help. Any other ideas?

Answer 1

If you have 7 million records the size of your index tree on disk is likely to be large. When you add 200,000 records to the DB the index needs to be updated for those records and it's going to cause a lot of IO.

One solution is to put the index on a different hard drive the second is to look closely at the index to see if you can reduce it's size a bit.