<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="myisam-storage-engine">
      </a>
      18.2 The MyISAM Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="myisam-start.html">
       18.2.1 MyISAM Startup Options
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="key-space.html">
       18.2.2 Space Needed for Keys
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="myisam-table-formats.html">
       18.2.3 MyISAM Table Storage Formats
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="myisam-table-problems.html">
       18.2.4 MyISAM Table Problems
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045148481424">
  </a>
  <a class="indexterm" name="idm46045148480384">
  </a>
  <p>
   <code class="literal">
    MyISAM
   </code>
   is based on the older (and no longer
    available)
   <code class="literal">
    ISAM
   </code>
   storage engine but has many
    useful extensions.
  </p>
  <div class="table">
   <a name="idm46045148477008">
   </a>
   <p class="title">
    <b>
     Table 18.2 MyISAM Storage Engine Features
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="Features supported by the MyISAM storage engine.">
     <colgroup>
      <col style="width: 60%"/>
      <col style="width: 40%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Feature
       </th>
       <th>
        Support
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <span class="bold">
         <strong>
          B-tree indexes
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Backup/point-in-time recovery
         </strong>
        </span>
        (Implemented in the server, rather than in the storage engine.)
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Cluster database support
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Clustered indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Compressed data
         </strong>
        </span>
       </td>
       <td>
        Yes (Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.)
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Data caches
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Encrypted data
         </strong>
        </span>
       </td>
       <td>
        Yes (Implemented in the server via encryption functions.)
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Foreign key support
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Full-text search indexes
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Geospatial data type support
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Geospatial indexing support
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Hash indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Index caches
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Locking granularity
         </strong>
        </span>
       </td>
       <td>
        Table
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          MVCC
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Replication support
         </strong>
        </span>
        (Implemented in the server, rather than in the storage engine.)
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Storage limits
         </strong>
        </span>
       </td>
       <td>
        256TB
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          T-tree indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Transactions
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Update statistics for data dictionary
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <div class="table-contents">
    <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
     <thead>
      <tr>
       <th style="width: 442.797px;">
        Feature
       </th>
       <th style="width: 295.203px;">
        Support
       </th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   Each
   <code class="literal">
    MyISAM
   </code>
   table is stored on disk in two files.
    The files have names that begin with the table name and have an
    extension to indicate the file type. The data file has an
   <code class="filename">
    .MYD
   </code>
   (
   <code class="literal">
    MYData
   </code>
   ) extension. The
    index file has an
   <code class="filename">
    .MYI
   </code>
   (
   <code class="literal">
    MYIndex
   </code>
   ) extension. The table definition is
    stored in the MySQL data dictionary.
  </p>
  <p>
   To specify explicitly that you want a
   <code class="literal">
    MyISAM
   </code>
   table, indicate that with an
   <code class="literal">
    ENGINE
   </code>
   table option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49421776"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MYISAM<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In MySQL 8.4, it is normally necessary to use
   <code class="literal">
    ENGINE
   </code>
   to specify the
   <code class="literal">
    MyISAM
   </code>
   storage engine because
   <code class="literal">
    InnoDB
   </code>
   is the default
    engine.
  </p>
  <p>
   You can check or repair
   <code class="literal">
    MyISAM
   </code>
   tables with the
   <a class="link" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
    <span class="command">
     <strong>
      mysqlcheck
     </strong>
    </span>
   </a>
   client or
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   utility. You can also compress
   <code class="literal">
    MyISAM
   </code>
   tables with
   <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
    <span class="command">
     <strong>
      myisampack
     </strong>
    </span>
   </a>
   to take up much less space. See
   <a class="xref" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
    Section 6.5.3, “mysqlcheck — A Table Maintenance Program”
   </a>
   ,
   <a class="xref" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”
   </a>
   , and
   <a class="xref" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
    Section 6.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”
   </a>
   .
  </p>
  <p>
   In MySQL 8.4, the
   <code class="literal">
    MyISAM
   </code>
   storage
    engine provides no partitioning support.
   <span class="emphasis">
    <em>
     Partitioned
     <code class="literal">
      MyISAM
     </code>
     tables created in previous versions of
    MySQL cannot be used in MySQL 8.4
    </em>
   </span>
   . For more
    information, see
   <a class="xref" href="partitioning-limitations-storage-engines.html" title="26.6.2 Partitioning Limitations Relating to Storage Engines">
    Section 26.6.2, “Partitioning Limitations Relating to Storage Engines”
   </a>
   . For help
    with upgrading such tables so that they can be used in MySQL
    8.4, see
   <a class="xref" href="upgrading-from-previous-series.html" title="3.5 Changes in MySQL 8.4">
    Section 3.5, “Changes in MySQL 8.4”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    MyISAM
   </code>
   tables have the following characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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.
     </p>
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      All numeric key values are stored with the high byte first to
        permit better index compression.
     </p>
    </li>
    <li class="listitem">
     <p>
      Large files (up to 63-bit file length) are supported on file
        systems and operating systems that support large files.
     </p>
    </li>
    <li class="listitem">
     <p>
      There is a limit of
        (2
      <sup>
       32
      </sup>
      )
      <sup>
       2
      </sup>
      (1.844E+19) rows in a
      <code class="literal">
       MyISAM
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      The maximum number of indexes per
      <code class="literal">
       MyISAM
      </code>
      table is 64.
     </p>
     <p>
      The maximum number of columns per index is 16.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      When rows are inserted in sorted order (as when you are using an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column), the index tree is
        split so that the high node only contains one key. This improves
        space utilization in the index tree.
     </p>
    </li>
    <li class="listitem">
     <p>
      Internal handling of one
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column per table is supported.
      <code class="literal">
       MyISAM
      </code>
      automatically updates this column for
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      and
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      operations. This makes
      <code class="literal">
       AUTO_INCREMENT
      </code>
      columns faster (at least 10%).
        Values at the top of the sequence are not reused after being
        deleted. (When an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      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
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value can be reset with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      or
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
        </strong>
       </span>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MyISAM
      </code>
      supports concurrent inserts: If a
        table has no free blocks in the middle of the data file, you can
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      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
      <a class="xref" href="concurrent-inserts.html" title="10.11.3 Concurrent Inserts">
       Section 10.11.3, “Concurrent Inserts”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can put the data file and index file in different
        directories on different physical devices to get more speed with
        the
      <code class="literal">
       DATA DIRECTORY
      </code>
      and
      <code class="literal">
       INDEX
        DIRECTORY
      </code>
      table options to
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
        TABLE
       </code>
      </a>
      . See
      <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       Section 15.1.20, “CREATE TABLE Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns can be indexed.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NULL
      </code>
      values are permitted in indexed columns.
        This takes 0 to 1 bytes per key.
     </p>
    </li>
    <li class="listitem">
     <p>
      Each character column can have a different character set. See
      <a class="xref" href="charset.html" title="Chapter 12 Character Sets, Collations, Unicode">
       Chapter 12,
       <i>
        Character Sets, Collations, Unicode
       </i>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There is a flag in the
      <code class="literal">
       MyISAM
      </code>
      index file that
        indicates whether the table was closed correctly. If
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      is started with the
      <a class="link" href="server-system-variables.html#sysvar_myisam_recover_options">
       <code class="literal">
        myisam_recover_options
       </code>
      </a>
      system
        variable set,
      <code class="literal">
       MyISAM
      </code>
      tables are automatically
        checked when opened, and are repaired if the table wasn't closed
        properly.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
        </strong>
       </span>
      </a>
      marks tables as checked if you run
        it with the
      <a class="link" href="myisamchk-check-options.html#option_myisamchk_update-state">
       <code class="option">
        --update-state
       </code>
      </a>
      option.
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk --fast
        </strong>
       </span>
      </a>
      checks only those
        tables that don't have this mark.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk --analyze
        </strong>
       </span>
      </a>
      stores statistics for
        portions of keys, as well as for entire keys.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
       <span class="command">
        <strong>
         myisampack
        </strong>
       </span>
      </a>
      can pack
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      and
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      columns.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <code class="literal">
    MyISAM
   </code>
   also supports the following features:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Support for a true
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      type;
        a
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      column starts with a
        length stored in one or two bytes.
     </p>
    </li>
    <li class="listitem">
     <p>
      Tables with
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      columns may
        have fixed or dynamic row length.
     </p>
    </li>
    <li class="listitem">
     <p>
      The sum of the lengths of the
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      and
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      columns in a table may be up
        to 64KB.
     </p>
    </li>
    <li class="listitem">
     <p>
      Arbitrary length
      <code class="literal">
       UNIQUE
      </code>
      constraints.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045148335360">
   </a>
   Additional Resources
  </h3>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A forum dedicated to the
      <code class="literal">
       MyISAM
      </code>
      storage
        engine is available at
      <a class="ulink" href="https://forums.mysql.com/list.php?21" target="_blank">
       https://forums.mysql.com/list.php?21
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
