Django's select_related and prefetch_related

I was working on a personal website for my kids where I can post videos for them and I noticed that for each video link that was displayed on the page there were 2 additional SQL queries. One to get the username of the user that added the video link, and another to get the list of categories for the link.

The model looks like this:

class Link(models.Model):
    ...
    # There was an extra query per link to get the categories for a link
    category = models.ManyToManyField(Category)
    # There was an extra query per link to get the user.username
    user = models.ForeignKey(User)
    ...

The reason that an extra query was required to get the username is because the query to get all the links does not look at the user table at all. It doesn't do any joins. So it has to look up the username by doing something like "select username from users where users.pk == link.user_id". You get the idea. And it has to do this for every link on the page. This is easily fixed by doing

Link.objects.select_related().all()

or something similar. Django will then do a JOIN on the User table so it will now have all the user-related fields in the result set.

I was still getting a query for the category names on each link. To resolve this, I did:

Link.objects.select_related().all().prefetch_related('category')

This causes Django to do a fetch on the Category_Link table (the table that describes the many-to-many relationship) to get all the categories that match the primary keys for the links displayed on the current page. Then a join is done in Python, essentially mapping a list of category objects with each link object.

The number of queries for my home page was reduced from 44 down to 5. Page load time has been decreased by 1-2 orders of magnitude.

So, the naive rule-of-thumb might be to always use select_related() if you have a ForeignKey and you are going to reference fields in the ForeignKey in your template, and to always use prefetch_related if you have a ManyToManyField and you need information in the related table. For now I am going to NOT use these commands unless I first use the django-debug-toolbar and figure out what is going on. Then if it's necessary to use select_related or prefetch_related I will.

Tags: 

Comments

There are many computer languages and coding but this HTML code is really precious for me because I am finding this info from many other sources. This model of speakers are looking so nice and I like it by using http://www.superiorpaper.net/ website which is easy to understand.

Manage.norton.com is a powerful antivirus where you get maximum security against online threats such as adware, spywares, viruses and worms. It removes all the threats by deleting them from your computer system. Norton antivirus helps in securing your data. It is very easy to have Norton in your computer.
Norton setup Download
Downloading Norton com setup in your computer system is simple. Visit www.norton.com/setup and download the Norton setup. If you are having a Norton account, then it is good. Otherwise create a Norton account.
After creating Norton account, download the norton com setup. You can download, install and Activate Norton.com/setup in your system as to get the most powerful security against the viruses. Norton setup provides you complete protection from internet risks. It helps in securing your computer data and other important information.
Downloading the Norton antivirus is an easy task but sometimes people face difficulty in downloading Norton.com/setup. In that situation, users can get online help at Norton customer service number.
You can download Norton com setup from official website of Symantec company or you may use some third party websites. After downloading the Norton setup carefully, you can easily install the setup.

It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing.

We are a get-together of volunteers and new activities in a relative strong point. Online journal gave us tremendous data to work. Jenn

Great  post, you have pointed out some  fantastic  points , I  likewise  think  this s a very  wonderful website

Parc Life

Mowgli (Neel Sethi) who was raised since his birth in the middle of the wolves was made to leave the jungle due to a fearsome tiger Shere Khan, who consistently remained after him to attack. showbox app

Add new comment