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!

jathanism · Accepted Answer · 2010-03-10 15:10:56Z

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

answered Mar 10, 2010 at 15:10

jathanism

33.9k9 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related