MySQL optimized analyze Table

http://blog.csdn.net/alongken2005/article/details/6394016

Analyze Table

MySQL Optimizer (optimized element) when optimizing SQL statements, you first need to gather some relevant information, including the table's cardinality (which can be translated to "hash extent"), which indicates how many different values the column corresponding to an index contains- If the cardinality is significantly less than the actual hash of the data, the index basically fails.
We can use the show index statement to see how much the index is hashed:

SHOW INDEX from PLAYERS;

TABLE key_name column_name cardinality
------- -------- ----------- -----------
PLAYERS PRIMARY Playerno

because the number of different playerno in the player table is far more than 14, the index basically fails.
below we fix the index by Analyze table statement:

ANALYZE TABLE PLAYERS;
SHOW INDEX from PLAYERS;
The result is:
TABLE key_name column_name cardinality
------- -------- ----------- -----------
PLAYERS PRIMARY Playerno

now that the index has been fixed, the query is much more efficient.

It is important to note that if Binlog is turned on, the results of analyze table will also be written to Binlog, and we can add the keyword local cancel write between analyze and table.

Checksum Table

The data may change during transmission, or it may be damaged for other reasons, and we can calculate the checksum (checksum value) in order to ensure consistent data.
tables that use the MyISAM engine store checksum as live checksum, and checksum changes as the data changes.
when executing checksum table, you can specify the option Qiuck or Extended;quick to return the stored checksum value at the end, and extended will recalculate checksum if no option is specified. Extended is used by default.

Optimize Table

disks that frequently update data need to be defragmented, as is the case with Optimize table statements, which are valid for tables of type MyISAM and InnoDB.
If the table is updated frequently, you should run the Optimize table statement regularly to ensure efficiency.
As with analyze table, Optimize table can also use local to cancel write Binlog.

Check Table

databases can often encounter errors, such as when data is written to disk, or if the index is not synchronized, or if the database has not been shut down MySQL is stopped.
in these cases, the data may have an error:
incorrect key file for table: '. Try to repair it.
at this point, we can use the Check table statement to examine the tables and their corresponding indexes.
For example, we run
CHECK TABLE PLAYERS;

The result is
TABLE OP msg_type msg_text
-------------- ----- -------- --------
tennis. PLAYERS Check Status OK

MySQL saves the time of the last check on the table, which is stored each time the check table is run:

Execution
SELECT table_name, Check_time
From information_schema. TABLES
WHERE table_name = ' PLAYERS '
and Table_schema = ' tennis '; /*tennis is the database name * /

The result is

table_name Check_time
----------   -------------------
PLAYERS 2006-08-21 16:44:25

Check table can also specify additional options:
UPGRADE: Used to test whether tables established in earlier versions of MySQL are compatible with the current version.
Quick: The fastest option to check the data for each column does not check the link's correctness or not, and you can use this option if you are not experiencing any problems.
FAST: Check that the table is shut down properly and use this option if you do not experience serious problems after the system has been powered down.
CHANGED: Only the data updated after the last check time is checked.
MEDIUM: The default option checks the correctness of the link between the index file and the data file.
EXTENDED: The slowest option is a full-scale check.

Repair Table

for repairing tables, only valid for tables of type MyISAM and archive.
This statement can also specify options:
Quick: The quickest option is to repair only the index tree.
EXTENDED: The slowest option, you need to rebuild the index row by line.
use_frm: Use this option only if the Myi file is missing and completely rebuild the entire index.

As with analyze table, Repair table can also use local to cancel write Binlog.

MySQL optimized analyze Table