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