Implementing Table Maintenance in MySQL

By /
Mysql Table Maintenance Statements

Along with implementing optimal queries that give us results faster, it is very important to maintain your database as well for faster operations. In this tutorial, we will learn some useful statements in MySQL that are used to maintain your database. Let’s dive into it!

Also read: MySQL Change Storage Engine

Quick Introduction

So, you might be thinking what is the need of performing database maintenance? Does it get slower over time?

The answer is, yes. Database maintenance or table maintenance is required to identify and correct the database issues such as-

  • Server crash which results in damaged tables.
  • Slow query processing.

There are multiple ways to analyze and maintain your database tables such as –

  • MySQL Workbench
  • Server Auto-Recovery
  • SQL Statements
  • mysqlcheck and myisamchk
  • MySQL Enterprise Monitor

In this tutorial, we will learn SQL statements that are used to perform MySQL table maintenance.

Table Maintenance Using SQL Statements

There are multiple SQL statements available for analyzing, checking, repairing and optimizing the table.

  • ANALYZE TABLE – To update index statistics
  • CHECK TABLE – To check a table or tables for errors.
  • CHECKSUM TABLE – To check integrity properly
  • REPAIR TABLE – To perform repairs if any
  • OPTIMIZE TABLE – To optimize the table

We will see the details of each statement one by one along with its syntax and example.

ANALYZE TABLE Statement

When you do a join on something other than a constant, MySQL utilizes the stored key distribution statistics to determine the order in which the optimizer joins tables. Furthermore, key distributions decide the indexes MySQL uses for each table in a query. To analyze and save the statistics, use the ANALYZE TABLE command, or set InnoDB to gather them automatically after a certain number of data changes or when you query table or index metadata.

In short, ANALYZE TABLE does a key distribution analysis on the named table or tables and saves the results. This statement is comparable to myisamchk – analysis for MyISAM tables.

Following are the characteristics of ANALYZE TABLE statement-

  • MySQL locks the table with a read lock for InnoDB and MyISAM during analysis.
  • To perform analysis, you require SELECT and INSERT privileges.
  • It supports partitioned tables.

Syntax of the ANALYZE TABLE statement is –

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...Code language: SQL (Structured Query Language) (sql)

Let’s analyze an existing table.

 ANALYZE TABLE journaldev.emp;Code language: SQL (Structured Query Language) (sql)
Analyze Table
Analyze Table

MySQL does not analyze the table if it has not changed since running the last ANALYZE TABLE command.

CHECK TABLE Statement

CHECK TABLE looks for faults in a table or tables. CHECK TABLE may also check views for issues, such as tables that are no longer referenced in the view definition.

Following are the characteristics of the CHECK TABLE-

  • It works for InnoDB, MyISAM, ARCHIVE, and CSV tables only.
  • It supports partitioned tables as well.

The FOR UPGRADE option determines whether or not the listed tables are compatible with the current MySQL version. The server uses FOR UPGRADE to check each table to see whether any of the table’s data types or indexes have changed in an incompatible way since the table was created. If this is not the case, the check will be successful. Otherwise, if a suspected conflict exists, the server does a thorough table check (which might take some time).

Syntax of the CHECK TABLE statement is –

CHECK TABLE tbl_name [, tbl_name] ... [option] ...Code language: SQL (Structured Query Language) (sql)

Let’s check an existing table –

CHECK TABLE journaldev.emp;Code language: SQL (Structured Query Language) (sql)
Check Table
Check Table

Repair a table if the output from the CHECK TABLE shows that it has issues. Before fixing a table, you might use the CHECK TABLE command to discover hardware issues (such as faulty memory or bad disc sectors). Normally, the Msg text output column is ok. Run a table repair if you don’t get either OK or the Table is already up to date. If the table is identified as corrupted or not properly closed, yet CHECK TABLE finds no problems with it, it is regarded as OK.

CHECKSUM TABLE Statement

CHECKSUM TABLE returns a checksum for a table’s contents. This statement can be used to ensure that the contents are the same before and after a backup, rollback, or other action that restores the data to a known state.

Following are the characteristics of the CHECKSUM TABLE-

  • It requires the SELECT privilege on the table.
  • It does not support views. The checksum value will be NULL if you run the statement against a view and will return a warning.
  • It will return the checksum value NULL and a warning if you run the statement against a non-existing table.
  • The table is locked with a read lock for InnoDB and MyISAM during the checksum operation.

