<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="innodb-limits">
      </a>
      17.21 InnoDB Limits
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045148828400">
  </a>
  <a class="indexterm" name="idm46045148826912">
  </a>
  <p>
   This section describes limits for
   <code class="literal">
    InnoDB
   </code>
   tables, indexes, tablespaces, and other aspects of the
   <code class="literal">
    InnoDB
   </code>
   storage engine.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A table can contain a maximum of 1017 columns. Virtual
          generated columns are included in this limit.
     </p>
    </li>
    <li class="listitem">
     <p>
      A table can contain a maximum of 64
      <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
       secondary indexes
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The index key prefix length limit is 3072 bytes for
      <code class="literal">
       InnoDB
      </code>
      tables that use
      <code class="literal">
       <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
        DYNAMIC
       </a>
      </code>
      or
      <code class="literal">
       <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
        COMPRESSED
       </a>
      </code>
      row format.
     </p>
     <p>
      The index key prefix length limit is 767 bytes for
      <code class="literal">
       InnoDB
      </code>
      tables that use the
      <code class="literal">
       <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
        REDUNDANT
       </a>
      </code>
      or
      <code class="literal">
       <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
        COMPACT
       </a>
      </code>
      row format. For example, you might hit this limit with a
      <a class="link" href="glossary.html#glos_column_prefix" title="column prefix">
       column prefix
      </a>
      index
          of more than 191 characters on a
      <code class="literal">
       TEXT
      </code>
      or
      <code class="literal">
       VARCHAR
      </code>
      column, assuming a
      <code class="literal">
       utf8mb4
      </code>
      character set and the maximum of 4
          bytes for each character.
     </p>
     <p>
      Attempting to use an index key prefix length that exceeds the
          limit returns an error.
     </p>
     <p>
      If you reduce the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_page_size" title="page size">
       page size
      </a>
      to 8KB or 4KB
          by specifying the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      option when
          creating the MySQL instance, the maximum length of the index
          key is lowered proportionally, based on the limit of 3072
          bytes for a 16KB page size. That is, the maximum index key
          length is 1536 bytes when the page size is 8KB, and 768 bytes
          when the page size is 4KB.
     </p>
     <p>
      The limits that apply to index key prefixes also apply to
          full-column index keys.
     </p>
    </li>
    <li class="listitem">
     <p>
      A maximum of 16 columns is permitted for multicolumn indexes.
          Exceeding the limit returns an error.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15821070"><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 output">ERROR 1070 (42000)<span class="token punctuation">:</span> Too many key parts specified; max 16 parts allowed</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The maximum row size, excluding any variable-length columns
          that are stored off-page, is slightly less than half of a page
          for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the
          maximum row size for the default
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      of 16KB is
          about 8000 bytes. However, for an
      <code class="literal">
       InnoDB
      </code>
      page size of 64KB, the maximum row size is approximately 16000
          bytes.
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        LONGBLOB
       </code>
      </a>
      and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        LONGTEXT
       </code>
      </a>
      columns must be less than 4GB, and the total row size,
          including
      <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, must be less than
          4GB.
     </p>
     <p>
      If a row is less than half a page long, all of it is stored
          locally within the page. If it exceeds half a page,
          variable-length columns are chosen for external off-page
          storage until the row fits within half a page, as described in
      <a class="xref" href="innodb-file-space.html" title="17.11.2 File Space Management">
       Section 17.11.2, “File Space Management”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Although
      <code class="literal">
       InnoDB
      </code>
      supports row sizes larger
          than 65,535 bytes internally, MySQL itself imposes a row-size
          limit of 65,535 for the combined size of all columns. See
      <a class="xref" href="column-count-limit.html" title="10.4.7 Limits on Table Column Count and Row Size">
       Section 10.4.7, “Limits on Table Column Count and Row Size”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The maximum table or tablespace size is impacted by the
          server's file system, which can impose a maximum file
          size that is smaller than the internal 64 TiB size limit
          defined by
      <code class="literal">
       InnoDB
      </code>
      . For example, the
      <span class="emphasis">
       <em>
        ext4
       </em>
      </span>
      file system on Linux has a maximum
          file size of 16 TiB, so the maximum table or tablespace size
          becomes 16 TiB instead of 64 TiB. Another example is the
      <span class="emphasis">
       <em>
        FAT32
       </em>
      </span>
      file system, which has a maximum
          file size of 4 GB.
     </p>
     <p>
      If you require a larger system tablespace, configure it using
          several smaller data files rather than one large data file, or
          distribute table data across file-per-table and general
          tablespace data files.
     </p>
    </li>
    <li class="listitem">
     <p>
      The combined maximum size for
      <code class="literal">
       InnoDB
      </code>
      log
          files is 512GB.
     </p>
    </li>
    <li class="listitem">
     <p>
      The minimum tablespace size is slightly larger than 10MB. The
          maximum tablespace size depends on the
      <code class="literal">
       InnoDB
      </code>
      page size.
     </p>
     <div class="table">
      <a name="idm46045148784528">
      </a>
      <p class="title">
       <b>
        Table 17.26 InnoDB Maximum Tablespace Size
       </b>
      </p>
      <div class="table-contents">
       <table summary="The maximum tablespace size for each InnoDB page size.">
        <colgroup>
         <col style="width: 40%"/>
         <col style="width: 60%"/>
        </colgroup>
        <thead>
         <tr>
          <th>
           InnoDB Page Size
          </th>
          <th>
           Maximum Tablespace Size
          </th>
         </tr>
        </thead>
        <tbody>
         <tr>
          <td>
           4KB
          </td>
          <td>
           16TB
          </td>
         </tr>
         <tr>
          <td>
           8KB
          </td>
          <td>
           32TB
          </td>
         </tr>
         <tr>
          <td>
           16KB
          </td>
          <td>
           64TB
          </td>
         </tr>
         <tr>
          <td>
           32KB
          </td>
          <td>
           128TB
          </td>
         </tr>
         <tr>
          <td>
           64KB
          </td>
          <td>
           256TB
          </td>
         </tr>
        </tbody>
       </table>
      </div>
     </div>
     <br class="table-break"/>
     <p>
      The maximum tablespace size is also the maximum size for a
          table.
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <code class="literal">
       InnoDB
      </code>
      instance supports up to 2^32
          (4294967296) tablespaces, with a small number of those
          tablespaces reserved for undo and temporary tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      Shared tablespaces support up to 2^32 (4294967296) tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      The path of a tablespace file, including the file name, cannot
          exceed the
      <code class="literal">
       MAX_PATH
      </code>
      limit on Windows. Prior
          to Windows 10, the
      <code class="literal">
       MAX_PATH
      </code>
      limit is 260
          characters. As of Windows 10, version 1607,
      <code class="literal">
       MAX_PATH
      </code>
      limitations are removed from
          common Win32 file and directory functions, but you must enable
          the new behavior.
     </p>
    </li>
    <li class="listitem">
     <p>
      For limits associated with concurrent read-write transactions,
          see
      <a class="xref" href="innodb-undo-logs.html" title="17.6.6 Undo Logs">
       Section 17.6.6, “Undo Logs”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
