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.
