Mysql – the fastest way to optimize a MySQL table

innodbMySQLoptimizationsql

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 😉

Best Answer

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.

Related Topic