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!Storage
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:
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 ***************************
As you can see, MySQL remembers the create options exactly as specified. And it chose a representation capable of holding 91 GB of data!Other stuff
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.
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.
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.
If a MyISAM table has no deleted rows, you can insert rows into the table while select queries are running against it.
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.
blog comments powered by Disqus