Q:

Repair vs Optimize table in MySQL

Does Optimize include repair table or vice versa internally?
Is it possible to execute repair table only if found corrupt, any tools can support this?

(I am using MyISAM table engine)

A:

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.

