sql query is too slow, how to improve speed
- by user1289282
I have run into a bottleneck when trying to update one of my tables.
The player table has, among other things, id, skill, school, weight.
What I am trying to do is:
SELECT id, skill
FROM player
WHERE player.school = (current school of 4500)
AND player.weight = (current weight of 14)
to find the highest skill of all players returned from the query
UPDATE player
SET starter = 'TRUE'
WHERE id = (highest skill)
move to next weight and repeat
when all weights have been completed
move to next school and start over
all schools completed, done
I have this code implemented and it works, but I have approximately 4500 schools totaling 172000 players and the way I have it now, it would take probably a half hour or more to complete (did not wait it out), which is way too slow.
How to speed this up? Short of reducing the scale of the system, I am willing to do anything that gets the intended result.
Thanks!
*the weights are the standard folk style wrestling weights
ie, 103, 113, 120, 126, 132, 138, 145, 152, 160, 170, 182, 195, 220, 285 pounds
