How to Rebuild or Repaire Tables or Indexes in MySQL

To rebuild a table with ALTER TABLE, use a “null” alteration; that is, an ALTER TABLE statement that “changes” the table to use the same storage engine that it already has. For example, if t1 is a MyISAM table, use this statement:

mysql> ALTER TABLE t1 ENGINE = MyISAM;

If you must rebuild a table because a table checking operation indicates that the table is corrupt or needs an upgrade, you can use REPAIR TABLE if that statement supports the table's storage engine. For example, to repair a MyISAM table, use this statement:

mysql> REPAIR TABLE t1;

For storage engines such as InnoDB that REPAIR TABLE does not support, use mysqldump to create a dump file and mysql to reload the file.

mysqlcheck --repair provides command-line access to the REPAIR TABLE statement. This can be a more convenient means of repairing tables because you can use the --databases or --all-databases option to repair all tables in specific databases or all databases, respectively:

shell> mysqlcheck --repair --databases db_name ...
shell> mysqlcheck --repair --all-databases

See also:

How to fix MySQL: table index is corrupted