MYSQL tables has many maintenance statements. They are the analyze, backup, check, checksum, optimize, repair and restore. Today I'll be discussing four of them, precisely the check, analyze, optimize and repair. These four are the most familiar statements among the maintenance statements. I'll discuss them one by one. Let's start with "check".
As the statement suggests, it is a maintenance statement that checks if there is an error in the table. The syntax to use this is CHECK TABLE <table name>;. The output in this statement are the table, the op, the msg_type and the msg_text. Here is a sample image:
As you can see the table column contains the name of the table together with its corresponding database. The op column always contains check as its value. The msg_type may contain the values 'status', 'error', 'info' and 'warning'. And finally the msg_text displays an informational message about the table.
The 'check table' statements have different options to be used. They are the quick, fast, changed, medium and extended options. The quick option informs that it must scan the rows to check for incorrect links that is not needed. The fast option informs to check only the tables that have not been closed properly. The changed option checks only the tables that have been changed since the last check or that have not been closed properly. The medium option tells to scans the rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. Finally the exteded option tells to do a full key lookup for all keys for each row. This ensures that the table is 100% consistent. To use these options you can use this syntax: CHECK TABLE <table name> <option>;.
ANALYZE TABLE
Analyze table is the maintenance statement that analyzes and stores the key distribution for a table. The key distribution is used by mysql to decide the order in which tables should be joined when a join statement is used on something other than a constant. In addition, key distributions can be used on deciding which indexes is to be used for a specific table within a query.
The syntax to use this statement is ANALYZE TABLE <table name>;. Just like the check table statement, analyze table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:
OPTIMIZE TABLE
Optimize table is the maintenance statement that is used when you have deleted a large part of a table or if you have made changes to a table with rows that have variable-length. It is used to reclaim the unused space after deleting a row and it is also used to defragment the data file. This statement can improve the performance of statements performed on an optimize table.
The syntax to use this statement is OPTIMIZE TABLE <table name>;. Just like the two table statements above, optimize table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:
REPAIR TABLE
Just like its statement suggest, the repair table is a maintenance statement that repairs a corrupted table. This statement gets back all your data from a myISAM table. Repair table is not a recommended to be used because it may cause data loss but when you need to use it you should first create a backup of your table. Just like the check table statement, repair table also have options like quick and extended.
The syntax to use this statement is REPAIR TABLE <table name>;. Just like the three table statements above, optimize table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:
These statements are the basic table maintenance statements available in MYSQL. Each statements have their additional options to be use. You can explore it more by trying different options if you like but I recommend to backup your tables before using those. You might never know what to expect when using these options.
Source: mysql.com
No comments:
Post a Comment