How to do it

Select the table you want to maintain, then click either Optimize Table, Analyze Table, Check Table or Repair Table from the Table menu to start the operation. Navicat will show all the resulting output of the operation.

NOTE: Navicat maintains the tables by issuing standard OPTIMIZE TABLE, CHECK TABLE, ANALYZE TABLE and REPAIR TABLE statements to the MySQL server. This is not supported in all versions of MySQL and is only applicable to MyISAM table types at this moment. Please refer to the MySQL documentations for more details on the isamchk and myisamchk command line utilities.

Optimize tables

The main reason for optimizing your table is to reclaim unused space and to defragment the data file. You should optimize a table if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old row positions.

Currently, MySQL supports optimizing only for MyISAM, InnoDB and BDB tables.

For MyISAM tables, Optimize Table works as follows:

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

Check tables

Check Table checks a table or tables for errors. Currently, MySQL supports checking only for MyISAM, InnoDB and ARCHIVE tables. For MyISAM tables, the key statistics are updated as well.

Check Table returns a result set with the following columns:

You might get many rows of information for each checked table. The last row has a Msg_type value of status and the Msg_text normally should be OK. If you do not get OK, or Table is already up to date you should normally run a repair of the table. Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

The other check options that can be given are shown in the following table:

Analyze tables

Analyze Table analyzes and stores the key distribution for the table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. Currently, MySQL supports analyzing only for MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.

Analyze Table returns a result set with the following columns:

You can check the stored key distribution with the SHOW INDEX command. If the table hasn't changed since the last ANALYZE TABLE command, the table will not be analyzed again.

Repair tables

Repair the corrupted table. The operation returns a table with the following columns:

Note that you can get many rows of information for each repaired table. The last one row will be of Msg type status and should normally be OK. If you don't get OK, you should try repairing the table with myisamchk -o, as REPAIR TABLE does not yet implement all the options of myisamchk. With myisamchk --safe-recover, you can also use options that Repair Table does not support, such as --max-record-length.

If you use QUICK then MySQL will try to do a repair of only the index tree.

If you use EXTENDED then MySQL will create the index row by row instead of creating one index at a time with sorting; This may be better than sorting on fixed length keys if you have long char() keys that compress very good.