title: Using an index in MySQL JOIN with OR condition
body: <p>I'm running a query which looks like this</p>
  
  <pre><code>SELECT parent.field, child.field
  FROM parent
  JOIN child ON (child.id = parent.id 
      OR child.id = parent.otherid)
  </code></pre>
  
  <p>This is however really slow (about 100k records, and JOINs to other tables in the real version), but despite having tried indexes on </p>
  
  <pre><code>parent.id (PRIMARY),  
  parent.otherid,  
  child.id (PRIMARY), 
  and a composite index of parent.id and parent.otherid
  </code></pre>
  
  <p>I cannot get MySQL to use any of those indexes when making this join.</p>
  
  <p>I read that MySQL can use only one index per join, but can't find anywhere whether it can use a composite index when a JOIN contains an OR condition.</p>
  
  <p>Does anyone here know if it's possible to make this query reference an index?
  If so, how?</p>
  
  <hr>
  
  <p>MY SOLUTION</p>
  
  <p>(SO won't let me answer my own question below atm)</p>
  
  <p>A bunch of tweaking and came up with a fairly decent solution which retains the ability to JOIN and aggregate other tables.</p>
  
  <pre><code>SELECT parent.field, child.field
  FROM parent
  JOIN (
      SELECT parent.id as parentid, 
      # Prevents the need to union
      IF(NOT ISNULL(parent.otherid) AND parent.otherid &lt;&gt; parent.id, 
         parent.otherid, 
         parent.id) as getdataforid
      FROM parent
      WHERE (condition)
  ) as foundrecords
      ON foundrecords.parentid = parent.id
  JOIN child ON child.id = parent.getdataforid
  </code></pre>
  
  <p>For speed requires a condition inside the subquery to reduce the number of records placed in a temporary table, but I have tons of additional joins on the outer query, some joining to the child and some to the parent (with some aggregates) so this one worked best for me.</p>
  
  <p>In many cases a union will be faster and more effective, but since I'm filtering on parent, but want additional data from child (parent self-references), the union caused extra rows for me which I couldn't consolidate.
  It's possible the same result can be found just by joining parent to itself and aliasing a where condition in the outer query, but this one works quite nicely for me.</p>
  
  <p>Thanks to Jirka for the UNION ALL suggestion, it's what prompted me to get here :)</p>