Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a helpful action?

share|improve this question
up vote 1 down vote accepted


You should perform this as follows:

TRUNCATE TABLE mydb.myisam_mytable;
ALTER TABLE mydb.myisam_table DISABLE KEYS;
LOAD DATA INFILE ... INTO mydb.myisam_table ... ;
ALTER TABLE mydb.myisam_table ENABLE KEYS;
ANALYZE TABLE mydb.myisam_table;

The DISABLE KEYS will allow the LOAD DATA INFILE perform a linear building of the PRIMARY KEY and any UNIQUE indexes.

The ENABLE KEYS will perform a linear building of all non-unique (secondary) indexes.

Skip doing DISABLE KEYS and ENABLE KEYS if there are no secondary indexes.

ANALYZE TABLE compiles index statistics (the last step of an OPTIMIZE TABLE)


OPTIMIZE TABLE would essentially do a reload of the table a second time. That's like doing LOAD DATA INFILE twice.

In other words doing this:

LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
OPTIMIZE TABLE mydb.innodb_table;

would mechanically behave like

LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
ALTER TABLE mydb.innodb_table ENGINE=InnoDB;

No real benefit in the case of InnoDB. Just the LOAD DATA INFILE would be adequate.

share|improve this answer
Great answer, thank you! – Raolin Apr 13 '13 at 4:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.