MySQL Table Maintenance

Introduction

This page contains several commands you will use to optimize, analyse and repair tables. It also presents the mysqlcheck command line tool

This page is part of the MySQL administration series:

Optimise Table

The OPTIMIZE TABLE statement reorganises the physical storage of table data and associated indexes. It reduces storage space and improves I/O.

OPTIMIZE TABLE is useful when the table is highly fragmented after doing a large number of INSERT, UPDATE and DELETE operations.

The OPTIMIZE TABLE can also be used to release unused disk spaced of an InnoDB table using innodb_file_per_table.

Analyse Table

The ANALYZE TABLE statement analyses the key distribution of a table. It locks the table with a read lock while the analysis takes place.

MySQL uses the key distribution to decide the order to use tables during a join operation. Key distribution can also be used to decide what indexes to use.

It returns a result informing the result:

Check Table

The CHECK TABLE statement checks a table for errors. If CHECK TABLE finds an issue with the table, it marks it as corrupted and cannot be used until it is repaired.

Repair Table

The REPAIR TABLE repairs a possibly corrupted table. It is only available for MyISAM, ARCHIVE and CSV storage engines. REPAIR TABLE does is not available for InnoDB.

mysqlcheck

The mysqlcheck is a command line program used to analyse and repair tables. mysqlcheck connects to a MySQL server and issues the OPTIMIZE TABLE, ANALYSE TABLE, CHECK TABLE and REPAIR TABLE statements.

This is the syntax:

When no table name provided, then mysqlcheck executes for all tables of the database. The benefit of mysqlcheck is that it can operate at database level.

The following command optimises all tables in a database:

The following command analyses all tables in a database:

The following command checks all tables in a database and repairs them if corrupted. Please note that mysqlcheck can only repair MyISAM, ARCHIVE and CSV tables. It cannot repair InnoDB tables:

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply