为什么这个带有子查询的SQL查询非常慢? [英] Why is this SQL query with subquery very slow?

问题描述

我有这个查询:

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

内部查询运行得非常快(不到0.1秒)，以获取两个ID，一个ID表示状态1，一个ID表示状态2，然后它根据主键进行选择，因此会对其进行索引.说明查询说，它仅使用where搜索135k行，我一生都无法弄清楚为什么这么慢.

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.

推荐答案

select b.*
from transaction_batch b
inner join (
    select max(id) as id
    from transaction_batch
    where status_id in (1, 2)
    group by status_id
) bm on b.id = bm.id

这篇关于为什么这个带有子查询的SQL查询非常慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！