<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="optimize-character">
      </a>
      10.4.2.2 Optimizing for Character and String Types
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226765424">
  </a>
  <p>
   For character and string columns, follow these guidelines:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use binary collation order for fast comparison and sort
              operations, when you do not need language-specific
              collation features. You can use the
      <a class="link" href="cast-functions.html#operator_binary">
       <code class="literal">
        BINARY
       </code>
      </a>
      operator to use binary
              collation within a particular query.
     </p>
    </li>
    <li class="listitem">
     <p>
      When comparing values from different columns, declare
              those columns with the same character set and collation
              wherever possible, to avoid string conversions while
              running the query.
     </p>
    </li>
    <li class="listitem">
     <p>
      For column values less than 8KB in size, use binary
      <code class="literal">
       VARCHAR
      </code>
      instead of
      <code class="literal">
       BLOB
      </code>
      . The
      <code class="literal">
       GROUP BY
      </code>
      and
      <code class="literal">
       ORDER BY
      </code>
      clauses can generate
              temporary tables, and these temporary tables can use the
      <code class="literal">
       MEMORY
      </code>
      storage engine if the original
              table does not contain any
      <code class="literal">
       BLOB
      </code>
      columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a table contains string columns such as name and
              address, but many queries do not retrieve those columns,
              consider splitting the string columns into a separate
              table and using join queries with a foreign key when
              necessary. When MySQL retrieves any value from a row, it
              reads a data block containing all the columns of that row
              (and possibly other adjacent rows). Keeping each row
              small, with only the most frequently used columns, allows
              more rows to fit in each data block. Such compact tables
              reduce disk I/O and memory usage for common queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      When you use a randomly generated value as a primary key
              in an
      <code class="literal">
       InnoDB
      </code>
      table, prefix it with an
              ascending value such as the current date and time if
              possible. When consecutive primary values are physically
              stored near each other,
      <code class="literal">
       InnoDB
      </code>
      can
              insert and retrieve them faster.
     </p>
    </li>
    <li class="listitem">
     <p>
      See
      <a class="xref" href="optimize-numeric.html" title="10.4.2.1 Optimizing for Numeric Data">
       Section 10.4.2.1, “Optimizing for Numeric Data”
      </a>
      for reasons why a
              numeric column is usually preferable to an equivalent
              string column.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
