MyISAM Tables - MySQL

As MySQL’s default storage engine, MyISAM provides a good compromise between performance and useful features. Versions of MySQL prior to 3.23 used the Index Sequential Access Method (ISAM) table format. In Version 3.23, ISAM tables were deprecated in favor of MyISAM, an enhanced ISAM format.* MyISAM tables don’t provide transactions or a very granular locking model, but they do have full-text indexing (see Chapter 4), compression, and more.

* ISAM tables may be used in MySQL 4.0 and 4.1. Presumably they’ll vanish sometime in the 5.x release cycle. If you’re still using ISAM tables, it’s time to upgrade to MyISAM!

In MyISAM storage, there are typically two files: a data file and an index file. The two files bear .MYD and .MYI extensions, respectively. The MyISAM format is plat-form-neutral, meaning you can copy the data and index files from an Intel-based server to a Macintosh PowerBook or Sun SPARC without any trouble.

MyISAM tables can contain either dynamic or static (fixed-length) rows. MySQL decides which format to use based on the table definition. The number of rows a MyISAM table can hold is limited primarily by the available disk space on your database server and the largest file your operating system will let you create. Some (mostly older) operating systems have been known to cut you off at 2 GB, so check your local documentation.

However, MyISAM files with variable-length rows, are set up by default to handle only 4 GB of data, mainly for efficiency. The index uses 32-bit pointers to the data records. To create a MyISAM table that can hold more than 4 GB, you must specify values for the MAX_ROWS and AVG_ROW_LENGTH options that represent ballpark figures for the amount of space you need:

CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;

In the example, we’ve told MySQL to be prepared to store at least 32 GB of data in the table. To find out what MySQL decided to do, simply ask for the table status:

mysql> SHOW TABLE STATUS LIKE 'mytable' \G

******************* 1. row ***************************
Name: mytable
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 98784247807
Index_length: 1024 Data_free: 0
Auto_increment: NULL
Create_time: 2002-02-24 17:36:57
Update_time: 2002-02-24 17:36:57
Check_time: NULL
Create_options: max_rows=1000000000 avg_row_length=32
Comment:
1 row in set (0.05 sec)

As you can see, MySQL remembers the create options exactly as specified. And it chose a representation capable of holding 91 GB of data!

As one of the oldest storage engines included in MySQL, MyISAM tables have a number of features that have been developed over time specifically to fill niche needs uncovered through years of use:

Locking and concurrency

Locking in MyISAM tables is performed at the table level. Readers obtain shared (read) locks on all tables they need to read. Writers obtain exclusive (write) locks.

Automatic repair

If MySQL is started with the --myisam-recover option, the first time it opens a MyISAM table, it examines the table to determine whether it was closed properly. If it was not (probably because of a hardware problem or power outage), MySQL scans the table for problems and repairs them. The downside, of course, is that your application must wait while a table it needs is being repaired.

Manual repair

You can use the CHECK TABLE mytable and REPAIR TABLE mytable commands to check a table for errors and repair them. The myisamchk command-line tool can also be used to check and repair tables when the server is offline.

Concurrency improvements

If a MyISAM table has no deleted rows, you can insert rows into the table while select queries are running against it.

Index features

BLOB and TEXT columns in a MyISAM table can be indexed. MyISAM tables have a limit of 500 bytes on each key, however, so the index uses only the first few hundred bytes of a BLOB or TEXT field. MyISAM tables also allow you to index columns that may contain NULL values. You can find more information on MyISAM indexes in Chapter 4.

Delayed key writes

MyISAM tables marked with the DELAY_KEY_WRITE create option don’t have index changes written to disk as they are made. Instead, the changes are made to the in-memory key buffer only and flushed to disk when the associated blocks are pruned from the key buffer or when the table is closed. This can yield quite a performance boost on heavily used tables that change frequently.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.