title: Slow MySQL query after adding multiple OR conditions to indexed column
body: <p>I have a query running on MySQL (v5.5 -- I know it's old but it's what I have to work with for now). The table <code>A</code> below has ~16 million rows and <code>B</code> has ~700,000. The query looks something like this:</p>
  <pre><code>SELECT A.id, A.x, A.y, A.z, B.foo FROM A STRAIGHT_JOIN B ON A.id = B.id
      where A.x = 53 ORDER BY A.y desc LIMIT 0, 30;  
  </code></pre>
  <p>There's an index setup on <code>A.id</code> as well as on <code>B.id</code>.<br />
  There's also an index setup on <code>(A.x, A.y)</code> (this key/index is called <code>DocsByType</code>).</p>
  <p>This query has worked great so far, it's performance has always been sub-second or thereabouts.  Recently though, I have a need to occasionally check against an additional possible value for <code>A.x</code> in the where clause. The following query is now performing very poorly, on average taking ~15 secs to complete:</p>
  <pre><code>SELECT A.id, A.x, A.y, A.z, B.foo FROM A STRAIGHT_JOIN B ON A.id = B.id
      where (A.x = 18 or A.x = 53) ORDER BY A.y desc LIMIT 0, 30;  
  </code></pre>
  <p>The <code>explain</code> for the fast query with only one comparison looks like this:</p>
  <pre><code>+----+-------------+-------+------+-----------------------------------------------------+------------+---------+----------------+---------+-------------+
  | id | select_type | table | type | possible_keys                                       | key        | key_len | ref            | rows    | Extra       |
  +----+-------------+-------+------+-----------------------------------------------------+------------+---------+----------------+---------+-------------+
  |  1 | SIMPLE      | A     | ref  | Documents1,Documents2,Documents3,DocsByType,KEY_AID | DocsByType | 4       | const          | 1870603 | Using where |
  |  1 | SIMPLE      | B     | ref  | KEY_BID                                             | KEY_BID    | 4       | mydb.B.id      |       1 |             |
  +----+-------------+-------+------+-----------------------------------------------------+------------+---------+----------------+---------+-------------+  
  </code></pre>
  <p>The <code>explain</code> for the multi-comparison query looks like this:</p>
  <pre><code>+----+-------------+-------+-------+-----------------------------------------------------+------------+---------+----------------+---------+-----------------------------+
  | id | select_type | table | type  | possible_keys                                       | key        | key_len | ref            | rows    | Extra                       |
  +----+-------------+-------+-------+-----------------------------------------------------+------------+---------+----------------+---------+-----------------------------+
  |  1 | SIMPLE      | A     | range | Documents1,Documents2,Documents3,DocsByType,KEY_AID | DocsByType | 4       | NULL           | 1878693 | Using where; Using filesort |
  |  1 | SIMPLE      | B     | ref   | KEY_BID                                             | KEY_BID    | 4       | mydb.B.id      |       1 |                             |
  +----+-------------+-------+-------+-----------------------------------------------------+------------+---------+----------------+---------+-----------------------------+ 
  </code></pre>
  <p>I can see that there's a <code>filesort</code> operation that's not in the first query. Also the <code>type</code> is &quot;range&quot; instead of &quot;ref&quot;, and the <code>ref</code> is &quot;NULL&quot; instead of &quot;const&quot;. Removing the order by clause fixes it completely, so that it completes in less than a second, but it's important that the results are sorted.</p>
  <p>Query optimization is not my strong suit. I would have thought that it would have worked exactly the same given that the column is already indexed. Can anyone explain why this behaves the way it does and suggest a way to optimize the query? Please also note that the new query might need to use 3, 4 or even 5 possible values for the where clause (but always against the same column).</p>
  <p>I've also tried running the queries using MySQL 5.8 but the result is the same. My table is using the MyISAM engine.</p>