Table maintenance in DMS
Data Management Service (DMS) lets you run MySQL table maintenance commands directly from the SQL console. This topic covers four operations: optimizing, checking, repairing, and analyzing tables.
Optimize a table
Use OPTIMIZE TABLE after large-scale row deletions or frequent updates to tables with variable-length columns (such as VARCHAR, BLOB, or TEXT). The statement reclaims unused space and defragments the data file.
Syntax:
OPTIMIZE TABLE <table_name>
Check a table
CHECK TABLE scans a table for structural errors and data integrity issues. Use it to verify table health before and after maintenance operations.
Syntax:
CHECK TABLE <table_name> [option]The following options control the scope of the check:
Option | Description | InnoDB behavior |
FAST | Checks tables without scanning rows for incorrect links. | Falls back to a standard check. |
QUICK | Checks only tables that were not closed correctly. | Falls back to a standard check. |
CHANGED | Checks only tables that have changed since the last check or were not properly closed. | Falls back to a standard check. |
(none) | Scans rows to verify that invalid links are cleared and calculates and verifies the key checksum. | Fully supported. |
EXTENDED | Performs a full keyword search across every row to ensure 100% table consistency. Takes significantly longer to run. | Fully supported. |
Execution speed from fastest to slowest: FAST > QUICK > CHANGED > (none) > EXTENDED.
Repair a table
REPAIR TABLE fixes errors in a table's data file and index file. Use it when a table is marked as corrupt or when CHECK TABLE reports errors.
Syntax:
REPAIR TABLE <table_name> [option]The following options control the repair method:
Option | Command | Description |
(none) |
| Performs a simple repair of both the data file and the index file. |
QUICK |
| Repairs only the index file. Fastest option, but does not repair the data file. |
EXTENDED |
| Repairs both the data file and the index file by recovering each row from the data file and reindexing row by row. |
Execution speed from fastest to slowest: (none) > QUICK > EXTENDED.
Analyze a table
ANALYZE TABLE updates the index statistics that the query optimizer uses to select efficient execution plans. Run it after large data changes or if you observe unexpectedly slow queries.
Syntax:
ANALYZE TABLE <table_name>