REPAIR TABLE and OPTIMIZE TABLE

When working with very large amounts of data, it happens to me that MySQL spits out strange error messages when accessing tables, such as “Table xxx is corrupt” or “Table XXX doesn’t exist” (although SHOW TABLES shows the table). This can occur through bugs in MySQL without doing anything wrong or the server having problems with the hardware.

The two commands REPAIR TABLE and OPTIMIZE TABLE provide a remedy. REPAIR TABLE tries to fix corrupt tables and usually does a good job after the kid has fallen into the well. OPTIMIZE TABLE rearranges data in the internal MySQL structure (which also improves performance, especially if you have changed or deleted large amounts of data) and thus helps to avoid errors from the outset.

In projects with large tables, I always set up a crobjob that prophylactically repairs and optimizes all critical tables once a day. This can take a minute or two per table, but it doesn’t harm anyone in the middle of the night and is worthwhile in the long term. Nevertheless, backups are of course always important.