Syntax of the CHECKSUM TABLE is-

CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED];Code language: SQL (Structured Query Language) (sql)

Let’s find a checksum value of an existing table.

CHECKSUM TABLE journaldev.emp;Code language: SQL (Structured Query Language) (sql)
Checksum Table
Checksum Table

The value of the checksum is determined by the table row format. When the row format changes, so do the checksum. For example, after MySQL 4.1, the storage format for VARCHAR changed, therefore if you upgrade a 4.1 table to a later version, the checksum value will change if the table contains VARCHAR columns.

OPTIMIZE TABLE Statement

To save storage space and enhance I/O efficiency while accessing the database, OPTIMIZE TABLE reorganizes the physical storage of table data and related index data by defragmenting it. The specific modifications performed to each table are determined by the table’s storage engine.

Following are the characteristics of the OPTIMIZE TABLE-

  • Defragmenting entails recovering unnecessary space caused by deletions and updates, as well as consolidating records that have gotten divided and stored in several locations.
  • It requires SELECT and INSERT privileges on the table.
  • It works for InnoDB, MyISAM, and ARCHIVE tables only.

After updating a large number of rows, you may use the OPTIMIZE TABLE query to rebuild a FULLTEXT index in InnoDB. OPTIMIZE TABLE is mapped to ALTER TABLE in InnoDB tables, which rebuilds the table to update index statistics and clear up unnecessary space in the clustered index. Because InnoDB does not suffer from fragmentation like other storage engines, you won’t need to use OPTIMIZE TABLE very often.

Syntax of the OPTIMIZE table is:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...Code language: SQL (Structured Query Language) (sql)

Let’s optimize an existing table:

OPTIMIZE TABLE is mapped to ALTER TABLE… FORCE in InnoDB tables, which rebuilds the table to update index statistics and free up space in the clustered index. When you run OPTIMIZE TABLE on an InnoDB table, this appears in the output, as shown below:

OPTIMIZE TABLE uuids;Code language: SQL (Structured Query Language) (sql)
Optimize Table
Optimize Table

REPAIR TABLE Statement

REPAIR TABLE works only for particular storage engines, and it fixes a potentially corrupted table.

Following are the characteristics of the REPAIR TABLE-

  • It requires SELECT and INSERT privileges on the table.
  • QUICK option: Only attempts to fix the index file rather than the data file. This form of correction is similar to what myisamchk –recover –fast does.
  • EXTENDED option: Instead of building one index at a time with sorting, MySQL builds the index row by row. This is similar to the work done by myisamchk –safe-recover.
  • USE_FRM option: If the.MYI index file is missing or its header is malformed, the USE FRM option is available. This option instructs MySQL not to trust the information in the.MYI file header and to re-create it using data dictionary information. Myisamchk is unable to do this type of repair.

Before doing a table repair procedure, it is essential to make a backup of the table; in some cases, the action may result in data loss. File system faults are one of the possible reasons, although they are not the only ones.

To avoid additional corruption, if the server breaks during a REPAIR TABLE action, you should immediately restart the server and run another REPAIR TABLE before completing any other activities.

If you regularly need to use REPAIR TABLE to recover from corrupt tables, attempt to figure out what’s causing the problem so you can avoid having to use REPAIR TABLE.

Syntax of the REPAIR TABLE is –

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]Code language: SQL (Structured Query Language) (sql)

Let’s repair an existing table-

REPAIR TABLE uuids;Code language: SQL (Structured Query Language) (sql)
Repair Table
Repair Table

Here, the table is of type InnoDB, hence the repair will not work on it. We will change the storage engine of the table to MyISAM and re-run the statement.

Repair Table OK
Repair Table OK

As you can see, there is no need to repair the table hence it returned the message “OK”.

Summary

In this tutorial, we learned-

  • What is mysql table maintenance and why do we need it.
  • Different ways of table maintenance.
  • SQL Statements which are used for mysql table maintenance.
  • ANALYZE TABLE Statement
  • CHECK TABLE Statement
  • CHECKSUM TABLE Statement
  • REPAIR TABLE Statement
  • OPTIMIZE TABLE Statement
  • Details and information of each statement.

References

MySQL official documentation on Table Maintenance Statements