Why is this SQL query with subquery very slow?

Question

I have this query:

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
);

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.

Answer 1

The inner query is run seperatly for every row of your table over and over again.

As there is no reference to the outer query in the inner query, I suggest you split those two queries and just insert the results of the inner query in the WHERE clause.