CODEWITHSUNDEEP
phpmysqldatabase
Martin
Martin

Reputation: 1135

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

I am trying to get a sum of scores for skills of individuals in a table of skills in a PHP script.

The table is InnoDB, has ~2m records and is structured: skills (id,cand_id,skillname,score).

An example of the query I have is:

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")

The query takes about 0.0006s but there are 50,000 people to loop through so it takes a while!

How can I speed this up maybe 10 times?

Thanks.

Upvotes: 0

Views: 431

Answers (3)

zebnat
zebnat

Reputation: 521

You are doing a query for each cand_id, triggering querys in a loop is your bottle neck.

As two people already told you, use the GROUP BY cand_id statement at the end of the query, you will win performance and not needed code.

Upvotes: 2

RobertoP
RobertoP

Reputation: 637

You are asking for a GROUP BY. This query will list each unique cand_id and it will sum up all of the scores for each 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

Upvotes: 1

Phil Wallach
Phil Wallach

Reputation: 3318

You need to calculate all the values in one query ...

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

Upvotes: 1

Related Questions