<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="optimize-blob">
      </a>
      10.4.2.3 Optimizing for BLOB Types
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226748016">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When storing a large blob containing textual data,
              consider compressing it first. Do not use this technique
              when the entire table is compressed by
      <code class="literal">
       InnoDB
      </code>
      or
      <code class="literal">
       MyISAM
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For a table with several columns, to reduce memory
              requirements for queries that do not use the BLOB column,
              consider splitting the BLOB column into a separate table
              and referencing it with a join query when needed.
     </p>
    </li>
    <li class="listitem">
     <p>
      Since the performance requirements to retrieve and display
              a BLOB value might be very different from other data
              types, you could put the BLOB-specific table on a
              different storage device or even a separate database
              instance. For example, to retrieve a BLOB might require a
              large sequential disk read that is better suited to a
              traditional hard drive than to an
      <a class="link" href="glossary.html#glos_ssd" title="SSD">
       SSD device
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      See
      <a class="xref" href="optimize-character.html" title="10.4.2.2 Optimizing for Character and String Types">
       Section 10.4.2.2, “Optimizing for Character and String Types”
      </a>
      for reasons why a
              binary
      <code class="literal">
       VARCHAR
      </code>
      column is sometimes
              preferable to an equivalent BLOB column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Rather than testing for equality against a very long text
              string, you can store a hash of the column value in a
              separate column, index that column, and test the hashed
              value in queries. (Use the
      <code class="literal">
       MD5()
      </code>
      or
      <code class="literal">
       CRC32()
      </code>
      function to produce the hash
              value.) Since hash functions can produce duplicate results
              for different inputs, you still include a clause
      <code class="literal">
       AND
       <em class="replaceable">
        <code>
         blob_column
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         long_string_value
        </code>
       </em>
      </code>
      in
              the query to guard against false matches; the performance
              benefit comes from the smaller, easily scanned index for
              the hashed values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
