Repair vs Optimize table in MySQL

Solution 1:

• REPAIR TABLE Fixes Table Corruption Issues, such as Open File Handle Counts, Resolution of Rows with Variable Length Data, and so forth.
• OPTIMIZE TABLE simply copies the table to remove unused space. If the table is MyISAM, ANALYZE TABLE is also performed to update index statistics for the sake of the Query Optimizer. If the table is InnoDB, ANALYZE TABLE is bypassed.

You could have mysqld auto check and repair all MyISAM tables.

In fact the book MySQL 5.0 Certification Study Guide, Section 30.5, Pages 444,445 state:

The MySQL server can be instructed to check and repair MyISAM tables automatically. With automatic repair enabled, the server checks each MyISAM table when it opens it to see whether the table was closed properly the last time it was used and is not marked as needing repair. If the table is not OK, the server repairs it.

To enable automatic MyISAM table maintenance, start the server with the --myisam-recover option, The option value can consist if a comma-separated list of one or more of the following values:

• DEFAULT for the default checking.
• BACKUP tells the server to make a backup of any table that is must change.
• FORCE causes table recovery to be performed even if it would cause the loss of more than one row of data.
• QUICK performs quick recovery : Tables that have no holes resulting from deletes or updates are skipped.

For example, to tell the server to perform a force recovery of MyISAM tables found to have problems but make a backup of any tables it changes, you can put the following lines in an option file:

[mysqld] myisam-recover=FORCE,BACKUP

You could also create a file called /root/StartUp.sql and put the REPAIR TABLE commands you want inside. Then add init-file=/root/StartUp.sql to /etc/my.cnf and restart mysql to trigger the init script.