MENU
Table Maintenance
| ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... |
| This analyses and stores, for a table, the key distribution. A key distribution is used to decide which indexes to use and the order in which tables should be joined. |
| This works with InnoDB, NDB and MyISAM tables. For InnoDB and MyISAM, the table is read-locked during the analysis. |
| NO_WRITE_TO_BINLOG and LOCAL are the same. |
| The SHOW INDEX statement checks a stored key distribution. |
| CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED} |
| This checks tables for errors. |
| This works for InnoDB, MyISAM, ARCHIVE and CSV tables. For MyISAM tables, the key statistics are also updated. |
| FOR UPGRADE checks whether the tables are compatible with the current version of MySQL. If the full check succeeds, the server marks the table’s .frm file with the current MySQL version number. Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. |
| QUICK does not scan the rows for incorrent links. This applies to InnoDB and MyISAM tables and views. |
| FAST checks only tables that have not been closed properly. This applies only to MyISAM tables and views. |
| CHANGED checks only tables that have been changed since the last check or that have not been closed properly. This applies only to MyISAM tables and views, and is ignored for InnoDB. |
| MEDIUM scans rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. This applies only to MyISAM tables and views, and is ignored for InnoDB. |
| EXTENDED does a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time. This applies only to MyISAM tables and views, and is ignored for InnoDB. |
| CHECK TABLE might change the table. This happens if the table is marked as ‘corrupted’ or ‘not closed properly’ but CHECK TABLE does not find any problems in the table. In this case, CHECK TABLE marks the table as okay |
| CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ] |
| This reports a checksum for the contents of a table. The checksum can be used to make sure the contents are the same before and after a backup, rollback, or other operation. |
| EXTENDED, the default, performs the calculation row-by-row. |
| For MyISAM tables created with the CHECKSUM=1 clause, the ‘live’ table checksum can be returned very fast. If the table does not meet all these conditions, the QUICK method returns NULL. |
| OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... |
| This reorganizes the physical storage of table data and associated data, to reduce storage space and improve I/O efficiency when accessing the table. |
| REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] |
| This repairs a possibly corrupted table. The statement only applies to MyISAM, ARCHIVE and CSV tables. |
| QUICK tries to repair only the index file, and not the data file. |
| EXTENDED creates the index row by row instead of creating one index at a time with sorting. |
| The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. Use the USE_FRM option only if you cannot use regular REPAIR modes! Telling the server to ignore the .MYI file makes important table metadata stored in the .MYI unavailable to the repair process, which can have deleterious consequences. |
| If USE_FRM is not used, REPAIR TABLE checks the table to see whether an upgrade is required. If so, it performs the upgrade. |
| By default, the server writes REPAIR TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_ BINLOG keyword or its alias LOCAL. In the event that a table on the master becomes corrupted and you run REPAIR TABLE on it, any resulting changes to the original table are not propagated to slaves. |