Load Data Infile + Disable/Enable Keys Performance

I have a table w开发者_如何学运维ith 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?

You can try external MySQL tools like mysqladmin and myisamchk. They are in /usr/local/mysql/bin path for a regular installation.

A solution path from the MySQL Web Site:

• Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

• Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This removes all use of indexes for the table.

• Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.

• If you intend only to read from the table in the future, use myisampack to compress it. See Section 13.4.3.3, “Compressed Table Characteristics”.

• Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

• Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

mysql official documents

Assuming that you want to minimize downtime caused by your table being locked, I believe that standard procedure is to clone the table, perform the insertion in the clone (with or without disabling keys, depending on your specific circumstances) and once done, issue a DROP TABLE tableName;RENAME TABLE tableClone TO tableName