时间戳

首页 > 解决方案 > MySQL查询永远执行

mysql - MySQL查询永远执行

问题描述

我正在尝试提取一个简单的报告。我已经准备了一个查询，但它需要永远执行。

PFB查询：-

select sc.product_name as product_name,
       (select count(id) from hg_scratch_card
           where product_name=sc.product_name
             and (review_received_date between '2019-12-01'
                                           and '2019-12-21') ) as RR_COUNT,
       (select count(id) from hg_scratch_card
           where product_name=sc.product_name
             and (review_updated_date between '2019-12-01'
                                          and '2019-12-21') ) as RU_COUNT
from hg_scratch_card sc
where sc.product_name !=''
order by RR_COUNT desc
limit 3

请建议我如何优化查询。我对所有列都有索引。

标签： mysqldatabase

解决方案

如果您想要每行 1 行，product_name那么您应该group by product_name使用条件聚合：

select product_name,
  sum(review_received_date between '2019-12-01' and '2019-12-21') as RR_COUNT,
  sum(review_updated_date between '2019-12-01' and '2019-12-21') as RU_COUNT
from hg_scratch_card
where product_name !=''
group by product_name
order by RR_COUNT desc
limit 3

推荐阅读