Improving performance of django DB query

Question

I'm using django/apache/sqlite3 and I have a django model that looks like this:

class Temp_entry(models.Model):
    dateTime = models.IntegerField() #datetime
    sensor = models.IntegerField()   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

I'm trying to get the last 300 Temp_entry items to place into a graph. I do that this way:

revOutsideTempHistory = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse()[:300]

However, this query takes ~1 second. Is there a way to improve this? I've dug around and found that order_by is horrible inefficient, so I'm hoping that there is a viable alternative?

An alternative I thought of, but can't figure out how to implement, would be to run the query every 20 minutes and keep it cached, that would be acceptable too, as the data can be slightly stale with no ill effects.

Answer 1

If caching is acceptable it always should be used. Something like:

from django.core.cache import cache

cached = cache.get('temp_entries')
if cached:
    result = cached 
else:
    result = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse().values_list()[:300]
    cache.set('temp_entries', result, 60*20)  # 20 min

Also you can set db_indexes for the appropriate columns

class Temp_entry(models.Model):
    dateTime = models.IntegerField(db_index=True) #datetime
    sensor = models.IntegerField(db_index=True)   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

Answer 2

Johnny Cache! http://packages.python.org/johnny-cache/ It works out-of-the-box, and it works well!

Answer 3

You probably need to add some more indexes in your database. Use the django-debug toolbar to get the SQL of the actual query that's being run, and use the EXPLAIN feature to show what indexes it's using. For this particular query, I'd imagine you need to add an index on (sensor, dateTime) - do that directly in the database shell.