MySQL-优化case语句

Question

我有一个包含许多 case 语句的查询

    SELECT 
    case when (std_no like '070%' or std_no like '071%') then 'A'
    when (std_no like '077%' or std_no like '076%') then 'B'
    when std_no like '075%' then 'C'
    when std_no like '072%' then 'D'
    when std_no like '078%' then 'E'
    when (std_no not like '07%' and std_no not like '00%'
       and std_no not like '0100%' 
       and substring(std_no,4,1) in('2','3')) then 'F'
    when (std_no not like '07%' and std_no not like '00%' 
       and std_no not like '0100%' 
       and substring(std_no,4,1)='5') then 'G' 
    when (std_no not like '07%' and std_no not like '00%'
       and std_no not like '0100%'
      and substring(std_no,4,1)='7') then 'H'
    end as GroupName,city_id,
    count(*) as  PostCount
    from imei_tb
    where  reg_date>='2017-01-06'
    and  reg_date<='2017-01-10'
    and length(std_no)='10'
    GROUP BY GroupName,city_id
    ORDER BY city_id ASC

有什么办法可以加快这个查询的速度吗？提前致谢

Answer 1

INDEX(reg_date) 可能是唯一有用的索引。

您被迫执行两种排序 - 一种用于 GROUP BY，另一种用于 ORDER BY。

一旦我看到SHOW CREATE TABLE imei_tb，我想我将能够使用子查询为您提供改进的查询。

新提示

可以通过使GROUP BY 和ORDER BY 匹配来消除一种排序。这应该提供相同的结果，但速度更快:

GROUP BY city_id, GroupName
ORDER BY city_id, GroupName