14.6 MyISAM Table Maintenance

MyISAM tables can be analyzed and optimized. These are operations that you might want to perform periodically to keep your MyISAM tables performing at their best:

• When MyISAM analyzes a table, it updates the index statistics. The MySQL 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 the MyISAM storage engine optimizes a table, it defragments the datafile 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, BLOB, or TEXT. Inserts and deletes can result in many gaps in such tables, particularly those that are modified frequently. Defragmenting eliminates these gaps.

Index analysis and optimization are operations that you can perform once on a table that is fully populated and that will not change thereafter. For tables that continue to be updated, the benefits of analysis and optimization diminish the more the table's contents change, so you might want to repeat these operations periodically.

You can analyze a table several ways. The following examples show how to analyze the Country table in the world database:

• Use the ANALYZE TABLE statement:

  
  

  
  mysql> ANALYZE TABLE world.Country;
  
  

  
  

• Use mysqlcheck or myisamchk with the --analyze option.

Optimize a table as follows:

• Use the OPTIMIZE TABLE statement:

  
  

  
  mysql> OPTIMIZE TABLE world.Country;
  
  

  
  

• Use mysqlcheck:

  
  

  
  shell> mysqlcheck --optimize world Country
  
  

  
  

• myisamchk --sort-index sorts a table's indexes, which speeds up some read operations.

If you use myisamchk to analyze or optimize a MyISAM table, be sure that the server is not using the table at the same time. You can guarantee this by stopping the server first.