How do I check and repair MySQL tables?

Share

Tweet

Share

Share

Email

Comments

To check a certain table for consistency with MySQL after you select the database, you can run:

mysql$ CHECK TABLE table_name;


If the tables in your databases say OK then there is not an issue.  However if instead of OK the CHECK TABLE reports Corrupt, then you have a broken table and you have to fix it as soon as possible.

Here is an example of a broken table after a CHECK REPAIR searchindex; :

+------------------+-------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+------------------------------------+
| test.searchindex | check | error | Key in wrong position at page 4096 |
| test.searchindex | check | error | Corrupt |
+------------------+-------+----------+------------------------------------+

To fix the CORRUPTED or BROKEN table as also known you have to issue the command:

mysql$ REPAIR TABLE yourtable_name;


Depending on your table size after a while you will get an OK message like the one below.

+------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------+----------+----------+
| test.searchindex | repair | status | OK |
+------------------+--------+----------+----------+
1 row in set (0.08 sec)


Expect The EXTENDED REPAIR function option to take a much more time, even sometimes with large databases with millions of records it could take hours, especially if the MySQL server is serving other client requests as well.

This terrible siutation sometimes occurs because of MySQL locks.

ANALYZE TABLE does synchronization of the information concerning the variables within tables that has a INDEX key settled according to the database to which they belong.  In other words, executing ANALYZE TABLE to your database tables every now and then and that would probably help in speeding up the code executed in the SQL that has JOINS involved.

The second one OPTIMIZE TABLE is natively supported with MyISAM SQL database types, and secondary supported with Innodb, where the Optimize with Innodb is done in a non-traditional way.

When invoked to process an Innodb table OPTIMIZE TABLE does use ALTER TABLE to achieve an Innodb table optimization.

In practice what the optimize table does is defragmentation of the table unto which it’s executed.

An example of the optimize table is for instance:

OPTIMIZE TABLE your_table_name;


In order to find out which tables need to be defragmented or in other words needs optimize table you have to issue the command:

show table status where Data_free!=0;


NOTE: You have to issue this command on each of your databases.