MySQL Performance Optimization Part II

You can speed up index scans if you periodically perform a void ALTER TABLE operation, which causes MySQL to rebuild the table:

ALTER TABLE tbl_name ENGINE=INNODB;

or 

ALTER TABLE tbl_name ENGINE=MYISAM;

Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.

Comments (write a comment):

For MyISAM I like to wait until the "Data_free" in SHOW TABLE STATUS is over 10%.

Even if Data_free is small, you might need reconstruction. For example, if you have "dynamic" (as opposed to "fixed") rows, and you have lots of deletes and inserts, the inserts may split the records in order to fill in multiple holes in the .MYD file. This means that fetching a single row may have to jump around in the file to find all its parts. Hence, if you have a lot of churn, consider doing the ALTER.

For InnoDB, there is not much need for rebuilding the table. All data (and indexes) are stored in 16KB blocks. There is an internal cleanup algorithm that makes some attempt at combining adjacent blocks that are not very full. Posted by: Rick James on Feb 27, 2012

leave a comment on tutorial leave a comment