CODEWITHSUNDEEP
pythonmysqldjango
SuzukiBKing
SuzukiBKing

Reputation: 178

Faster way to find latest entry for a given period in Django

I've got a basic event model with the following fields:

I need to return the latest value of description for a given key prior to a given date. Under normal conditions I would use the following query:

desc = Event.objects.filter(key=key,time_stamp__lte=date).order_by('-id').values_list('description',flat=True)[0]

However in this case the model has 2.5 million records, so it takes about 15 seconds to execute. I managed to get a small improvement in speed by specifying a time_stamp__range, but that limits how far back the latest entry can be which I don't really want.

I've done some testing and the order_by('-id') is what's slowing it down.

I'm considering splitting the event model into models for types of event keys. This will decrease the number of records per model, but before I go that route, can anybody perhaps recommend a faster way of get the last record of a query-set?

Upvotes: 2

Views: 224

Answers (1)

Guillermo Silva
Guillermo Silva

Reputation: 381

You should try with db index

Model index reference

from django.db import models    

class Event():
    key = ...
    time_stamp = ...

    class Meta:
        index = [
            models.Index(fields=['key', '-time_stamp']),

    ]

Upvotes: 2

Related Questions