<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="converting-tables-to-innodb">
      </a>
      17.6.1.5 Converting Tables from MyISAM to InnoDB
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045166371344">
  </a>
  <a class="indexterm" name="idm46045166369424">
  </a>
  <p>
   If you have
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   tables that you want
    to convert to
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   for better
    reliability and scalability, review the following guidelines and
    tips before converting.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Partitioned
    <code class="literal">
     MyISAM
    </code>
    tables created in previous
      versions of MySQL are not compatible with MySQL 8.4.
      Such tables must be prepared prior to upgrade, either by removing
      the partitioning, or by converting them to
    <code class="literal">
     InnoDB
    </code>
    . See
    <a class="xref" href="partitioning-limitations-storage-engines.html" title="26.6.2 Partitioning Limitations Relating to Storage Engines">
     Section 26.6.2, “Partitioning Limitations Relating to Storage Engines”
    </a>
    , for
      more information.
   </p>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-memory-usage" title="Adjusting Memory Usage for MyISAM and InnoDB">
       Adjusting Memory Usage for MyISAM and InnoDB
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-transactions" title="Handling Too-Long Or Too-Short Transactions">
       Handling Too-Long Or Too-Short Transactions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-deadlock" title="Handling Deadlocks">
       Handling Deadlocks
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-plan-storage" title="Storage Layout">
       Storage Layout
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-convert" title="Converting an Existing Table">
       Converting an Existing Table
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-clone" title="Cloning the Structure of a Table">
       Cloning the Structure of a Table
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-transfer" title="Transferring Data">
       Transferring Data
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-storage-requirements" title="Storage Requirements">
       Storage Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-primary-key" title="Defining Primary Keys">
       Defining Primary Keys
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-application-performance" title="Application Performance Considerations">
       Application Performance Considerations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="converting-tables-to-innodb.html#innodb-convert-understand-files" title="Understanding Files Associated with InnoDB Tables">
       Understanding Files Associated with InnoDB Tables
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-memory-usage">
       </a>
       Adjusting Memory Usage for MyISAM and InnoDB
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166348416">
   </a>
   <p>
    As you transition away from
    <code class="literal">
     MyISAM
    </code>
    tables,
      lower the value of the
    <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
     <code class="literal">
      key_buffer_size
     </code>
    </a>
    configuration
      option to free memory no longer needed for caching results.
      Increase the value of the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
     <code class="literal">
      innodb_buffer_pool_size
     </code>
    </a>
    configuration option, which performs a similar role of allocating
      cache memory for
    <code class="literal">
     InnoDB
    </code>
    tables. The
    <code class="literal">
     InnoDB
    </code>
    <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
     buffer
      pool
    </a>
    caches both table data and index data, speeding up
      lookups for queries and keeping query results in memory for reuse.
      For guidance regarding buffer pool size configuration, see
    <a class="xref" href="memory-use.html" title="10.12.3.1 How MySQL Uses Memory">
     Section 10.12.3.1, “How MySQL Uses Memory”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-transactions">
       </a>
       Handling Too-Long Or Too-Short Transactions
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166338688">
   </a>
   <p>
    Because
    <code class="literal">
     MyISAM
    </code>
    tables do not support
    <a class="link" href="glossary.html#glos_transaction" title="transaction">
     transactions
    </a>
    , you might
      not have paid much attention to the
    <a class="link" href="server-system-variables.html#sysvar_autocommit">
     <code class="literal">
      autocommit
     </code>
    </a>
    configuration option
      and the
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    and
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      ROLLBACK
     </code>
    </a>
    statements. These keywords are important to allow multiple
      sessions to read and write
    <code class="literal">
     InnoDB
    </code>
    tables
      concurrently, providing substantial scalability benefits in
      write-heavy workloads.
   </p>
   <p>
    While a transaction is open, the system keeps a snapshot of the
      data as seen at the beginning of the transaction, which can cause
      substantial overhead if the system inserts, updates, and deletes
      millions of rows while a stray transaction keeps running. Thus,
      take care to avoid transactions that run for too long:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If you are using a
       <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        <span class="command">
         <strong>
          mysql
         </strong>
        </span>
       </a>
       session for
          interactive experiments, always
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       (to finalize the
          changes) or
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       (to
          undo the changes) when finished. Close down interactive
          sessions rather than leave them open for long periods, to
          avoid keeping transactions open for long periods by accident.
      </p>
     </li>
     <li class="listitem">
      <p>
       Make sure that any error handlers in your application also
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       incomplete changes or
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       completed changes.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       is
          a relatively expensive operation, because
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       , and
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       operations are written
          to
       <code class="literal">
        InnoDB
       </code>
       tables prior to the
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       , with the expectation
          that most changes are committed successfully and rollbacks are
          rare. When experimenting with large volumes of data, avoid
          making changes to large numbers of rows and then rolling back
          those changes.
      </p>
     </li>
     <li class="listitem">
      <p>
       When loading large volumes of data with a sequence of
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       statements, periodically
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       the results to avoid
          having transactions that last for hours. In typical load
          operations for data warehousing, if something goes wrong, you
          truncate the table (using
       <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
        <code class="literal">
         TRUNCATE
          TABLE
        </code>
       </a>
       ) and start over from the beginning rather than
          doing a
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    The preceding tips save memory and disk space that can be wasted
      during too-long transactions. When transactions are shorter than
      they should be, the problem is excessive I/O. With each
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    , MySQL makes sure each
      change is safely recorded to disk, which involves some I/O.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For most operations on
       <code class="literal">
        InnoDB
       </code>
       tables, you
          should use the setting
       <a class="link" href="server-system-variables.html#sysvar_autocommit">
        <code class="literal">
         autocommit=0
        </code>
       </a>
       . From an
          efficiency perspective, this avoids unnecessary I/O when you
          issue large numbers of consecutive
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       , or
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       statements. From a
          safety perspective, this allows you to issue a
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       statement to recover lost or garbled data if you make a
          mistake on the
       <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        <span class="command">
         <strong>
          mysql
         </strong>
        </span>
       </a>
       command line, or in an
          exception handler in your application.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="server-system-variables.html#sysvar_autocommit">
        <code class="literal">
         autocommit=1
        </code>
       </a>
       is suitable for
       <code class="literal">
        InnoDB
       </code>
       tables when running a sequence of
          queries for generating reports or analyzing statistics. In
          this situation, there is no I/O penalty related to
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       or
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         ROLLBACK
        </code>
       </a>
       ,
          and
       <code class="literal">
        InnoDB
       </code>
       can
       <a class="link" href="innodb-performance-ro-txn.html" title="10.5.3 Optimizing InnoDB Read-Only Transactions">
        automatically
          optimize the read-only workload
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If you make a series of related changes, finalize all the
          changes at once with a single
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       at the end. For example,
          if you insert related pieces of information into several
          tables, do a single
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       after making all the changes. Or if you run many consecutive
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       statements, do a single
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       after all the data is
          loaded; if you are doing millions of
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       statements, perhaps
          split up the huge transaction by issuing a
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       every ten thousand or
          hundred thousand records, so the transaction does not grow too
          large.
      </p>
     </li>
     <li class="listitem">
      <p>
       Remember that even a
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statement opens a transaction, so after running some report or
          debugging queries in an interactive
       <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        <span class="command">
         <strong>
          mysql
         </strong>
        </span>
       </a>
       session, either issue a
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT
        </code>
       </a>
       or close the
       <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        <span class="command">
         <strong>
          mysql
         </strong>
        </span>
       </a>
       session.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For related information, see
    <a class="xref" href="innodb-autocommit-commit-rollback.html" title="17.7.2.2 autocommit, Commit, and Rollback">
     Section 17.7.2.2, “autocommit, Commit, and Rollback”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-deadlock">
       </a>
       Handling Deadlocks
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166271456">
   </a>
   <p>
    You might see warning messages referring to
    <span class="quote">
     “
     <span class="quote">
      deadlocks
     </span>
     ”
    </span>
    in the MySQL error log, or the output of
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE INNODB
      STATUS
     </code>
    </a>
    . A
    <a class="link" href="glossary.html#glos_deadlock" title="deadlock">
     deadlock
    </a>
    is not a serious issue for
    <code class="literal">
     InnoDB
    </code>
    tables, and
      often does not require any corrective action. When two
      transactions start modifying multiple tables, accessing the tables
      in a different order, they can reach a state where each
      transaction is waiting for the other and neither can proceed. When
    <a class="link" href="glossary.html#glos_deadlock_detection" title="deadlock detection">
     deadlock detection
    </a>
    is enabled (the default), MySQL immediately detects this condition
      and cancels (
    <a class="link" href="glossary.html#glos_rollback" title="rollback">
     rolls back
    </a>
    ) the
    <span class="quote">
     “
     <span class="quote">
      smaller
     </span>
     ”
    </span>
    transaction, allowing the other to proceed.
      If deadlock detection is disabled using the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_deadlock_detect">
     <code class="literal">
      innodb_deadlock_detect
     </code>
    </a>
    configuration option,
    <code class="literal">
     InnoDB
    </code>
    relies on the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_lock_wait_timeout">
     <code class="literal">
      innodb_lock_wait_timeout
     </code>
    </a>
    setting
      to roll back transactions in case of a deadlock.
   </p>
   <p>
    Either way, your applications need error-handling logic to restart
      a transaction that is forcibly cancelled due to a deadlock. When
      you re-issue the same SQL statements as before, the original
      timing issue no longer applies. Either the other transaction has
      already finished and yours can proceed, or the other transaction
      is still in progress and your transaction waits until it finishes.
   </p>
   <p>
    If deadlock warnings occur constantly, you might review the
      application code to reorder the SQL operations in a consistent
      way, or to shorten the transactions. You can test with the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_print_all_deadlocks">
     <code class="literal">
      innodb_print_all_deadlocks
     </code>
    </a>
    option
      enabled to see all deadlock warnings in the MySQL error log,
      rather than only the last warning in the
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE INNODB
      STATUS
     </code>
    </a>
    output.
   </p>
   <p>
    For more information, see
    <a class="xref" href="innodb-deadlocks.html" title="17.7.5 Deadlocks in InnoDB">
     Section 17.7.5, “Deadlocks in InnoDB”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-plan-storage">
       </a>
       Storage Layout
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166253536">
   </a>
   <p>
    To get the best performance from
    <code class="literal">
     InnoDB
    </code>
    tables,
      you can adjust a number of parameters related to storage layout.
   </p>
   <p>
    When you convert
    <code class="literal">
     MyISAM
    </code>
    tables that are large,
      frequently accessed, and hold vital data, investigate and consider
      the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    and
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    variables, and
      the
    <a class="link" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
     <code class="literal">
      ROW_FORMAT
     </code>
     and
     <code class="literal">
      KEY_BLOCK_SIZE
     </code>
     clauses
    </a>
    of the
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement.
   </p>
   <p>
    During your initial experiments, the most important setting is
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    . When this
      setting is enabled, which is the default, new
    <code class="literal">
     InnoDB
    </code>
    tables are implicitly created in
    <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
     file-per-table
    </a>
    tablespaces. In contrast with the
    <code class="literal">
     InnoDB
    </code>
    system
      tablespace, file-per-table tablespaces allow disk space to be
      reclaimed by the operating system when a table is truncated or
      dropped. File-per-table tablespaces also support
    <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
     DYNAMIC
    </a>
    and
    <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
     COMPRESSED
    </a>
    row
      formats and associated features such as table compression,
      efficient off-page storage for long variable-length columns, and
      large index prefixes. For more information, see
    <a class="xref" href="innodb-file-per-table-tablespaces.html" title="17.6.3.2 File-Per-Table Tablespaces">
     Section 17.6.3.2, “File-Per-Table Tablespaces”
    </a>
    .
   </p>
   <p>
    You can also store
    <code class="literal">
     InnoDB
    </code>
    tables in a shared
      general tablespace, which support multiple tables and all row
      formats. For more information, see
    <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
     Section 17.6.3.3, “General Tablespaces”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-convert">
       </a>
       Converting an Existing Table
      </h5>
     </div>
    </div>
   </div>
   <p>
    To convert a non-
    <code class="literal">
     InnoDB
    </code>
    table to use
    <code class="literal">
     InnoDB
    </code>
    use
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
      TABLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55990344"><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> <span class="token keyword"><em class="replaceable">table_name</em></span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-clone">
       </a>
       Cloning the Structure of a Table
      </h5>
     </div>
    </div>
   </div>
   <p>
    You might make an
    <code class="literal">
     InnoDB
    </code>
    table that is a clone
      of a MyISAM table, rather than using
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
      TABLE
     </code>
    </a>
    to perform conversion, to test the old and new
      table side-by-side before switching.
   </p>
   <p>
    Create an empty
    <code class="literal">
     InnoDB
    </code>
    table with identical
      column and index definitions. Use
    <code class="literal">
     SHOW CREATE TABLE
     <em class="replaceable">
      <code>
       table_name
      </code>
     </em>
     \G
    </code>
    to see the full
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement to use.
      Change the
    <code class="literal">
     ENGINE
    </code>
    clause to
    <code class="literal">
     ENGINE=INNODB
    </code>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-transfer">
       </a>
       Transferring Data
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166219296">
   </a>
   <p>
    To transfer a large volume of data into an empty
    <code class="literal">
     InnoDB
    </code>
    table created as shown in the previous
      section, insert the rows with
    <code class="literal">
     INSERT INTO
     <em class="replaceable">
      <code>
       innodb_table
      </code>
     </em>
     SELECT * FROM
     <em class="replaceable">
      <code>
       myisam_table
      </code>
     </em>
     ORDER BY
     <em class="replaceable">
      <code>
       primary_key_columns
      </code>
     </em>
    </code>
    .
   </p>
   <p>
    You can also create the indexes for the
    <code class="literal">
     InnoDB
    </code>
    table after inserting the data. Historically, creating new
      secondary indexes was a slow operation for
    <code class="literal">
     InnoDB
    </code>
    , but now you can create the indexes
      after the data is loaded with relatively little overhead from the
      index creation step.
   </p>
   <p>
    If you have
    <code class="literal">
     UNIQUE
    </code>
    constraints on secondary
      keys, you can speed up a table import by turning off the
      uniqueness checks temporarily during the import operation:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22763723"><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">import</span> operation <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 disk I/O because
    <code class="literal">
     InnoDB
    </code>
    can use its
    <a class="link" href="glossary.html#glos_change_buffer" title="change buffer">
     change buffer
    </a>
    to write
      secondary index records as a batch. Be certain that the data
      contains no duplicate keys.
    <a class="link" href="server-system-variables.html#sysvar_unique_checks">
     <code class="literal">
      unique_checks
     </code>
    </a>
    permits but does
      not require storage engines to ignore duplicate keys.
   </p>
   <p>
    For better control over the insertion process, you can insert big
      tables in pieces:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4052160"><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> newtable <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> oldtable
   <span class="token keyword">WHERE</span> yourkey <span class="token operator">&gt;</span> <em class="replaceable">something</em> <span class="token operator">AND</span> yourkey <span class="token operator">&lt;=</span> <em class="replaceable">somethingelse</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    After all records are inserted, you can rename the tables.
   </p>
   <p>
    During the conversion of big tables, increase the size of the
    <code class="literal">
     InnoDB
    </code>
    buffer pool to reduce disk I/O.
      Typically, the recommended buffer pool size is 50 to 75 percent of
      system memory. You can also increase the size of
    <code class="literal">
     InnoDB
    </code>
    log files.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-storage-requirements">
       </a>
       Storage Requirements
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166200192">
   </a>
   <p>
    If you intend to make several temporary copies of your data in
    <code class="literal">
     InnoDB
    </code>
    tables during the conversion process, it
      is recommended that you create the tables in file-per-table
      tablespaces so that you can reclaim the disk space when you drop
      the tables. When the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    configuration option is enabled (the default), newly created
    <code class="literal">
     InnoDB
    </code>
    tables are implicitly created in
      file-per-table tablespaces.
   </p>
   <p>
    Whether you convert the
    <code class="literal">
     MyISAM
    </code>
    table directly
      or create a cloned
    <code class="literal">
     InnoDB
    </code>
    table, make sure that
      you have sufficient disk space to hold both the old and new tables
      during the process.
    <span class="bold">
     <strong>
      <code class="literal">
       InnoDB
      </code>
      tables require
      more disk space than
      <code class="literal">
       MyISAM
      </code>
      tables.
     </strong>
    </span>
    If an
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    operation runs
      out of space, it starts a rollback, and that can take hours if it
      is disk-bound. For inserts,
    <code class="literal">
     InnoDB
    </code>
    uses the
      insert buffer to merge secondary index records to indexes in
      batches. That saves a lot of disk I/O. For rollback, no such
      mechanism is used, and the rollback can take 30 times longer than
      the insertion.
   </p>
   <p>
    In the case of a runaway rollback, if you do not have valuable
      data in your database, it may be advisable to kill the database
      process rather than wait for millions of disk I/O operations to
      complete. For the complete procedure, see
    <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
     Section 17.20.3, “Forcing InnoDB Recovery”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-primary-key">
       </a>
       Defining Primary Keys
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166186256">
   </a>
   <p>
    The
    <code class="literal">
     PRIMARY KEY
    </code>
    clause is a critical factor
      affecting the performance of MySQL queries and the space usage for
      tables and indexes. The primary key uniquely identifies a row in a
      table. Every row in the table should have a primary key value, and
      no two rows can have the same primary key value.
   </p>
   <p>
    These are guidelines for the primary key, followed by more
      detailed explanations.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Declare a
       <code class="literal">
        PRIMARY KEY
       </code>
       for each table.
          Typically, it is the most important column that you refer to
          in
       <code class="literal">
        WHERE
       </code>
       clauses when looking up a single
          row.
      </p>
     </li>
     <li class="listitem">
      <p>
       Declare the
       <code class="literal">
        PRIMARY KEY
       </code>
       clause in the
          original
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       statement, rather than adding it later through an
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       Choose the column and its data type carefully. Prefer numeric
          columns over character or string ones.
      </p>
     </li>
     <li class="listitem">
      <p>
       Consider using an auto-increment column if there is not
          another stable, unique, non-null, numeric column to use.
      </p>
     </li>
     <li class="listitem">
      <p>
       An auto-increment column is also a good choice if there is any
          doubt whether the value of the primary key column could ever
          change. Changing the value of a primary key column is an
          expensive operation, possibly involving rearranging data
          within the table and within each secondary index.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Consider adding a
    <a class="link" href="glossary.html#glos_primary_key" title="primary key">
     primary
      key
    </a>
    to any table that does not already have one. Use the
      smallest practical numeric type based on the maximum projected
      size of the table. This can make each row slightly more compact,
      which can yield substantial space savings for large tables. The
      space savings are multiplied if the table has any
    <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
     secondary indexes
    </a>
    ,
      because the primary key value is repeated in each secondary index
      entry. In addition to reducing data size on disk, a small primary
      key also lets more data fit into the
    <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
     buffer pool
    </a>
    , speeding up
      all kinds of operations and improving concurrency.
   </p>
   <p>
    If the table already has a primary key on some longer column, such
      as a
    <code class="literal">
     VARCHAR
    </code>
    , consider adding a new unsigned
    <code class="literal">
     AUTO_INCREMENT
    </code>
    column and switching the primary
      key to that, even if that column is not referenced in queries.
      This design change can produce substantial space savings in the
      secondary indexes. You can designate the former primary key
      columns as
    <code class="literal">
     UNIQUE NOT NULL
    </code>
    to enforce the same
      constraints as the
    <code class="literal">
     PRIMARY KEY
    </code>
    clause, that is,
      to prevent duplicate or null values across all those columns.
   </p>
   <p>
    If you spread related information across multiple tables,
      typically each table uses the same column for its primary key. For
      example, a personnel database might have several tables, each with
      a primary key of employee number. A sales database might have some
      tables with a primary key of customer number, and other tables
      with a primary key of order number. Because lookups using the
      primary key are very fast, you can construct efficient join
      queries for such tables.
   </p>
   <p>
    If you leave the
    <code class="literal">
     PRIMARY KEY
    </code>
    clause out
      entirely, MySQL creates an invisible one for you. It is a 6-byte
      value that might be longer than you need, thus wasting space.
      Because it is hidden, you cannot refer to it in queries.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-application-performance">
       </a>
       Application Performance Considerations
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166162352">
   </a>
   <p>
    The reliability and scalability features of
    <code class="literal">
     InnoDB
    </code>
    require more disk storage than
      equivalent
    <code class="literal">
     MyISAM
    </code>
    tables. You might change the
      column and index definitions slightly, for better space
      utilization, reduced I/O and memory consumption when processing
      result sets, and better query optimization plans making efficient
      use of index lookups.
   </p>
   <p>
    If you set up a numeric ID column for the primary key, use that
      value to cross-reference with related values in any other tables,
      particularly for
    <a class="link" href="glossary.html#glos_join" title="join">
     join
    </a>
    queries.
      For example, rather than accepting a country name as input and
      doing queries searching for the same name, do one lookup to
      determine the country ID, then do other queries (or a single join
      query) to look up relevant information across several tables.
      Rather than storing a customer or catalog item number as a string
      of digits, potentially using up several bytes, convert it to a
      numeric ID for storing and querying. A 4-byte unsigned
    <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
     <code class="literal">
      INT
     </code>
    </a>
    column can index over 4 billion
      items (with the US meaning of billion: 1000 million). For the
      ranges of the different integer types, see
    <a class="xref" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
     Section 13.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT,
        MEDIUMINT, BIGINT”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-convert-understand-files">
       </a>
       Understanding Files Associated with InnoDB Tables
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166153440">
   </a>
   <p>
    <code class="literal">
     InnoDB
    </code>
    files require more care and planning
      than
    <code class="literal">
     MyISAM
    </code>
    files do.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       You must not delete the
       <a class="link" href="glossary.html#glos_ibdata_file" title="ibdata file">
        ibdata files
       </a>
       that
          represent the
       <code class="literal">
        InnoDB
       </code>
       <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
        system
          tablespace
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Methods of moving or copying
       <code class="literal">
        InnoDB
       </code>
       tables
          to a different server are described in
       <a class="xref" href="innodb-migration.html" title="17.6.1.4 Moving or Copying InnoDB Tables">
        Section 17.6.1.4, “Moving or Copying InnoDB Tables”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
