MySQL Performance Optimization Part II

Category:

Tutorials > Database Management Systems > MySQL > Administration

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 do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

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 J on Feb 27, 2012

leave a comment on tutorial leave a comment