3

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Steven Musumeche
  • 2,886
  • 5
  • 33
  • 55

2 Answers2

3

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

Yasar GOZUDELI
  • 101
  • 4
  • if cardinality Value is not updated for your your table's indexes myisamchk -rqa /path/to/db/tbl_name options will work properly – Yasar GOZUDELI Apr 27 '11 at 09:13
  • Also note that using `ALTER TABLE DISABLE KEYS` (and `... ENABLE KEYS`) is the same as using the `myisamchk` commands (I was a bit confused if there was a difference, then read rest of MySQL docs)
     – Dolan Antenucci Aug 10 '13 at 20:04
1

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

user3127882
  • 482
  • 5
  • 12