<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="optimize-table">
      </a>
      15.7.3.4 OPTIMIZE TABLE Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045172207008">
  </a>
  <a class="indexterm" name="idm46045172205952">
  </a>
  <a class="indexterm" name="idm46045172204464">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59377275"><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">OPTIMIZE</span> <span class="token punctuation">[</span><span class="token keyword">NO_WRITE_TO_BINLOG</span> <span class="token operator">|</span> <span class="token keyword">LOCAL</span><span class="token punctuation">]</span>
    <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   reorganizes the
        physical storage of table data and associated index data, to
        reduce storage space and improve I/O efficiency when accessing
        the table. The exact changes made to each table depend on the
   <a class="link" href="glossary.html#glos_storage_engine" title="storage engine">
    storage engine
   </a>
   used
        by that table.
  </p>
  <p>
   Use
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   in these
        cases, depending on the type of table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      After doing substantial insert, update, or delete operations
            on an
      <code class="literal">
       InnoDB
      </code>
      table that has its own
      <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
       .ibd file
      </a>
      because it
            was created with the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      option enabled. The table and indexes are reorganized, and
            disk space can be reclaimed for use by the operating system.
     </p>
    </li>
    <li class="listitem">
     <p>
      After doing substantial insert, update, or delete operations
            on columns that are part of a
      <code class="literal">
       FULLTEXT
      </code>
      index in an
      <code class="literal">
       InnoDB
      </code>
      table. Set the
            configuration option
      <a class="link" href="innodb-parameters.html#sysvar_innodb_optimize_fulltext_only">
       <code class="literal">
        innodb_optimize_fulltext_only=1
       </code>
      </a>
      first. To keep the index maintenance period to a reasonable
            time, set the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_num_word_optimize">
       <code class="literal">
        innodb_ft_num_word_optimize
       </code>
      </a>
      option to specify how many words to update in the search
            index, and run a sequence of
      <code class="literal">
       OPTIMIZE
            TABLE
      </code>
      statements until the search index is fully
            updated.
     </p>
    </li>
    <li class="listitem">
     <p>
      After deleting a large part of a
      <code class="literal">
       MyISAM
      </code>
      or
      <code class="literal">
       ARCHIVE
      </code>
      table, or making many changes
            to a
      <code class="literal">
       MyISAM
      </code>
      or
      <code class="literal">
       ARCHIVE
      </code>
      table with variable-length rows (tables that have
      <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>
      columns). Deleted rows
            are maintained in a linked list and subsequent
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      operations reuse old
            row positions. 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>
      to reclaim the unused space and to
            defragment the data file. After extensive changes to a
            table, this statement may also improve performance of
            statements that use the table, sometimes significantly.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This statement requires
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privileges for the
        table.
  </p>
  <p>
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   works for
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   ,
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   , and
   <a class="link" href="archive-storage-engine.html" title="18.5 The ARCHIVE Storage Engine">
    <code class="literal">
     ARCHIVE
    </code>
   </a>
   tables.
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   is also supported
        for dynamic columns of in-memory
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   tables. It does not work for
        fixed-width columns of in-memory tables, nor does it work for
        Disk Data tables. The performance of
   <code class="literal">
    OPTIMIZE
   </code>
   on NDB Cluster tables can be tuned using
   <a class="link" href="mysql-cluster-options-variables.html#option_mysqld_ndb-optimization-delay">
    <code class="option">
     --ndb-optimization-delay
    </code>
   </a>
   , which
        controls the length of time to wait between processing batches
        of rows by
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   . For
        more information, see
   <a class="xref" href="mysql-cluster-limitations-resolved.html" title="25.2.7.11 Previous NDB Cluster Issues Resolved in NDB Cluster 8.4">
    Section 25.2.7.11, “Previous NDB Cluster Issues Resolved in NDB Cluster 8.4”
   </a>
   .
  </p>
  <p>
   For NDB Cluster tables,
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE
        TABLE
    </code>
   </a>
   can be interrupted by (for example) killing the
        SQL thread performing the
   <code class="literal">
    OPTIMIZE
   </code>
   operation.
  </p>
  <p>
   By default,
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   work for tables created using any other
        storage engine and returns a result indicating this lack of
        support. You can make
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE
        TABLE
    </code>
   </a>
   work for other storage engines by starting
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   with the
   <a class="link" href="server-options.html#option_mysqld_skip-new">
    <code class="option">
     --skip-new
    </code>
   </a>
   option. In this case,
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   is just mapped to
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   .
  </p>
  <p>
   This statement does not work with views.
  </p>
  <p>
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   is supported for
        partitioned tables. For information about using this statement
        with partitioned tables and table partitions, see
   <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
    Section 26.3.4, “Maintenance of Partitions”
   </a>
   .
  </p>
  <p>
   By default, the server writes
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE
        TABLE
    </code>
   </a>
   statements to the binary log so that they
        replicate to replicas. To suppress logging, specify the optional
   <code class="literal">
    NO_WRITE_TO_BINLOG
   </code>
   keyword or its alias
   <code class="literal">
    LOCAL
   </code>
   . You must have the
   <a class="link" href="privileges-provided.html#priv_optimize-local-table">
    <code class="literal">
     OPTIMIZE_LOCAL_TABLE
    </code>
   </a>
   privilege to
        use this option.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="optimize-table.html#optimize-table-output" title="OPTIMIZE TABLE Output">
       OPTIMIZE TABLE Output
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimize-table.html#optimize-table-innodb-details" title="InnoDB Details">
       InnoDB Details
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimize-table.html#optimize-table-myisam-details" title="MyISAM Details">
       MyISAM Details
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="optimize-table.html#optimize-table-other-considerations" title="Other Considerations">
       Other Considerations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="optimize-table-output">
       </a>
       OPTIMIZE TABLE Output
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    returns a result
          set with the columns shown in the following table.
   </p>
   <div class="informaltable">
    <table summary="Columns of the OPTIMIZE TABLE result set.">
     <colgroup>
      <col style="width: 15%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Column
       </th>
       <th>
        Value
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <code class="literal">
         Table
        </code>
       </td>
       <td>
        The table name
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Op
        </code>
       </td>
       <td>
        Always
        <code class="literal">
         optimize
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Msg_type
        </code>
       </td>
       <td>
        <code class="literal">
         status
        </code>
        ,
        <code class="literal">
         error
        </code>
        ,
        <code class="literal">
         info
        </code>
        ,
        <code class="literal">
         note
        </code>
        , or
        <code class="literal">
         warning
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Msg_text
        </code>
       </td>
       <td>
        An informational message
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    table catches
          and throws any errors that occur while copying table
          statistics from the old file to the newly created file. For
          example. if the user ID of the owner of the
    <code class="filename">
     .MYD
    </code>
    or
    <code class="filename">
     .MYI
    </code>
    file is
          different from the user ID of the
    <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
     <span class="command">
      <strong>
       mysqld
      </strong>
     </span>
    </a>
    process,
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    generates a "cannot change ownership of the file" error unless
    <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
     <span class="command">
      <strong>
       mysqld
      </strong>
     </span>
    </a>
    is started by the
    <code class="literal">
     root
    </code>
    user.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="optimize-table-innodb-details">
       </a>
       InnoDB Details
      </h5>
     </div>
    </div>
   </div>
   <p>
    For
    <code class="literal">
     InnoDB
    </code>
    tables,
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    is mapped to
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE ...
          FORCE
     </code>
    </a>
    , which rebuilds the table to update index
          statistics and free unused space in the clustered index. This
          is displayed in the output of
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE
          TABLE
     </code>
    </a>
    when you run it on an
    <code class="literal">
     InnoDB
    </code>
    table, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98160816"><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 prompt">mysql&gt;</span> <span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> foo<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Table    <span class="token punctuation">|</span> Op       <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text                                                          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> test.foo <span class="token punctuation">|</span> optimize <span class="token punctuation">|</span> note     <span class="token punctuation">|</span> Table does not support optimize, doing recreate <span class="token punctuation">+</span> analyze instead <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test.foo <span class="token punctuation">|</span> optimize <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK                                                                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    uses
    <a class="link" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
     online DDL
    </a>
    for
          regular and partitioned
    <code class="literal">
     InnoDB
    </code>
    tables,
          which reduces downtime for concurrent DML operations. The
          table rebuild triggered by
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE
          TABLE
     </code>
    </a>
    is completed in place. An exclusive table lock
          is only taken briefly during the prepare phase and the commit
          phase of the operation. During the prepare phase, metadata is
          updated and an intermediate table is created. During the
          commit phase, table metadata changes are committed.
   </p>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    rebuilds the
          table using the table copy method under the following
          conditions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       When the
       <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
        <code class="literal">
         old_alter_table
        </code>
       </a>
       system variable is enabled.
      </p>
     </li>
     <li class="listitem">
      <p>
       When the server is started with the
       <a class="link" href="server-options.html#option_mysqld_skip-new">
        <code class="option">
         --skip-new
        </code>
       </a>
       option.
      </p>
     </li>
    </ul>
   </div>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    using
    <a class="link" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
     online DDL
    </a>
    is not
          supported for
    <code class="literal">
     InnoDB
    </code>
    tables that contain
    <code class="literal">
     FULLTEXT
    </code>
    indexes. The table copy method is
          used instead.
   </p>
   <p>
    <code class="literal">
     InnoDB
    </code>
    stores data using a page-allocation
          method and does not suffer from fragmentation in the same way
          that legacy storage engines (such as
    <code class="literal">
     MyISAM
    </code>
    ) do. When considering whether or not
          to run optimize, consider the workload of transactions that
          your server is expected to process:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Some level of fragmentation is expected.
       <code class="literal">
        InnoDB
       </code>
       only fills
       <a class="link" href="glossary.html#glos_page" title="page">
        pages
       </a>
       93% full, to
                leave room for updates without having to split pages.
      </p>
     </li>
     <li class="listitem">
      <p>
       Delete operations might leave gaps that leave pages less
                filled than desired, which could make it worthwhile to
                optimize the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Updates to rows usually rewrite the data within the same
                page, depending on the data type and row format, when
                sufficient space is available. See
       <a class="xref" href="innodb-compression-internals.html" title="17.9.1.5 How Compression Works for InnoDB Tables">
        Section 17.9.1.5, “How Compression Works for InnoDB Tables”
       </a>
       and
       <a class="xref" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
        Section 17.10, “InnoDB Row Formats”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       High-concurrency workloads might leave gaps in indexes
                over time, as
       <code class="literal">
        InnoDB
       </code>
       retains multiple
                versions of the same data due through its
       <a class="link" href="glossary.html#glos_mvcc" title="MVCC">
        MVCC
       </a>
       mechanism. See
       <a class="xref" href="innodb-multi-versioning.html" title="17.3 InnoDB Multi-Versioning">
        Section 17.3, “InnoDB Multi-Versioning”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="optimize-table-myisam-details">
       </a>
       MyISAM Details
      </h5>
     </div>
    </div>
   </div>
   <p>
    For
    <code class="literal">
     MyISAM
    </code>
    tables,
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    works as
          follows:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       If the table has deleted or split rows, repair the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the index pages are not sorted, sort them.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the table's statistics are not up to date (and the
              repair could not be accomplished by sorting the index),
              update them.
      </p>
     </li>
    </ol>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="optimize-table-other-considerations">
       </a>
       Other Considerations
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    is performed
          online for regular and partitioned
    <code class="literal">
     InnoDB
    </code>
    tables. Otherwise, MySQL
    <a class="link" href="glossary.html#glos_table_lock" title="table lock">
     locks
          the table
    </a>
    during the time
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE
          TABLE
     </code>
    </a>
    is running.
   </p>
   <p>
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    does not sort
          R-tree indexes, such as spatial indexes on
    <code class="literal">
     POINT
    </code>
    columns. (Bug #23578)
   </p>
  </div>
 </div>
 <br/>
</div>
