title: MySQL "IN" queries terribly slow with subquery but fast with explicit values
body: <p>I have a MySQL query (Ubu 10.04,Innodb, Core i7, 16Gb RAM, SSD drives, MySQL params optimized):</p>
  
  <pre><code>SELECT
  COUNT(DISTINCT subscriberid)
  FROM
  em_link_data
  WHERE
  linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')
  </code></pre>
  
  <p>The table em_link_data has about 7million rows, em_link has a few thousand.
  This query will take about <strong>18 seconds</strong> to complete.  However, if I substitute the results
  of the subquery and do this:</p>
  
  <pre><code>SELECT
  COUNT(DISTINCT subscriberid)
  FROM
  em_link_data
  WHERE
  linkid in (24899,24900,24901,24902);
  </code></pre>
  
  <p>then the query will run in less than 1 millisecond.  The subquery alone runs in less than 1ms, the column linkid is indexed.</p>
  
  <p>If I rewrite the query as a join, also less than 1ms.  Why is a "IN" query so slow with a subquery in it and why so fast with values in it? I can't rewrite the query (bought software) so I was hoping there is some tweak or hint to speedup this query!  Any help is appreciated.</p>