<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-file-defragmenting">
      </a>
      17.11.4 Defragmenting a Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045160615744">
  </a>
  <a class="indexterm" name="idm46045160613840">
  </a>
  <p>
   Random insertions into or deletions from a secondary index can
      cause the index to become fragmented. Fragmentation means that the
      physical ordering of the index pages on the disk is not close to
      the index ordering of the records on the pages, or that there are
      many unused pages in the 64-page blocks that were allocated to the
      index.
  </p>
  <p>
   One symptom of fragmentation is that a table takes more space than
      it
   <span class="quote">
    “
    <span class="quote">
     should
    </span>
    ”
   </span>
   take. How much that is exactly, is
      difficult to determine. All
   <code class="literal">
    InnoDB
   </code>
   data and
      indexes are stored in
   <a class="link" href="glossary.html#glos_b_tree" title="B-tree">
    B-trees
   </a>
   ,
      and their
   <a class="link" href="glossary.html#glos_fill_factor" title="fill factor">
    fill factor
   </a>
   may
      vary from 50% to 100%. Another symptom of fragmentation is that a
      table scan such as this takes more time than it
   <span class="quote">
    “
    <span class="quote">
     should
    </span>
    ”
   </span>
   take:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74918548"><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">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> <em class="replaceable">non_indexed_column</em> <span class="token operator">&lt;&gt;</span> <span class="token number">12345</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The preceding query requires MySQL to perform a full table scan,
      the slowest type of query for a large table.
  </p>
  <p>
   To speed up index scans, you can periodically perform a
   <span class="quote">
    “
    <span class="quote">
     null
    </span>
    ”
   </span>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operation, which causes MySQL to rebuild the table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48580377"><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">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB</code></pre>
  </div>
  <p>
   You can also use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     FORCE
    </code>
   </a>
   to perform a
   <span class="quote">
    “
    <span class="quote">
     null
    </span>
    ”
   </span>
   alter operation that rebuilds the table.
  </p>
  <p>
   Both
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     ENGINE=INNODB
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     FORCE
    </code>
   </a>
   use
   <a class="link" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
    online DDL
   </a>
   . For more
      information, see
   <a class="xref" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
    Section 17.12, “InnoDB and Online DDL”
   </a>
   .
  </p>
  <p>
   Another way to perform a defragmentation operation is to use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to dump the table to a text file,
      drop the table, and reload it from the dump file.
  </p>
  <p>
   If the insertions into an index are always ascending and records
      are deleted only from the end, the
   <code class="literal">
    InnoDB
   </code>
   filespace management algorithm guarantees that fragmentation in
      the index does not occur.
  </p>
 </div>
 <br/>
</div>
