The MyISAM Storage Engine in MySQL

MyISAM is the default storage engine as of MySQL 3.23. It is based on the ISAM code but has many useful extensions.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. 

• An .frm file stores the table definition. 
• The data file has an .MYD (MYData) extension. 
• The index file has an .MYI (MYIndex) extension,

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE or TYPE table option:

CREATE TABLE t (i INT) ENGINE = MYISAM;

CREATE TABLE t (i INT) TYPE = MYISAM;

Normally, the ENGINE or TYPE option is unnecessary; MyISAM is the default storage engine unless the default has been changed.

You can check or repair MyISAM tables with the myisamchk utility. You can compress MyISAM tables with myisampack to take up much less space. 

The following characteristics of the MyISAM storage engine are improvements over the older ISAM engine:

• All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirement for binary portability is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems, which sometimes have peculiar processors.
• There is no big speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code.
• Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
• Dynamic-sized rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This can be changed by recompiling. The maximum number of columns per index is 16.
• The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can be changed by recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes per key.
• All numeric key values are stored with the high byte first to allow better index compression.
• Index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM normally will use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.
• When records are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.
• Internal handling of one AUTO_INCREMENT column per table. MyISAM automatically updates this column for INSERT/UPDATE. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted as they are with ISAM. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of deleted values does occur.) The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamchk.
• If a table doesn't have free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. (These are known as concurrent inserts.) A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again.
• You can put the data and index files on different directories to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE.
• As of MySQL 4.1, each character column can have a different character set.
• There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the --myisam-recover option, MyISAM tables are automatically checked (and optionally repaired) when opened if the table wasn't closed properly.
• myisamchk marks tables as checked if you run it with the --update-state option. myisamchk --fast checks only those tables that don't have this mark.
• myisamchk --analyze stores statistics for key parts, not only for whole keys as in ISAM.
• myisampack can pack BLOB and VARCHAR columns; pack_isam cannot.

MyISAM also supports the following features, which MySQL will be able to use in the near future:

• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in two bytes.
• Tables with VARCHAR may have fixed or dynamic record lengths.
• VARCHAR and CHAR columns may be up to 64KB.
• A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. Hence, you can't search on a UNIQUE computed index, however.

Happy Exploring!