title: Will MySQL Optimize Queries With Two WHERE-X-IN-Y Clauses On The Same Column?
body: <p>I'm curious if MySQL will internally optimize a query that has two or more <code>WHERE X IN Y</code> conditions on the same column.</p>
  
  <p>As a quick background of why I ask: The application in question uses a PHP query-builder and conditions are added to the query in two different spots. Permission-based restrictions are added to a query automatically first in one area of the code. In a separate area, that restricted <code>SELECT</code> query object is retrieved (without need for knowledge of what was previously done) and additional user-chosen filter restrictions are added in addition to the permission-based restrictions potentially resulting in two <code>WHERE</code> conditions on one field.</p>
  
  <p>For a small contrived example, assume there is a simple table like:</p>
  
  <pre><code>CREATE TABLE users (
      userId INT(12) NOT NULL auto_increment,
      managerUserId INT(12),
      name VARCHAR(16)
      primary KEY (userId)
  );
  </code></pre>
  
  <p>(Obviously there would be appropriate indexes added as well)</p>
  
  <p>So, when building up the <code>SELECT</code> query, we'd start with <code>SELECT * FROM users</code> initially.</p>
  
  <p>Say, for example, the user making the query has access to the following managerUserId values: 11,22,33,44,55,66,77.<br />
  The query builder will automatically impose a <code>WHERE managerUserId IN (11,22,33,44,55,66,77)</code> addition to 
  the bare query. Now we have the query <code>SELECT * FROM users WHERE managerUserId IN (11,22,33,44,55,66,77)</code> represented by our query object.</p>
  
  <p>However, the user specified they only wanted information about managerUserId values: 33,55,77 at this time.<br />
  Since this is a user requested filter and not an access-related restriction, the query is modified at a different point in the code to add <code>AND managerUserId IN (33,55,77)</code> to the <code>WHERE</code> clause.</p>
  
  <p>At this point we now have the final query:</p>
  
  <pre><code>SELECT *
  FROM   users
  WHERE  managerUserId IN (11,22,33,44,55,66,77)
  AND    managerUserId IN (33,55,77)
  </code></pre>
  
  <p>Now, it seems obvious we only need to actually query on the union of (11,22,33,44,55,66,77) and (33,55,77).</p>
  
  <p><strong>My question is: Does MySQL know how to internally optimize the above query to simply be the equivalent of</strong></p>
  
  <pre><code>SELECT *
  FROM   users
  WHERE  managerUserId IN (33,55,77)
  </code></pre>
  
  <p>In my case, the first access-based restriction could potentially contain up to 100-200 values while the second one, in most cases, would probably only contain a dozen or fewer values. Seems to me an easy enough thing MySQL could detect an optimize but I'm not sure how to confirm that is the case or what the potential performance issues with including both clauses might be.</p>