MySQL Statements For Table Maintenance

MySQL Statements For Table Maintenance

-CHECK TABLE    -------> For integrity checking

-REPAIR TABLE   -------> For repairs

-ANALYZE TABLE  -------> For analysis

-OPTIMIZE TABLE -------> For optimization

1.CHECK TABLE

The check table statement performs an integrity check on table structure and contents, and if the output from CHECK TABLE indicates that a table has problems, the table structure should be repaired.

check table <table_name>

check table <table_name>,<table_name>,<table_name>

2.REPAIR TABLE 

The repair table statement corrects the problem in a table that has become corrupted.

repair table <table_name>

repair table <table_name>,<table_name>,<table_name>

3.ANALYZE TABLE

The analyze table statement updates a table with information about the distribution of key values in the table, and this information is used the optimizer to make better choices about query execution plans.

analyzer table <table_name>

analyzer table <table_name>,<table_name>,<table_name>

4.OPTIMIZE TABLE

The optimize table statement cleans up a MyISAM table by defragmenting it, It involves reclaiming unused space resulting from deletes & updates, coalescing split records and stored non-contiguously and it also sorts the index pages if they are out of order and updates the index statistics.

optimize table <table_name>

optimize table <table_name>,<table_name>,<table_name>

To optimize all tables in all MySQL database using mysqlcheck 

mysqlcheck -o --all-databases -u root -p

To repair multiple MySQL databases using mysqlcheck

mysqlcheck -r --databases mysql qtest

To analyze databases using mysqlcheck

mysqlcheck -u root -p --analyze mysql