How to Optimize/Repair a Table (MySQL)

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.

 
  • 2 Users Found This Useful
Was this answer helpful?

Related Articles

OpenVPN Tutorial For Debian/Ubuntu on OpenVZ

What is OpenVPN?OpenVPN is a reliable and well tested VPN solution that is able to run over a...

How do I SSH into my VPS? (OS X/Terminal)

If you are on a Mac, SSH-ing into your VPS is very simple. Simply launch "Terminal" by going to...

How do I SSH into my VPS? (Windows/Putty)

This article will teach you how to access your server via SSH using the PuTTY client on Windows....

Install Remi and EPEL yum repo Centos 7

Install Yum EPEL and Remi repositories on Centos 7 Adding additional useful repo's on Centos 6...

Install PHP/PHP-FPM 5.4 Centos 7

Install PHP/PHP-FPM 5.4 on Centos 7 PHP is a server-side scripting language designed for web...