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?
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.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, source code, php, install cairo, laptop, mysql, java, linux, install mysql, mysql initialization, mysql mysql, tools, ubuntu
Similar Tutorials:
- What should be monitored on a busy Database/MysQL server?
- Not-so-well-known differences between MyISAM and Innodb MySQL server storage engines
- How to set default values for MySQL JSON columns
- MySQL tables statistics (size, engine, no. of rows etc.)
- MySQL Query Cache not necessarily a bad thing
Tutorials in 'Database Management Systems > MySQL' (more):
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
- The server requested authentication method unknown to the client [phpmyadmin]
- How to set default values for MySQL JSON columns
- How to delete rows in table when PK is referenced in another table
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