MyISAM Indexing Features

This section devotes attention to some special performance-related features offered by the MyISAM engine. These include the key cache, which is an in-memory cache designed to reduce the need for costly disk access, the myisamchk utility, as well as a number of options to change the physical structure of a table.

Key Cache

Indexes speed access to information by providing "shortcuts" to data. For example, an index on last names is sorted alphabetically, which means you can use this sorted list to navigate very quickly to a particular name, or even a range of names.

MySQL takes these already-fast data access shortcuts and speeds them even further by giving administrators the option to store frequently used indexes in memory. Because in-memory operations are usually 10 times (or more) faster than disk operations, the savings can really add up. For the MyISAM storage engine, these cached indexes are held in a structure called the key cache. Unlike the InnoDB buffer pool, MyISAM offers no internal memory caching structures for data per se; the operating system's buffering features usually look after that responsibility. In fact, if you disable the key cache, you will still usually benefit from the built-in operating system based caching found on most modern systems.

The myisamchk Utility

MySQL provides the myisamchk utility to let database administrators perform a wide variety of vital administration tasks, including the following:

• Integrity checking

• Repair

• Analysis

• Restructuring

• Optimization

Chapter 11, dedicated to the MyISAM storage engine, examines both the key cache (including how you can configure and affect its behavior) as well as using the myisamchk utility to boost performance.

Index and Table Compression

MyISAM helps conserve disk space by automatically compressing CHAR and VARCHAR columns; you can also request numeric column compression by specifying PACK_KEYS=1 when generating your table. If disk space is truly at a premium, and you aren't performing any data modifications, you can use myisampack to compress an entire table.