current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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
add comment

1 Answer

up vote 1 down vote accepted

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.

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

Your Answer

 
discard

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.