title: MySQL: how to index an "OR" clause
body: <p>I'm executing the following query</p>
  
  <pre><code>SELECT COUNT(*)
  FROM table
  WHERE field1='value' AND (field2 &gt;= 1000 OR field3 &gt;= 2000)
  </code></pre>
  
  <p>There is one index over field1 and another composited over field2&amp;field3.</p>
  
  <p>I see MySQL always selects the field1 index and then makes a join using the other two fields which is quite bad because it needs to join 146.000 rows.</p>
  
  <p>Suggestions on how to improve this? Thanks</p>
  
  <p>(EDIT AFTER TRYING SOLUTION PROPOSED)</p>
  
  <p>Based in the solution proposed I've seen this on Mysql when playing with this.</p>
  
  <pre><code>SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
  UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;
  </code></pre>
  
  <p>is a lot slower than execute:</p>
  
  <pre><code>SELECT COUNT(*)
  FROM table
  WHERE (columnA = value1 AND columnB = value2)
        OR (columnA = value1 AND columnC = value3)
  </code></pre>
  
  <p>Having two composited index:</p>
  
  <pre><code>index1 (columnA,columnB)
  index2 (columnA,columnC)
  </code></pre>
  
  <p>Interesting enough is that asking Mysql to "explain" the query it's taking always index1 on both cases and index2 is not used.</p>
  
  <p>If I change the indexes to:</p>
  
  <pre><code>index1 (columnB,columnA)
  index2 (columnC,columnA)
  </code></pre>
  
  <p>And the query to:</p>
  
  <pre><code>SELECT COUNT(*)
  FROM table
  WHERE (columnB = value2 AND columnA = value1)
        OR (columnC = value3 AND columnA = value1)
  </code></pre>
  
  <p>Then it's the fastest way I've found Mysql works.</p>