(mysql.info) myisam-storage-engine

 
 14.1 The `MyISAM' Storage Engine
 ================================

 
* myisam-start                 `MyISAM' Startup Options
* key-space                    Space Needed for Keys
* myisam-table-formats         `MyISAM' Table Storage Formats
* myisam-table-problems        `MyISAM' Table Problems
 
 `MyISAM' is the default storage engine. It is based on the older `ISAM'
 code but has many useful extensions. (Note that MySQL 5.0 does _not_
 support `ISAM'.)
 
 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;
 
 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 unnecesary 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  mysqlcheck, 
 crash-recovery, and  myisampack.
 
 `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
      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 `MyISAM' table is 64. This can
      be changed by recompiling. Beginning with MySQL 5.0.18, you can
      configure the build by invoking `configure' with the
      -with-max-indexes=N option, where N is the maximum number of
      indexes to permit per `MyISAM' table. N must be less thann 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_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.
 
    * 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. (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. See  concurrent-inserts.
 
    * 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'. See  create-table.
 
    * `BLOB' and `TEXT' columns can be indexed.
 
    * `NULL' values are allowed in indexed columns.  This takes 0-1
      bytes per key.
 
    * Each character column can have a different character set. See
       charset.
 
    * 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:
 
    * Support for a true `VARCHAR' type; a `VARCHAR' column starts with
      a length stored in one or two bytes.
 
    * Tables with `VARCHAR' columns may have fixed or dynamic row length.
 
    * The sum of the lengths of the `VARCHAR' and `CHAR' columns in a
      table may be up to 64KB.
 
    * A hashed computed index can be used for `UNIQUE'. This allows you
      to have `UNIQUE' on any combination of columns in a table.
      (However, you cannot search on a `UNIQUE' computed index.)
 
 *Additional resources*
 
    * A forum dedicated to the `MyISAM' storage engine is available at
      `http://forums.mysql.com/list.php?21'.

Info Catalog

(mysql.info) storage-engines

(mysql.info) innodb

automatically generated byinfo2html

(mysql.info) myisam-storage-engine

Menu