mysql - 按整数值分类和 COUNT() 的 sql 优化

Question

我有这样的 table 。

table ppsscore
string name, int playlevel , int scoretotal, 

• 游戏级别为 0-1
• 总分取0-100

超过 100 万行。

我想按游戏级别和总得分对行进行分类，然后对行进行计数。

但是这需要太多时间。

首先，我对“playlevel”和“scoretotal”进行了索引，但差别不大。

还有其他优化计数的好方法吗？

  hash['level1']['over100'] = Ppscore.where("playlevel = '1' AND scoretotal = '100'").count    
  hash['level1']['over90'] = Ppscore.where("playlevel = '1' AND scoretotal >= '90' AND scoretotal != '100'").count
  hash['level1']['over80'] = Ppscore.where("playlevel = '1' AND scoretotal >= '80' AND scoretotal < '90'").count
  hash['level1']['over70'] = Ppscore.where("playlevel = '1' AND scoretotal >= '70' AND scoretotal < '80'").count
  hash['level1']['over60'] = Ppscore.where("playlevel = '1' AND scoretotal >= '60' AND scoretotal < '70'").count
  hash['level1']['over50'] = Ppscore.where("playlevel = '1' AND scoretotal >= '50' AND scoretotal < '60'").count
  hash['level1']['over40'] = Ppscore.where("playlevel = '1' AND scoretotal >= '40' AND scoretotal < '50'").count
  hash['level1']['over30'] = Ppscore.where("playlevel = '1' AND scoretotal >= '30' AND scoretotal < '40'").count
  hash['level1']['over20'] = Ppscore.where("playlevel = '1' AND scoretotal >= '20' AND scoretotal < '30'").count
  hash['level1']['over10'] = Ppscore.where("playlevel = '1' AND scoretotal >= '10' AND scoretotal < '20'").count
  hash['level1']['over0'] = Ppscore.where("playlevel = '1' AND scoretotal >= '0' AND scoretotal < '10'").count


  hash['level0']['over100'] = Ppscore.where("playlevel = '0' AND scoretotal = '100'").count    
  hash['level0']['over90'] = Ppscore.where("playlevel = '0' AND scoretotal >= '90' AND scoretotal != '100'").count
  hash['level0']['over80'] = Ppscore.where("playlevel = '0' AND scoretotal >= '80' AND scoretotal < '90'").count
  hash['level0']['over70'] = Ppscore.where("playlevel = '0' AND scoretotal >= '70' AND scoretotal < '80'").count
  hash['level0']['over60'] = Ppscore.where("playlevel = '0' AND scoretotal >= '60' AND scoretotal < '70'").count
  hash['level0']['over50'] = Ppscore.where("playlevel = '0' AND scoretotal >= '50' AND scoretotal < '60'").count
  hash['level0']['over40'] = Ppscore.where("playlevel = '0' AND scoretotal >= '40' AND scoretotal < '50'").count
  hash['level0']['over30'] = Ppscore.where("playlevel = '0' AND scoretotal >= '30' AND scoretotal < '40'").count
  hash['level0']['over20'] = Ppscore.where("playlevel = '0' AND scoretotal >= '20' AND scoretotal < '30'").count
  hash['level0']['over10'] = Ppscore.where("playlevel = '0' AND scoretotal >= '10' AND scoretotal < '20'").count
  hash['level0']['over0'] = Ppscore.where("playlevel = '0' AND scoretotal >= '0' AND scoretotal < '10'").count

Answer 1

在一个查询中完成:

select playlevel, concat('over',floor(scoretotal/10)*10) score, count(*) c
from ppsscore
group by playlevel, concat('over',floor(scoretotal/10)*10)