<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-storage-layout">
      </a>
      10.5.1 Optimizing Storage Layout for InnoDB Tables
     </h3>
    </div>
   </div>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Once your data reaches a stable size, or a growing table has
            increased by tens or some hundreds of megabytes, consider
            using the
      <code class="literal">
       OPTIMIZE TABLE
      </code>
      statement to
            reorganize the table and compact any wasted space. The
            reorganized tables require less disk I/O to perform full
            table scans. This is a straightforward technique that can
            improve performance when other techniques such as improving
            index usage or tuning application code are not practical.
     </p>
     <p>
      <code class="literal">
       OPTIMIZE TABLE
      </code>
      copies the data part of
            the table and rebuilds the indexes. The benefits come from
            improved packing of data within indexes, and reduced
            fragmentation within the tablespaces and on disk. The
            benefits vary depending on the data in each table. You may
            find that there are significant gains for some and not for
            others, or that the gains decrease over time until you next
            optimize the table. This operation can be slow if the table
            is large or if the indexes being rebuilt do not fit into the
            buffer pool. The first run after adding a lot of data to a
            table is often much slower than later runs.
     </p>
    </li>
    <li class="listitem">
     <p>
      In
      <code class="literal">
       InnoDB
      </code>
      , having a long
      <code class="literal">
       PRIMARY
            KEY
      </code>
      (either a single column with a lengthy value,
            or several columns that form a long composite value) wastes
            a lot of disk space. The primary key value for a row is
            duplicated in all the secondary index records that point to
            the same row. (See
      <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
       Section 17.6.2.1, “Clustered and Secondary Indexes”
      </a>
      .)
            Create an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column as the
            primary key if your primary key is long, or index a prefix
            of a long
      <code class="literal">
       VARCHAR
      </code>
      column instead of the
            entire column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      data type
            instead of
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      to store
            variable-length strings or for columns with many
      <code class="literal">
       NULL
      </code>
      values. A
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR(
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        )
       </code>
      </a>
      column always takes
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      characters
            to store data, even if the string is shorter or its value is
      <code class="literal">
       NULL
      </code>
      . Smaller tables fit better in the
            buffer pool and reduce disk I/O.
     </p>
     <p>
      When using
      <code class="literal">
       COMPACT
      </code>
      row format (the
            default
      <code class="literal">
       InnoDB
      </code>
      format) and
            variable-length character sets, such as
      <code class="literal">
       utf8mb4
      </code>
      or
      <code class="literal">
       sjis
      </code>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR(
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        )
       </code>
      </a>
      columns occupy a variable amount of space, but still at
            least
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      bytes.
     </p>
    </li>
    <li class="listitem">
     <p>
      For tables that are big, or contain lots of repetitive text
            or numeric data, consider using
      <code class="literal">
       COMPRESSED
      </code>
      row format. Less disk I/O is
            required to bring data into the buffer pool, or to perform
            full table scans. Before making a permanent decision,
            measure the amount of compression you can achieve by using
      <code class="literal">
       COMPRESSED
      </code>
      versus
      <code class="literal">
       COMPACT
      </code>
      row format.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
