(mysql.info.gz) MyISAM storage engine
Info Catalog
(mysql.info.gz) Storage engines
(mysql.info.gz) Storage engines
(mysql.info.gz) MERGE storage engine
14.1 The `MyISAM' Storage Engine
================================
`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.
Crash recovery. You can compress `MyISAM' tables with
`myisampack' to take up much less space. `myisampack'
myisampack.
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 file and index file on different directories
to get more speed with the `DATA DIRECTORY' and `INDEX DIRECTORY'
table options to `CREATE TABLE'. `CREATE TABLE' 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 when opened and 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 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 length.
* `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. (You
can't search on a `UNIQUE' computed index, however.)
Menu
* MyISAM start `MyISAM' Startup Options
* Key space Space Needed for Keys
* MyISAM table formats `MyISAM' Table Storage Formats
* MyISAM table problems `MyISAM' Table Problems
Info Catalog
(mysql.info.gz) Storage engines
(mysql.info.gz) Storage engines
(mysql.info.gz) MERGE storage engine
automatically generated byinfo2html