MyISAM is the default storage engine as of
MySQL 3.23. It is based on the
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
.frm file stores the table format. The data
file has an
extension. The index file has an
To specify explicitly that you want a
table, indicate that with an
CREATE TABLE t (i INT) ENGINE = MYISAM;
The older term
TYPE is supported as a synonym
ENGINE for backward compatibility, but
ENGINE is the preferred term from MySQL 4.0.18
TYPE is deprecated.
option is unnecessary;
MyISAM is the default
storage engine unless the default has been changed. To ensure that
MyISAM is used in situations where the default
might have been changed, specify the storage engine 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.2, “myisamchk — MyISAM Table-Maintenance Utility”, and
Section 4.6.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
The following characteristics of the
storage engine are improvements over the older
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.
The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This can be changed by changing the source and 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 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.
Index files are usually much smaller with
MyISAM than with
This means that
MyISAM normally uses less
system resources than
ISAM, but needs more
CPU time when inserting data into a compressed index.
When rows are inserted in sorted order (as when you are using
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
column per table is supported.
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
AUTO_INCREMENT column 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_INCREMENT value can be reset with
ALTER TABLE or
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.
MyISAM supports concurrent inserts: If a
table has no free blocks in the middle of the data file, you
INSERT new 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 7.6.3, “Concurrent Inserts”.
You can put the data file and index file in different
directories on different physical devices to get more speed
DATA DIRECTORY and
DIRECTORY table options to
CREATE TABLE. See
Section 12.1.5, “CREATE TABLE Syntax”.
NULL values are permitted in indexed
columns. This takes 0-1 bytes per key.
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 tables are automatically checked
when opened, and are repaired if the table wasn't closed
myisamchk --analyze stores statistics for
portions of keys, not only for whole keys as in
MyISAM also supports the following features,
which MySQL will be able to use in the near future:
A forum dedicated to the
engine is available at