Table Maintenance in MySQL (Article -16)

Share via:

Table Maintenance  in MySQL.

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

Table maintenance will separated in five ways .They are  :
  1. Check Table
  2. Repair Table
  3. Analyse Table
  4. Optimise Table
  5. mysqlcheck


To get information about above table maintenance options .

Connect to mysql

Check the databases.

Conenct to world database.

Now we can use help contents option to get the topics list 

We need to choose Table maintenance option .

In table maintenance we have five topics.

Check with any option

If you check with any option it contains syntax’s ,description and alse http link of MySQL docs.


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

Check current database and tables list .

Now choose one object and use check option.

Here the table doesn’t have any issues .

Incase if  your table is corrupted msg type is error and text will be corrupted

We can check multiple tables at a time.

Let’s create a view

Now check the object types.

Now drop the base table called t3 and check the status of view

Now check the status of view .

Check the structure of vwt3 view

There is a warning  at the end of the result.

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

Content of repair table.

Repair table will not work for InnoDB Storage Engine.

Will corrupt a table by changing the content in a physical file

choose one table.

Here test2 has MyISAM Storage Engine .

Change content in physical engine

Now check the status of test2 object.

Now repair test2 table.

Now we got corrupt result .

Using FRM file we need repair the object.

Now check the status of test2 object.

3.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 the table full status .

Analyze the table

We can Analyze multiple tables .

4.Optimize 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 INSERTUPDATE and DELETE operations.

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

Optimize the object city.


The mysqlcheck is a command line program used to analyse and repair tables. mysqlcheck connects to a MySQL server and issues theOPTIMIZE TABLEANALYSE TABLECHECK 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:


Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.


Facebook :
LinkedIn :
Twitter :
YouTube :
Instagram :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Add Comment