How to Optimize/Repair a Table

From Brian Nelson Ramblings
Jump to: navigation, search

How to Optimize and Repair your MySQL Tables

Does your database feel like it has slowed down? Your pages starting to load slower and slower? It might be time to some database optimizations.

ANALYZE TABLE <table_name>

First thing I run when check tables it ANALYZE TABLE which analyes and stores the key distribution for the table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

REPAIR TABLE <table_name>

REPAIR TABLE only applies to MyISAM, ARCHIVE, and CSV tables. See Section 14.3, The MyISAM Storage Engine, and Section 14.6, The ARCHIVE Storage Engine, and Section 14.5, The CSV Storage Engine

OPTIMIZE TABLE <table_name>

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

Looking a one liner that will do repair, check and optimize on all databases. I would recommend setting up an alias in your bash, so you do not put your root password in your bash history.

mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --optimize --all-databases

To see if a table has crashed, you can check your mysqld.log

less /var/log/mysql.log

Once you have identified the table you can use the commands above to repair and optimize that table.

Additional Articles