MENU
Table Maintenance Commands
MySQL provides a small set of statements dedicated to inspecting and maintaining the physical storage of a table: gathering key distribution statistics, checking for corruption, computing checksums, reclaiming wasted space, and repairing damaged tables. Support for each option varies by storage engine.ANALYZE TABLE
| ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] ... [WITH N BUCKETS] ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name UPDATE HISTOGRAM ON col_name [USING DATA 'json_data'] ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name] ... |
The full ANALYZE TABLE syntax.
ANALYZE TABLE analyzes and stores, for a table, the key distribution. A key distribution is used by the Query Optimizer to decide which indexes to use and the order in which tables should be joined.By default, the server writes ANALYZE TABLE statements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.
The HISTOGRAM clause enables management of histogram statistics for table column values. The generated histogram can be seen in the information_schema.column_statistics table.
| ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS; SELECT * FROM information_schema.column_statistics; ANALYZE TABLE t DROP HISTOGRAM ON c2; |
Building a 10-bucket histogram on two columns, inspecting it, then dropping a histogram from a different column.
ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.column_statistics
WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'c1';Query OK, 0 rows affected
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM |
|---|---|---|---|
| mydb | t | c1 | {"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-10-11 16:13:14.563319", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100} |
CHECK TABLE
| CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED} |
Checks tables for errors. Works for InnoDB, MyISAM, ARCHIVE, and CSV tables. For MyISAM tables, key statistics are also updated.
- FOR UPGRADE – checks whether the tables are compatible with the current MySQL version. If the full check succeeds, the server marks the table's metadata with the current MySQL version number. Incompatibilities can occur when a data type's storage format or sort order has changed.
- QUICK – does not scan the rows for incorrect links. Applies to InnoDB and MyISAM tables and views.
- FAST – checks only tables that have not been closed properly. 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. Applies only to MyISAM tables and views; ignored for InnoDB.
- MEDIUM – scans rows to verify that deleted links are valid, and calculates a key checksum for the rows, verifying it against a calculated checksum for the keys. Applies only to MyISAM tables and views; ignored for InnoDB.
- EXTENDED – does a full key lookup for all keys on each row, ensuring the table is 100% consistent, at the cost of a long runtime. Applies only to MyISAM tables and views; ignored for InnoDB.
CHECKSUM TABLE
| CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ] |
Reports a checksum for the contents of a table, useful for confirming the contents are identical 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, a "live" table checksum can be returned very fast using QUICK; if the table does not meet the conditions for a live checksum, QUICK returns NULL.OPTIMIZE TABLE
| OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... |
Reorganizes the physical storage of table data and associated index data, reducing storage space and improving I/O efficiency when accessing the table.
REPAIR TABLE
| REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] |
Repairs a possibly corrupted table. Applies only to MyISAM, ARCHIVE, and CSV tables.
QUICK tries to repair only the index file, not the data file. EXTENDED creates the index row by row instead of creating one index at a time with sorting. USE_FRM is available if the index file is missing or its header is corrupted; it tells MySQL not to trust the index file header and to re-create it from table metadata instead – use it only when regular repair modes fail, since important metadata stored in the index file header becomes unavailable to the repair process.If USE_FRM is not used, REPAIR TABLE checks whether an upgrade is required and performs it if so. By default the server writes REPAIR TABLE statements to the binary log so they replicate to replicas; specify NO_WRITE_TO_BINLOG or its alias LOCAL to suppress logging. If a table on the source becomes corrupted and REPAIR TABLE is run on it, resulting changes are not propagated to replicas.
-- ANALYZE TABLE: refresh key distribution statistics used by the optimizer
-- (see the histogram example above for building/inspecting a histogram)
ANALYZE LOCAL TABLE t1, t2;
ANALYZE TABLE t DROP HISTOGRAM ON c2;
-- CHECK TABLE: verify table integrity
CHECK TABLE t1, t2 FOR UPGRADE;
CHECK TABLE t1 QUICK;
CHECK TABLE t1 EXTENDED;
-- CHECKSUM TABLE: compare contents before/after a backup or rollback
CHECKSUM TABLE t1;
CHECKSUM TABLE t1 EXTENDED;
-- OPTIMIZE TABLE: reclaim wasted space and defragment
OPTIMIZE TABLE t1;
-- REPAIR TABLE: fix a corrupted MyISAM/ARCHIVE/CSV table
REPAIR TABLE t1;
REPAIR TABLE t1 QUICK EXTENDED;