Table Maintenance

The Table Maintenance Wizard allows you to perform native MySQL operations for analyzing, checking, optimizing and repairing tables. To run the wizard select Tools | Administrations Tools | Maintain Tables.

The first step of the wizard allows you to select the maintenance operations to perform.

Check options to execute the corresponding operations. You should select at least one operation to proceed to the next step.

Analyze Tables

This operation allows you to analyze and store the key distribution for the table. During the analysis the table is locked with a read lock. Currently MySQL supports analyzing only for MyISAM tables and transaction-safe type tables. 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.

Check Tables

This operation allows you to check the database tables on errors. Currently works only for MyISAM and transaction-safe type tables.

Optimize Tables

This operation allows you to repair the database tables that may be corrupted. Currently works only for MyISAM and transaction-safe type tables.

Repair Tables

This operation should be used 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 record positions. You can use optimization to reclaim the unused space and to defragment the data file.

The second step is intended for selecting the operation tables.

The Available Tables contains all tables from the current database. Move the tables to the Selected Tables list to include them into the operations to execute.

The next step allows you to define the additional options for Check and Repair operations.

The following check options are available:

    Quick

Don't scan the rows to check for incorrect links.

    Fast

Only check tables that haven't been closed properly.

    Changed

Only check tables that have been changed since the last check or haven't been closed properly.

    Medium

Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.

    Extended

Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time.

The following repair options are available:

Quick

Tries to repair only the index tree

Extended

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 well.

The final step of the wizard displays the log of executing the operations.

Click Finish to start performing the selected operations with the options you defined.

After all operations are completed, you can view the result of the execution within the Table Maintenance Result window.