[+/-]
MyISAM
is the default storage engine. It is based
on the older ISAM
code but has many useful
extensions. (Note that MySQL 5.4 does
not support ISAM
.)
Table 13.1. MyISAM
Storage Engine
Features
Storage limits | 256TB | Transactions | No | Locking granularity | Table |
MVCC | No | Geospatial datatype support | Yes | Geospatial indexing support | Yes |
B-tree indexes | Yes | Hash indexes | No | Full-text search indexes | Yes |
Clustered indexes | No | Data caches | No | Index caches | Yes |
Compressed data | Yes[a] | Encrypted data[b] | Yes | Cluster database support | No |
Replication support[c] | Yes | Foreign key support | No | Backup / point-in-time recovery[d] | Yes |
Query cache support | Yes | Update statistics for data dictionary | Yes | ||
[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [b] Implemented in the server (via encryption functions), rather than in the storage engine. [c] Implemented in the server, rather than in the storage product [d] Implemented in the server, rather than in the storage product |
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 format. 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
table option:
CREATE TABLE t (i INT) ENGINE = MYISAM;
Normally, it is unnecessary to use ENGINE
to
specify the 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 allow 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 MyISAM
table. If you build MySQL
with the --with-big-tables
option, the row limitation is increased to
(232)2
(1.844E+19) rows. See Section 2.10.2, “Typical configure Options”.
Binary distributions for Unix and Linux are built with this
option.
The maximum number of indexes per MyISAM
table is 64. This can be changed by recompiling. You can
configure the build by invoking configure
with the
--with-max-indexes=
option, where N
N
is the maximum number
of indexes to permit per MyISAM
table.
N
must be less than or equal to 128.
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_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 is supported. MyISAM
automatically updates this column for
INSERT
and
UPDATE
operations. This makes
AUTO_INCREMENT
columns faster (at least 10%).
Values at the top of the sequence are not reused after being
deleted. (When an 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
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.
MyISAM
supports concurrent inserts: If a
table has no 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. 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.3.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 DATA DIRECTORY
and INDEX
DIRECTORY
table options to CREATE
TABLE
. See Section 12.1.14, “CREATE TABLE
Syntax”.
NULL
values are allowed in indexed columns.
This takes 0–1 bytes per key.
Each character column can have a different character set. See Section 9.1, “Character Set Support”.
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 when
opened, and are repaired 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 portions of keys, as well as for entire keys.
myisampack can pack
BLOB
and
VARCHAR
columns.
MyISAM
also supports the following features:
Additional Resources
A forum dedicated to the MyISAM
storage
engine is available at http://forums.mysql.com/list.php?21.
User Comments
Add your own comment.