MySQL: OPTIMIZE after TRUNCATE?

Question

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?

Answer 1

MyISAM

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)

InnoDB

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.