mysql - myquery在mysql中花费太长时间

我的transactionDetail表有3亿个数据。要为我的查询获取数据需要很长时间。
下面是我的问题

 select
 MerchantId as y_m,BoothId ,
 TransactionTypeId ,
 count(Amount) ,
 sum(Amount)
 from TransactionDetail
 where TransactionDate>='2014-02-26'
 and TransactionDate<'2019-02-27'
 and not (BoothId like 'TEST%')
 and MerchantId in (select MerchantId from MerchantGroup where  MerchantClassId='MD-SAFAL')
 group by MerchantId, BoothId, TransactionTypeId
 order by y_m asc, BoothId asc, TransactionTypeId asc;

TransactionDetail表具有以下键和索引
主键（TransactionId），
索引如下
键idxTransactionDetail003（MerchantId），
键idxTransactionDetail004（TransactionDate）
MerchantGroup表在MerchantId列上有索引

最佳答案

可以在TransactionDetail列（MerchantId、TransactionDate、BoothId）上添加复合索引
您可以使用内部连接而不是IN子句
用不喜欢代替不喜欢

    select d.MerchantId as y_m
    ,d.BoothId
    ,d.TransactionTypeId
    , count(d.Amount) , sum(d.Amount)
    from TransactionDetail d
    INNER JOIN (
        select MerchantId
        from MerchantGroup
        where MerchantClassId='MD-SAFAL'
    ) t t.MerchantId = d.MerchantId
    where d.TransactionDate>='2014-02-26'
        and d.TransactionDate<'2019-02-27'
    and d.BoothId not like 'TEST%'
    group by d.MerchantId, d.BoothId, d.TransactionTypeId
    order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;