Friday, April 22, 2022

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

No comments:

Post a Comment