title: MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?
body: <p>I've got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this:</p>
  
  <pre><code>SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT(*) &gt; 1
  </code></pre>
  
  <p>This way, I will get all rows with relevant_field occuring more than once. This query takes milliseconds to execute.</p>
  
  <p>Now, I wanted to inspect each of the duplicates, so I thought I could SELECT each row in some_table with a relevant_field in the above query, so I did like this:</p>
  
  <pre><code>SELECT *
  FROM some_table 
  WHERE relevant_field IN
  (
      SELECT relevant_field
      FROM some_table
      GROUP BY relevant_field
      HAVING COUNT(*) &gt; 1
  )
  </code></pre>
  
  <p>This turns out to be extreeeemely slow for some reason (it takes minutes). What exactly is going on here to make it that slow? relevant_field is indexed.</p>
  
  <p>Eventually I tried creating a view "temp_view" from the first query <code>(SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) &gt; 1)</code>, and then making my second query like this instead:</p>
  
  <pre><code>SELECT *
  FROM some_table
  WHERE relevant_field IN
  (
      SELECT relevant_field
      FROM temp_view
  )
  </code></pre>
  
  <p>And that works just fine. MySQL does this in some milliseconds.</p>
  
  <p>Any SQL experts here who can explain what's going on?</p>