fast lookup for the last element in a Django QuerySet?

Question

I've a model called Valor. Valor has a Robot. I'm querying like this:

Valor.objects.filter(robot=r).reverse()[0]

to get the last Valor the the r robot. Valor.objects.filter(robot=r).count() is about 200000 and getting the last items takes about 4 seconds in my PC.

How can I speed it up? I'm querying the wrong way?

Answer 1

Well, there's no order_by clause so I'm wondering about what you mean by 'last'. Assuming you meant 'last added',

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

might do the job for you.

Answer 2

Is there a limit clause in django? This way you can have the db, simply return a single record.

mysql

 select * from table where x = y limit 1

sql server

 select top 1 * from table where x = y

oracle

 select * from table where x = y and rownum = 1

I realize this isn't translated into django, but someone can come back and clean this up.

Answer 3

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.

Answer 4

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_save

def record_last_valor(sender, **kwargs):
    if kwargs.get('created', False):
        instance = kwargs.get('instance')
        instance.robot.last_valor = instance

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