我如何加快这个mysql查询在一个大型mysql表(200万行)中求和得分 - How do I speed this mysql query up to sum a score in a large mysql table (2million rows)

How do I speed this mysql query up to sum a score in a large mysql table (2million rows)

我试图在PHP脚本中的技能表中获得个人技能的总和。

表为InnoDB，有~2m条记录，结构为:skills (id,cand_id,skillname,score)

我有一个查询的例子是:

select SUM(score) as skillscore FROM skills WHERE cand_id = "6509" AND skillname in ("Cascading Style Sheets","jQuery","Personal Home Page","MySQL","JavaScript","HTML","Android","industry~it")

查询大约需要0.0006秒，但有50,000人要循环，所以需要一段时间!

我怎样才能把速度提高10倍?

谢谢。

您正在为每个cand_id进行查询，在循环中触发查询是您的瓶颈。

正如两个人已经告诉你的那样，在查询结束时使用GROUP BY cand_id语句，您将获得性能和不需要的代码。

您需要在一个查询中计算所有值…

select cand_id, SUM(score) as skillscore
FROM skills 
WHERE skillname in ("Cascading Style Sheets","jQuery","Personal Home Page","MySQL","JavaScript","HTML","Android","industry~it") 
GROUP BY cand_id

您正在请求GROUP BY。这个查询将列出每个唯一的cand_id，它将总结每个cand_id的所有分数。

SELECT cand_id, SUM(score) as skillscore 
FROM skills WHERE skillname in 
("Cascading Style Sheets","jQuery",
"Personal Home Page","MySQL","JavaScript","HTML","Android","industry~it")
GROUP BY cand_id