title: Practical limit on "where in" clause
body: <p>I have a fairly simple table with approx a million rows. </p>
  
  <pre><code>id  |  my_col  |  other1  |  other 2 | ...
  </code></pre>
  
  <p>There are about 15k distinct my_col values in this table and I have an index on my_col. I have a set of 7k my_col values that I need to remove from this table.</p>
  
  <p>What is the more efficient thing to do in SQL (I'm currently working with MySQL, but may port to MS SQL in future).</p>
  
  <p>Is it a) In my java app code, itterate through all the my_col values and call sql delete on each one.</p>
  
  <pre><code>for (String my_colValue : listMyCol) {
     [delete from my_table where my_col = my_colValue]
  }
  </code></pre>
  
  <p>or b) Build up a single SQL [large] statement containing all these values using the "where in" clause ?</p>
  
  <pre><code>delete from my_table where my_col in ('aaa', 'aab', 'aac', ...)
  </code></pre>
  
  <p>I'd guess it is b), but I'm not sure if specifying about 7k values in this "where in" clause becomes inefficient.</p>
  
  <p>For what its worth, my app server and database server are both hosted in Amazon, but on separate tiers. </p>