fast lookup for the last element in a Django QuerySet?

The optimal mysql syntax for this problem would be something along the lines of:

SELECT * FROM table WHERE x=y ORDER BY z DESC LIMIT 1

The django equivalent of this would be:

Valor.objects.filter(robot=r).order_by('-id')[:1][0]

Notice how this solution utilizes django's slicing method to limit the queryset before compiling the list of objects.

If none of the earlier suggestions are working, I'd suggest taking Django out of the equation and run this raw sql against your database. I'm guessing at your table names, so you may have to adjust accordingly:

SELECT * FROM valor v WHERE v.robot_id = [robot_id] ORDER BY id DESC LIMIT 1;

Is that slow? If so, make your RDBMS (MySQL?) explain the query plan to you. This will tell you if it's doing any full table scans, which you obviously don't want with a table that large. You might also edit your question and include the schema for the valor table for us to see.

Also, you can see the SQL that Django is generating by doing this (using the query set provided by Peter Rowell):

qs = Valor.objects.filter(robot=r).order_by('-id')[0]print qs.query

Make sure that SQL is similar to the 'raw' query I posted above. You can also make your RDBMS explain that query plan to you.

It sounds like your data set is going to be big enough that you may want to denormalize things a little bit. Have you tried keeping track of the last Valor object in the Robot object?

class Robot(models.Model):    # ...    last_valor = models.ForeignKey('Valor', null=True, blank=True)

And then use a post_save signal to make the update.

from django.db.models.signals import post_savedef record_last_valor(sender, **kwargs):    if kwargs.get('created', False):        instance = kwargs.get('instance')        instance.robot.last_valor = instancepost_save.connect(record_last_valor, sender=Valor)

You will pay the cost of an extra db transaction when you create the Valor objects but the last_valor lookup will be blazing fast. Play with it and see if the tradeoff is worth it for your app.