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

Our delivery is usually within minutes so that you may be nicely in your way to channeling focused traffic and embracing the success which you crave. So leverage the electricity of our services to seriously enhance your Instagram presence. buy Instagram likes

This will factory reset your Dell® epson printer drivers using a system recovery disk set, available by download, disk or bootable USB key. This is compatible with all languages of COA (product key) and will re-install the Dell® epson printer drivers in English (English US). epson printer drivers

I was surfing net and fortunately came across this site and found very interesting stuff here. Its really fun to read. I enjoyed a lot. Thanks for sharing this wonderful information.
click this site

Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.
The Astra Centre

Thank you for another great article. Where else could anyone get that kind of information in such a perfect way of writing? I have a presentation next week, and I am on the look for such information.
Geldbörse nähen

Thanks for every other informative site. The place else may just I get that kind of information written in such an ideal means? I have a venture that I’m just now operating on, and I have been on the look out for such information.
Posters

Hi, I find reading this article a joy. It is extremely helpful and interesting and very much looking forward to reading more of your work..
go here

Awesome article! I want people to know just how good this information is in your article. It’s interesting, compelling content. Your views are much like my own concerning this subject.
Pay Monthly Phones

Just admiring your work and wondering how you managed this blog so well. It’s so remarkable that I can't afford to not go through this valuable information whenever I surf the internet!
Things to do

I was surfing net and fortunately came across this site and found very interesting stuff here. Its really fun to read. I enjoyed a lot. Thanks for sharing this wonderful information.
zero carb recipes

When you work with The Cannabis Creative, you get a killer team of experts that not only have a personal understanding of the medicine of cannabis/CBD's, but also deep branding and marketing experience in a variety of different industries. cannabis creative

Very useful post. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up.
click here

begin growing veggies or fruit? Then it's miles important to buy a grower. via Growspot.nl you can order the pleasant cultivars. numerous brands just like the complete boom , Budbox & Homebox boom , massive boom and mystery Jardin Growers may be ordered from us. purchase the great first-rate cultivars at Growspot.nl. purchase your crop fast and quickly. growshop online

I'm happy I discovered this site, I couldn't discover any learning on this matter earlier to.Also work a website and in the event that you are ever keen on doing some guest composing for me if conceivable don't hesitate to tell me, im dependably search for individuals to look at my site. Bethard casino bij swingmasters

Keyboards may or may not have an internal power supply system built to the main circuit board, but most modern keyboards are often equipped with an included AC adapter. midi piano

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.

I think this is an enlightening post and it is extremely valuable and proficient. along these lines, I might want to thank you for the endeavors you have made in composing this article. Wordpress Webdesign Mainz

I am constantly flabbergasted by the measure of data accessible on this subject. What you displayed was very much inquired about and eloquent so as to get your stand on this crosswise over to every one of your perusers. Travel

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.

Pages

Add new comment