30.1. Types of Table Maintenance Operations


Table-maintenance operations are useful for identifying and correcting problems with your databases (for example, if a table becomes damaged as a result of a server crash), or for helping MySQL to process queries on your tables more quickly. MySQL enables you to perform several types of maintenance operations:

  • A table check performs an integrity check to make sure that the table's structure and content have no problems. This operation can be done for MyISAM and InnoDB tables.

  • A table repair corrects integrity problems to restore the table to a known, usable state. This operation can be done for MyISAM tables.

  • A table analysis updates statistics about the distribution of index key values. This is information that the optimizer can use to generate better execution plans for queries on the table. This operation can be done for MyISAM and InnoDB tables.

  • A table optimization reorganizes a table so that its contents can be accessed more efficiently. This operation can be done for MyISAM and InnoDB tables.

Table analysis and optimization are operations that you might want to perform periodically to keep your tables performing at their best:

  • When MySQL analyzes a MyISAM or InnoDB table, it updates the index statistics. The optimizer uses these statistics when processing queries to make better decisions about how best to look up records in the table and the order in which to read tables in a join.

  • When MySQL optimizes a MyISAM table, it defragments the data file to reclaim unused space, sorts the indexes, and updates the index statistics. Periodic defragmenting is useful for speeding up table access for tables that contain variable-length columns such as VARCHAR, VARBINARY, BLOB, or TEXT. Inserts and deletes can result in many gaps in such tables, particularly those that are modified frequently. Defragmenting eliminates these gaps.

Table analysis and optimization operations are maximally beneficial when performed on a table that is fully populated and that will not change thereafter. The benefits of analysis and optimization diminish if the table continues to be updated, so you might want to repeat these operations periodically.

The tools at your disposal for table maintenance include SQL statements such as CHECK TABLE and REPAIR TABLE, client programs such as MySQL Administrator and mysqlcheck, the myisamchk utility, and the server's capabilities for auto-recovery. The following sections describe these tools.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net