<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="dynamic-format">
      </a>
      18.2.3.2 Dynamic Table Characteristics
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045148084848">
  </a>
  <a class="indexterm" name="idm46045148083792">
  </a>
  <p>
   Dynamic storage format is used if a
   <code class="literal">
    MyISAM
   </code>
   table contains any variable-length columns
        (
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   ,
   <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
    <code class="literal">
     VARBINARY
    </code>
   </a>
   ,
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   , or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   ), or if the table was
        created with the
   <code class="literal">
    ROW_FORMAT=DYNAMIC
   </code>
   table
        option.
  </p>
  <p>
   Dynamic format is a little more complex than static format
        because each row has a header that indicates how long it is. A
        row can become fragmented (stored in noncontiguous pieces) when
        it is made longer as a result of an update.
  </p>
  <a class="indexterm" name="idm46045148074720">
  </a>
  <p>
   You can use
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   or
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk -r
     </strong>
    </span>
   </a>
   to defragment a table. If you
        have fixed-length columns that you access or change frequently
        in a table that also contains some variable-length columns, it
        might be a good idea to move the variable-length columns to
        other tables just to avoid fragmentation.
  </p>
  <p>
   Dynamic-format tables have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      All string columns are dynamic except those with a length
            less than four.
     </p>
    </li>
    <li class="listitem">
     <p>
      Each row is preceded by a bitmap that indicates which
            columns contain the empty string (for string columns) or
            zero (for numeric columns). This does not include columns
            that contain
      <code class="literal">
       NULL
      </code>
      values. If a string
            column has a length of zero after trailing space removal, or
            a numeric column has a value of zero, it is marked in the
            bitmap and not saved to disk. Nonempty strings are saved as
            a length byte plus the string contents.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NULL
      </code>
      columns require additional space in
            the row to record whether their values are
      <code class="literal">
       NULL
      </code>
      . Each
      <code class="literal">
       NULL
      </code>
      column
            takes one bit extra, rounded up to the nearest byte.
     </p>
    </li>
    <li class="listitem">
     <p>
      Much less disk space usually is required than for
            fixed-length tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      Each row uses only as much space as is required. However, if
            a row becomes larger, it is split into as many pieces as are
            required, resulting in row fragmentation. For example, if
            you update a row with information that extends the row
            length, the row becomes fragmented. In this case, you may
            have to run
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      or
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -r
        </strong>
       </span>
      </a>
      from time to time to improve
            performance. Use
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -ei
        </strong>
       </span>
      </a>
      to obtain
            table statistics.
     </p>
    </li>
    <li class="listitem">
     <p>
      More difficult than static-format tables to reconstruct
            after a crash, because rows may be fragmented into many
            pieces and links (fragments) may be missing.
     </p>
    </li>
    <li class="listitem">
     <p>
      The expected row length for dynamic-sized rows is calculated
            using the following expression:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa18683237"><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-none">3
+ (<em class="replaceable">number of columns</em> + 7) / 8
+ (<em class="replaceable">number of char columns</em>)
+ (<em class="replaceable">packed size of numeric columns</em>)
+ (<em class="replaceable">length of strings</em>)
+ (<em class="replaceable">number of NULL columns</em> + 7) / 8</code></pre>
     </div>
     <p>
      There is a penalty of 6 bytes for each link. A dynamic row
            is linked whenever an update causes an enlargement of the
            row. Each new link is at least 20 bytes, so the next
            enlargement probably goes in the same link. If not, another
            link is created. You can find the number of links using
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -ed
        </strong>
       </span>
      </a>
      . All links may be removed
            with
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      or
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -r
        </strong>
       </span>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
