MyISAM is the default storage engine. It is based
on the older (and no longer available)
storage engine but has many useful extensions.
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
file stores the table format. The data file has an
MYData) extension. The
index file has an
To specify explicitly that you want a
table, indicate that with an
ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MYISAM;
The older term
TYPE is supported as a synonym for
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
Normally, it is unnecessary to use
MyISAM storage engine.
MyISAM is the default engine unless the default
has been changed. To ensure that
MyISAM is used
in situations where the default might have been changed, include the
ENGINE option explicitly.
You can check or repair
MyISAM tables with the
mysqlcheck client or myisamchk
utility. You can also compress
MyISAM tables with
myisampack to take up much less space. See
Section 4.5.3, “mysqlcheck — A Table Maintenance Program”, Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”, and
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
MyISAM tables have the following characteristics:
All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing 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.
All numeric key values are stored with the high byte first to permit better index compression.
Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
There is a limit of 232 (~4.295E+09) rows in a
MyISAMtable. If you build MySQL with the
--with-big-tablesoption, the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.17.3, “MySQL Source-Configuration Options”. Binary distributions for Unix and Linux are built with this option.
The maximum number of indexes per
MyISAMtable is 64. This can be changed by recompiling. Beginning with MySQL 5.0.18, you can configure the build by invoking configure with the
Nis the maximum number of indexes to permit per
Nmust be less than or equal to 128. Before MySQL 5.0.18, you must change the source.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
When rows are inserted in sorted order (as when you are using an
AUTO_INCREMENTcolumn), 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_INCREMENTcolumn per table is supported.
MyISAMautomatically updates this column for
UPDATEoperations. This makes
AUTO_INCREMENTcolumns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an
AUTO_INCREMENTcolumn is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) The
AUTO_INCREMENTvalue can be reset with
ALTER TABLEor myisamchk.
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.
MyISAMsupports concurrent inserts: If a table has no free blocks in the middle of the data file, you can
INSERTnew rows into it at the same time that other threads are reading from the table. 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. See Section 8.11.3, “Concurrent Inserts”.
You can put the data file and index file in different directories on different physical devices to get more speed with the
INDEX DIRECTORYtable options to
CREATE TABLE. See Section 13.1.10, “CREATE TABLE Syntax”.
NULLvalues are permitted in indexed columns. This takes 0 to 1 bytes per key.
Each character column can have a different character set. See Section 10.1, “Character Set Support”.
There is a flag in the
MyISAMindex file that indicates whether the table was closed correctly. If mysqld is started with the
MyISAMtables are automatically checked when opened, and are repaired if the table wasn't closed properly.
myisamchk --analyze stores statistics for portions of keys, as well as for entire keys.
MyISAM also supports the following features:
A forum dedicated to the
MyISAMstorage engine is available at http://forums.mysql.com/list.php?21.