<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-best-practices">
      </a>
      17.1.2 Best Practices for InnoDB Tables
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section describes best practices when using
   <code class="literal">
    InnoDB
   </code>
   tables.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Specify a primary key for every table using the most
          frequently queried column or columns, or an auto-increment
          value if there is no obvious primary key.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use joins wherever data is pulled from multiple tables based
          on identical ID values from those tables. For fast join
          performance, define foreign keys on the join columns, and
          declare those columns with the same data type in each table.
          Adding foreign keys ensures that referenced columns are
          indexed, which can improve performance. Foreign keys also
          propagate deletes and updates to all affected tables, and
          prevent insertion of data in a child table if the
          corresponding IDs are not present in the parent table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Turn off autocommit. Committing hundreds of times a second
          puts a cap on performance (limited by the write speed of your
          storage device).
     </p>
    </li>
    <li class="listitem">
     <p>
      Group sets of related DML operations into transactions by
          bracketing them with
      <code class="literal">
       START TRANSACTION
      </code>
      and
      <code class="literal">
       COMMIT
      </code>
      statements. While you don't want to
          commit too often, you also don't want to issue huge batches of
      <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 that run for
          hours without committing.
     </p>
    </li>
    <li class="listitem">
     <p>
      Do not use
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      statements.
      <code class="literal">
       InnoDB
      </code>
      can handle multiple
          sessions all reading and writing to the same table at once
          without sacrificing reliability or high performance. To get
          exclusive write access to a set of rows, use the
      <a class="link" href="innodb-locking-reads.html" title="17.7.2.4 Locking Reads">
       <code class="literal">
        SELECT
          ... FOR UPDATE
       </code>
      </a>
      syntax to lock just the rows you
          intend to update.
     </p>
    </li>
    <li class="listitem">
     <p>
      Enable the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      variable or use general tablespaces to put the data and
          indexes for tables into separate files instead of the system
          tablespace. The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      variable is enabled by default.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluate whether your data and access patterns benefit from
          the
      <code class="literal">
       InnoDB
      </code>
      table or page compression
          features. You can compress
      <code class="literal">
       InnoDB
      </code>
      tables
          without sacrificing read/write capability.
     </p>
    </li>
    <li class="listitem">
     <p>
      Run the server with the
      <a class="link" href="server-system-variables.html#sysvar_sql_mode">
       <code class="literal">
        --sql_mode=NO_ENGINE_SUBSTITUTION
       </code>
      </a>
      option to prevent tables from being created with storage
          engines that you do not want to use.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
