(mysql.info.gz) MyISAM storage engine

(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.)
(mysql.info.gz) MyISAM storage engine

Menu
