Optimizing Django database queries

Lazy queries

Django QuerySets are lazy, and that's actually a good thing. But you need to use it properly, if not, things can get slow. When I say that they are lazy, I mean that Django don't send the query to the database before you actually start working with the data/showing the data. Take a look at this example:

# The query
posts = Post.objects.all()
drafts = posts.filter(status=Post.DRAFT)

# The query still has not been executed. But as soon as we start using the data in a template, it will

{% for post in posts %}
  {{ post.title }}
{% endfor %}

This was a really simple example. And you can't really go wrong as long as the queries are as simple as this. The problem here would be if the post contained related comments or something similar. Let me show you how this should be handled.

Example, not good

First of all, let's say we have a database model similar to this (with at least one ForeignKey):

class Post(models.Model):
  title = models.CharField(max_length=255)
  author = models.ForeignKey(User)

Let's set up the QuerySet to get the posts

posts = Post.objects.all()

for post in posts:
  print (post.author.name)

The problem with this code is that every iteration of the posts loop makes a new query to the database where it's getting the author (User). This makes it really slow when you're starting to get a few entries in your database.

Example, good

# Backend
posts = Post.objects.select_related('author').all()

By adding a select_related, you are making a joined SQL. So instead of one new query per iteration, you just get the author at the same time you get the posts.

Summary

Another great tip I have is to install a plugin called Django Debug Toolbar. It's free and it's really awesome!

When you have Django Debug Toolbar installed and run the first code (without the select_releated), you'll see that the number of queries will be quite high. As soon as you add select_related, you will see that it starts dropping.

Play around with Django Debug Toolbar and see if there are other places you can use select_related at your site :-)