<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-queries">
      </a>
      10.5.6 Optimizing InnoDB Queries
     </h3>
    </div>
   </div>
  </div>
  <p>
   To tune queries for
   <code class="literal">
    InnoDB
   </code>
   tables, create an
        appropriate set of indexes on each table. See
   <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
    Section 10.3.1, “How MySQL Uses Indexes”
   </a>
   for details. Follow these
        guidelines for
   <code class="literal">
    InnoDB
   </code>
   indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Because each
      <code class="literal">
       InnoDB
      </code>
      table has a
      <a class="link" href="glossary.html#glos_primary_key" title="primary key">
       primary key
      </a>
      (whether
            you request one or not), specify a set of primary key
            columns for each table, columns that are used in the most
            important and time-critical queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      Do not specify too many or too long columns in the primary
            key, because these column values are duplicated in each
            secondary index. When an index contains unnecessary data,
            the I/O to read this data and memory to cache it reduce the
            performance and scalability of the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      Do not create a separate
      <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
       secondary index
      </a>
      for each column, because each query can only make use of one
            index. Indexes on rarely tested columns or columns with only
            a few different values might not be helpful for any queries.
            If you have many queries for the same table, testing
            different combinations of columns, try to create a small
            number of
      <a class="link" href="glossary.html#glos_concatenated_index" title="concatenated index">
       concatenated
            indexes
      </a>
      rather than a large number of single-column
            indexes. If an index contains all the columns needed for the
            result set (known as a
      <a class="link" href="glossary.html#glos_covering_index" title="covering index">
       covering index
      </a>
      ),
            the query might be able to avoid reading the table data at
            all.
     </p>
    </li>
    <li class="listitem">
     <p>
      If an indexed column cannot contain any
      <code class="literal">
       NULL
      </code>
      values, declare it as
      <code class="literal">
       NOT
            NULL
      </code>
      when you create the table. The optimizer can
            better determine which index is most effective to use for a
            query, when it knows whether each column contains
      <code class="literal">
       NULL
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can optimize single-query transactions for
      <code class="literal">
       InnoDB
      </code>
      tables, using the technique in
      <a class="xref" href="innodb-performance-ro-txn.html" title="10.5.3 Optimizing InnoDB Read-Only Transactions">
       Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
