<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-bulk-data-loading">
      </a>
      10.5.5 Bulk Data Loading for InnoDB Tables
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226128160">
  </a>
  <p>
   These performance tips supplement the general guidelines for
        fast inserts in
   <a class="xref" href="insert-optimization.html" title="10.2.5.1 Optimizing INSERT Statements">
    Section 10.2.5.1, “Optimizing INSERT Statements”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When importing data into
      <code class="literal">
       InnoDB
      </code>
      , turn off
            autocommit mode, because it performs a log flush to disk for
            every insert. To disable autocommit during your import
            operation, surround it with
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        SET
            autocommit
       </code>
      </a>
      and
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69948751"><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">SET</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SQL</span> <span class="token keyword">import</span> statements <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></em><span class="token punctuation"></span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      option
      <a class="link" href="mysqldump.html#option_mysqldump_opt">
       <code class="option">
        --opt
       </code>
      </a>
      creates dump files
            that are fast to import into an
      <code class="literal">
       InnoDB
      </code>
      table, even without wrapping them with the
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        SET
            autocommit
       </code>
      </a>
      and
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you have
      <code class="literal">
       UNIQUE
      </code>
      constraints on
            secondary keys, you can speed up table imports by
            temporarily turning off the uniqueness checks during the
            import session:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31027233"><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">SET</span> unique_checks<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SQL</span> <span class="token keyword">import</span> statements <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></em><span class="token punctuation"></span>
<span class="token keyword">SET</span> unique_checks<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For big tables, this saves a lot of disk I/O because
      <code class="literal">
       InnoDB
      </code>
      can use its change buffer to write
            secondary index records in a batch. Be certain that the data
            contains no duplicate keys.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you have
      <code class="literal">
       FOREIGN KEY
      </code>
      constraints in
            your tables, you can speed up table imports by turning off
            the foreign key checks for the duration of the import
            session:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55907017"><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">SET</span> foreign_key_checks<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SQL</span> <span class="token keyword">import</span> statements <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></em><span class="token punctuation"></span>
<span class="token keyword">SET</span> foreign_key_checks<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For big tables, this can save a lot of disk I/O.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the multiple-row
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      syntax to reduce communication overhead between the client
            and the server if you need to insert many rows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43964097"><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">INSERT</span> <span class="token keyword">INTO</span> yourtable <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This tip is valid for inserts into any table, not just
      <code class="literal">
       InnoDB
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      When doing bulk inserts into tables with auto-increment
            columns, set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_autoinc_lock_mode">
       <code class="literal">
        innodb_autoinc_lock_mode
       </code>
      </a>
      to
            2 (interleaved) instead of 1 (consecutive). See
      <a class="xref" href="innodb-auto-increment-handling.html" title="17.6.1.6 AUTO_INCREMENT Handling in InnoDB">
       Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”
      </a>
      for
            details.
     </p>
    </li>
    <li class="listitem">
     <p>
      When performing bulk inserts, it is faster to insert rows in
      <code class="literal">
       PRIMARY KEY
      </code>
      order.
      <code class="literal">
       InnoDB
      </code>
      tables use a
      <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
       clustered index
      </a>
      ,
            which makes it relatively fast to use data in the order of
            the
      <code class="literal">
       PRIMARY KEY
      </code>
      . Performing bulk inserts
            in
      <code class="literal">
       PRIMARY KEY
      </code>
      order is particularly
            important for tables that do not fit entirely within the
            buffer pool.
     </p>
    </li>
    <li class="listitem">
     <p>
      For optimal performance when loading data into an
      <code class="literal">
       InnoDB
      </code>
      <code class="literal">
       FULLTEXT
      </code>
      index,
            follow this set of steps:
     </p>
     <div class="orderedlist">
      <ol class="orderedlist" type="1">
       <li class="listitem">
        <p>
         Define a column
         <code class="literal">
          FTS_DOC_ID
         </code>
         at table
                creation time, of type
         <code class="literal">
          BIGINT UNSIGNED NOT
                NULL
         </code>
         , with a unique index named
         <code class="literal">
          FTS_DOC_ID_INDEX
         </code>
         . For example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64000468"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
    FTS_DOC_ID <span class="token datatype">BIGINT</span> <span class="token keyword">unsigned</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    title <span class="token datatype">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
    <span class="token datatype">text</span> <span class="token datatype">mediumtext</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>FTS_DOC_ID<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> FTS_DOC_ID_INDEX <span class="token keyword">on</span> t1<span class="token punctuation">(</span>FTS_DOC_ID<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         Load the data into the table.
        </p>
       </li>
       <li class="listitem">
        <p>
         Create the
         <code class="literal">
          FULLTEXT
         </code>
         index after the
                data is loaded.
        </p>
       </li>
      </ol>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       When adding
       <code class="literal">
        FTS_DOC_ID
       </code>
       column at table
              creation time, ensure that the
       <code class="literal">
        FTS_DOC_ID
       </code>
       column is updated when the
       <code class="literal">
        FULLTEXT
       </code>
       indexed column is updated, as
              the
       <code class="literal">
        FTS_DOC_ID
       </code>
       must increase
              monotonically with each
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       or
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       . If you choose not
              to add the
       <code class="literal">
        FTS_DOC_ID
       </code>
       at table creation
              time and have
       <code class="literal">
        InnoDB
       </code>
       manage DOC IDs for
              you,
       <code class="literal">
        InnoDB
       </code>
       adds the
       <code class="literal">
        FTS_DOC_ID
       </code>
       as a hidden column with the
              next
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE
              FULLTEXT INDEX
        </code>
       </a>
       call. This approach, however,
              requires a table rebuild which can impact performance.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      If loading data into a
      <span class="emphasis">
       <em>
        new
       </em>
      </span>
      MySQL
            instance, consider disabling redo logging using
      <a class="link" href="alter-instance.html" title="15.1.5 ALTER INSTANCE Statement">
       <code class="literal">
        ALTER
            INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG
       </code>
      </a>
      syntax.
            Disabling redo logging helps speed up data loading by
            avoiding redo log writes. For more information, see
      <a class="xref" href="innodb-redo-log.html#innodb-disable-redo-logging" title="Disabling Redo Logging">
       Disabling Redo Logging
      </a>
      .
     </p>
     <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Warning
      </div>
      <p>
       This feature is intended only for loading data into a new
              MySQL instance.
       <span class="emphasis">
        <em>
         Do not disable redo logging on a
              production system.
        </em>
       </span>
       It is permitted to shutdown
              and restart the server while redo logging is disabled, but
              an unexpected server stoppage while redo logging is
              disabled can cause data loss and instance corruption.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Use MySQL Shell to import data. MySQL Shell's parallel
            table import utility
      <code class="literal">
       util.importTable()
      </code>
      provides rapid data import to a MySQL relational table for
            large data files. MySQL Shell's dump loading utility
      <code class="literal">
       util.loadDump()
      </code>
      also offers parallel load
            capabilities. See
      <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-shell-utilities.html" target="_top">
       MySQL Shell Utilities
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
