Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've ran many different DELETE queries against many tables in the huge database (hundreds of millions records). What would be faster - to run OPTIMIZE TABLE against each modified table. Or just to mysqldump into the file and then restore back from the file?

Think that this will be equal. Just mysqldump | mysql will be faster ;)

share|improve this question

1 Answer 1

up vote 4 down vote accepted

OPTIMISE TABLE will be faster as the data will not have to be converted to a textual format used by mysqldump and then digested by the mysql command line client.

If you are using InnoDB then you can do another trick,

ALTER TABLE $table ENGINE innodb;

This will rebuild the table by copying all the rows to a new table space and replacing the current table once done. However, while this is happening, the original table will not block readers. Writers will, of course, be blocked from updating until the operation is complete.

share|improve this answer
    
Just to be clear, this works if the table is already InnoDB? –  John Gardeniers Feb 27 '10 at 8:32
    
This command will convert the table format to InnoDB, one of the side effects of running it on a table that is already in InnoDB format, is that it will be rebuilt. –  Dave Cheney Feb 27 '10 at 8:48
    

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.