时间戳

首页 > 解决方案 > CASE查询优化

mysql - CASE查询优化

问题描述

SELECT
  COUNT(CASE WHEN VALUE = 1 THEN 1 END) AS score_1,
  COUNT(CASE WHEN VALUE = 2 THEN 1 END) AS score_2,
  COUNT(CASE WHEN VALUE = 3 THEN 1 END) AS score_3,
  COUNT(CASE WHEN VALUE = 4 THEN 1 END) AS score_4,
  COUNT(CASE WHEN VALUE = 5 THEN 1 END) AS score_5,
  COUNT(CASE WHEN VALUE = 6 THEN 1 END) AS score_6,
  COUNT(CASE WHEN VALUE = 7 THEN 1 END) AS score_7,
  COUNT(CASE WHEN VALUE = 8 THEN 1 END) AS score_8,
  COUNT(CASE WHEN VALUE = 9 THEN 1 END) AS score_9,
  COUNT(CASE WHEN VALUE = 10 THEN 1 END) AS score_10
FROM
  `answers`
WHERE
`created_at` BETWEEN '2017-01-01 00:00:00' AND '2019-11-30 23:59:59'

有没有办法优化这个查询，因为我的数据库中有 400 万条答案记录，而且运行速度很慢？

标签： mysqloptimizationcase

解决方案

You could try add a redundant composite index 

create idx1 on table answers(created_at, value)

using redudance in index the query should be result without accessing to table data just using the index content

推荐阅读