title: Why is this SQL query with subquery very slow?
body: <p>I have this query:</p>
  
  <pre><code>select *
  from transaction_batch
  where id IN
  (
      select MAX(id) as id
      from transaction_batch
      where status_id IN (1,2)
      group by status_id
  );
  </code></pre>
  
  <p>The inner query runs very fast (less than 0.1 seconds) to get two ID's, one for status 1, one for status 2, then it selects based on primary key so it is indexed. The explain query says that it's searching 135k rows using where only, and I cannot for the life of me figure out why this is so slow.</p>