How could I rewrite this loop of Django queries as a more efficient SQL query?

Question

This is an inefficient way to update a denormalized field on an Player Django model. The field essentially stores the position of the player on the leaderboard, which is a requirement for a system we have for displaying "nearby" players to a given player.

for position, player in enumerate(Player.objects.order_by('-score')):
    player.position = position + 1
    player.save()

Is there a way to perform this update in one SQL query instead? The database backend we're using is MySQL.

Thanks for your time!

Answer 1

If you think of the SQL behind Player.objects.order_by('-score'), it's SELECT player.* from player order by player.score desc. I am not sure what you're looking to do is possible without a nested MySQL query, which technically, would be two queries anyways.

If you revisit your original solution, it's really not that complex.

If performance is your concern, I suggest you check out django-debug-toolbar if you haven't already. This little package provides timing and stats on queries. The latest version (0.8.3) also provides a CLI plugin that shows you the queries performed using manage.py shell (called debugsqlshell).