title: MySQL Hash Indexes for Optimization
body: <p>So maybe this is noob, but I'm messing with a couple tables.</p>
  <p>I have TABLE A roughly 45,000 records</p>
  <p>I have TABLE B roughly 1.5 million records</p>
  <p>I have a query:</p>
  <pre><code>update
      schema1.tablea a
      inner join (
          SELECT DISTINCT
              ID, Lookup,
              IDpart1, IDpart2
          FROM
              schema1.tableb
          WHERE
              IDpart1 is not NULL
          AND
              Lookup is not NULL
          ORDER BY
              ID,Lookup
      ) b Using(ID,Lookup)
  set 
      a.Elg_IDpart1 = b.IDpart1, 
      a.Elg_IDpart2 = b.IDpart2
  where
      a.ID is NOT NULL
  AND
      a.Elg_IDpart1 is NULL
  </code></pre>
  <p>So I am forcing the index on ID, Lookup. Each table does have a index on those columns as well but because of the sub-query I forced it.</p>
  <p>It is taking FOR-EVER to run, and it really should take, i'd imagine under 5 minutes...</p>
  <p>My questions are in regards to the indexes, not the query.</p>
  <p>I know that you can't use hash index in ordered index.</p>
  <p>I currently have indexes on both ID, Lookup sperately, and as one index, and it is a B-Tree index. Based on my <code>WHERE</code> Clause, does a hash index fit for as an optimization technique??</p>
  <p>Can I have a single hash index, and the rest of the indexes b B-tree index?</p>
  <p>This is not a primary key field.</p>
  <p>I would post my explain but i changed the name on these tables. Basically it is using the index only for ID...instead of using the ID, Lookup, I would like to force it to use both, or at least turn it into a different kind of index and see if that helps?</p>
  <p>Now I know MySQL is smart enough to determine which index is most appropriate, so is that what it's doing?
  The Lookup field maps the first and second part of the ID...</p>
  <p>Any help or insight on this is appreciated.</p>
  <hr />
  <h3> UPDATE</h3>
  <p>An <code>EXPLAIN</code> on the <code>UPDATE</code> after I took out sub-query.</p>
  <pre>
  +----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+
  | id | select_type | table | type |        possible_keys        |     key      | key_len |        ref        | rows  |    Extra    |
  +----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+
  |  1 | SIMPLE      | m     | ALL  | Lookup_Idx,ID_Idx,ID_Lookup |              |         |                   | 44023 | Using where |
  |  1 | SIMPLE      | c     | ref  | ID_LookupIdx                | ID_LookupIdx |       5 | schema1.tableb.ID |     4 | Using where |
  +----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+
  </pre>
  <p><code>tablea</code> relevant indexes:</p>
  <ul>
  <li><code>ID_LookupIdx (ID, Lookup)</code></li>
  </ul>
  <p><code>tableb</code> relevant indexes:</p>
  <ul>
  <li><code>ID (ID)</code></li>
  <li><code>Lookup_Idx (Lookup)</code></li>
  <li><code>ID_Lookup_Idx (ID, Lookup)</code></li>
  </ul>
  <p>All of the indexes are normal B-trees.</p>