The select_related and prefetch_related functions in the Django framework are used to optimize database queries. djangorelatedname

The select_related and prefetch_related functions in the Django framework are used to optimize database queries. djangorelatedname

Instance background

Assume that the Personal Information System must record the home, residence, and city of each individual in the system. The database design is as follows:

The content of Models. py is as follows:
 

from django.db import models class Province(models.Model): name = models.CharField(max_length=10) def __unicode__(self):  return self.name class City(models.Model): name = models.CharField(max_length=5) province = models.ForeignKey(Province) def __unicode__(self):  return self.name class Person(models.Model): firstname = models.CharField(max_length=10) lastname = models.CharField(max_length=10) visitation = models.ManyToManyField(City, related_name = "visitor") hometown = models.ForeignKey(City, related_name = "birth") living  = models.ForeignKey(City, related_name = "citizen") def __unicode__(self):  return self.firstname + self.lastname

NOTE 1: The created app is named "QSOptimize"

NOTE 2: For the sake of simplicity, the 'qsoptimize _ province 'table has only two data entries: Hubei province and Guangdong province, and the 'qsoptimize _ City' table has only three data entries: Wuhan, Shiyan city, and Guangzhou city.

If we want to get people from all our hometown cities in Hubei province, the brainless way is to first get people from Hubei Province, then all cities in Hubei province, and finally get people from this city. Like this:
 

>>> Hb = Province. objects. get (name _ iexact = u "Hubei Province") >>>> people = [] >>> for city in hb. city_set.all ():... people. extend (city. birth. all ())...

Obviously, this is not a wise choice, because this will lead to 1 + (number of cities in Hubei Province) SQL queries. It is an inverse example, and the query and result obtained will not be listed.
Prefetch_related () may be a good solution. Let's take a look.
 

>>> Hb = Province. objects. prefetch_related ("city_set _ birth "). objects. get (name _ iexact = u "Hubei Province") >>>> people = [] >>> for city in hb. city_set.all ():... people. extend (city. birth. all ())...

Because it is a prefetch with a depth of 2, it will cause three SQL queries:
 

SELECT 'qsoptimize _ province '. 'id', 'qsoptimize _ province '. 'name' FROM 'qsoptimize _ province 'WHERE 'qsoptimize _ province '. 'name' LIKE 'hubei province '; SELECT 'qsoptimize _ City '. 'id', 'qsoptimize _ City '. 'name', 'qsoptimize _ City '. 'province _ id' FROM 'qsoptimize _ City' WHERE 'qsoptimize _ City '. 'vince _ id' IN (1); SELECT 'qsoptimize _ person '. 'id', 'qsoptimize _ person '. 'firstname', 'qsoptimize _ person '. 'lastname', 'qsoptimize _ person '. 'hometown _ id', 'qsoptimize _ person '. 'Living _ id' FROM 'qsoptimize _ person' WHERE 'qsoptimize _ person '. 'hometown _ id' IN (1, 3 );

Well... It looks good, but is it three queries? Which of the following queries may be simpler?
 

>>> People = list (Person. objects. select_related ("hometown _ province "). filter (hometown _ province _ name _ iexact = u "Hubei province") SELECT 'qsoptimize _ person '. 'id', 'qsoptimize _ person '. 'firstname', 'qsoptimize _ person '. 'lastname', 'qsoptimize _ person '. 'hometown _ id', 'qsoptimize _ person '. 'Living _ id', 'qsoptimize _ City '. 'id', 'qsoptimize _ City '. 'name', 'qsoptimize _ City '. 'province _ id', 'qsoptimize _ province '. 'id', 'qsoptimize _ province '. 'name' FROM 'qsoptimize _ person 'inner join 'qsoptimize _ City' ON ('qsoptimize _ body '. 'hometown _ id' = 'qsoptimize _ City '. 'id') inner join 'qsoptimize _ province 'ON ('qsoptimize _ City '. 'province _ id' = 'qsoptimize _ province '. 'id') WHERE 'qsoptimize _ province '. 'name' LIKE 'hubei province '; + ---- + ----------- + ---------- + ------------- + ----------- + ---- + -------- + ------------- + ---- + -------- + | id | firstname | lastname | response | living_id | id | name | province_id | id | name | + ---- + ----------- + ---------- + ------------- + --------- + ---- + -------- + ------------- + ---- + -------- + | 1 | Zhang | 3 | 3 | 3 | Shiyan city | 1 | 1 | Hubei Province | 2 | Li | 4 | 1 | 3 | 1 | Wuhan City | 1 | 1 | Hubei Province | 3 | Wang | Machin | 3 | 2 | 3 | Shiyan city | 1 | 1 | Hubei Province | + ---- + ----------- + ---------- + ------------- + ----------- + ---- + -------- + ------------- + ---- + -------- + 3 rows in set (0.00 sec)

No problem at all. This not only reduces the number of SQL queries, but also simplifies the python program.
Select_related () is more efficient than prefetch_related (). Therefore, it is best to use select_related () Wherever possible, that is, for the ForeignKey field, avoid using prefetch_related ().
Combination
For the same QuerySet, you can use both functions.
Add a model: Order (Order) to the example we have been using)
 

class Order(models.Model): customer = models.ForeignKey(Person) orderinfo = models.CharField(max_length=50) time  = models.DateTimeField(auto_now_add = True) def __unicode__(self):  return self.orderinfo

If we get an order id, we need to know the province that the customer of this order has been. Because ManyToManyField must use prefetch_related (). What if I only use prefetch_related?
 

>>> plist = Order.objects.prefetch_related('customer__visitation__province').get(id=1)>>> for city in plist.customer.visitation.all():... print city.province.name...

Obviously, four tables are related: Order, Person, City, and Province. According to the prefetch_related () feature, four SQL queries are required.
 

SELECT `QSOptimize_order`.`id`, `QSOptimize_order`.`customer_id`, `QSOptimize_order`.`orderinfo`, `QSOptimize_order`.`time`FROM `QSOptimize_order`WHERE `QSOptimize_order`.`id` = 1 ; SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`FROM `QSOptimize_person`WHERE `QSOptimize_person`.`id` IN (1); SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`FROM `QSOptimize_city`INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)WHERE `QSOptimize_person_visitation`.`person_id` IN (1); SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`FROM `QSOptimize_province`WHERE `QSOptimize_province`.`id` IN (1, 2);

+ ---- + ------------- + Hour + | id | customer_id | orderinfo | time | + ---- + ------------- + --------------- + hour + | 1 | 1 | Info of Order | 17:05:48 | + ---- + ------------- + --------------- + ------------------- + 1 row in set (0.00 sec) + ---- + ----------- + ---------- + ------------- + ----------- + | id | firstname | lastname | region | living_id | + ---- + ----------- + ---------- + ------------- + ----------- + | 1 | 3 | 3 | 1 | + ---- + ----------- + ---------- + ------------- + ----------- + 1 row in set (0.00 sec) + keys + ---- + -------- + ------------- + | _ prefetch_related_val | id | name | province_id | + --------------------- + ---- + -------- + ------------- + | 1 | 1 | Wuhan | 1 | 2 | Guangzhou | 2 | 1 | 3 | Shiyan city | 1 | + ------------------------- + ---- + -------- + ------------- + 3 rows in set (0.00 sec) + ---- + -------- + | id | name | + ---- + -------- + | 1 | Hubei Province | 2 | Guangdong Province | + ---- + -------- + 2 rows in set (0.00 sec)

A better solution is to call select_related () First, call prefetch_related (), and then the table after select_related ().
 

>>> plist = Order.objects.select_related('customer').prefetch_related('customer__visitation__province').get(id=1)>>> for city in plist.customer.visitation.all():... print city.province.name...

In this way, there will be only three SQL queries. Django will first make select_related, and then use the previously cached data when prefetch_related, thus avoiding one additional SQL query:

SELECT 'qsoptimize _ order '. 'id', 'qsoptimize _ order '. 'Customer _ id', 'qsoptimize _ order '. 'orderinfo', 'qsoptimize _ order '. 'time', 'qsoptimize _ person '. 'id', 'qsoptimize _ person '. 'firstname', 'qsoptimize _ person '. 'lastname', 'qsoptimize _ person '. 'hometown _ id', 'qsoptimize _ person '. 'Living _ id' FROM 'qsoptimize _ order' inner join 'qsoptimize _ person' ON ('qsoptimize _ order '. 'Customer _ id' = 'qsoptimize _ person '. 'id') WHERE 'qsoptimize _ order '. 'id' = 1; SELECT ('qsoptimize _ person_visitation '. 'person _ id') AS '_ prefetch_related_val', 'qsoptimize _ City '. 'id', 'qsoptimize _ City '. 'name', 'qsoptimize _ City '. 'province _ id' FROM 'qsoptimize _ City' inner join 'qsoptimize _ person_visitation 'ON ('qsoptimize _ City '. 'id' = 'qsoptimize _ person_visitation '. 'city _ id') WHERE 'qsoptimize _ person_visitation '. 'person _ id' IN (1); SELECT 'qsoptimize _ province '. 'id', 'qsoptimize _ province '. 'name' FROM 'qsoptimize _ province 'WHERE 'qsoptimize _ province '. 'id' IN (1, 2 ); + ---- + ------------- + hour + ---- + ----------- + ---------- + ----------- + | id | customer_id | orderinfo | time | id | firstname | lastname | hometown_id | living_id | + ---- + ------------- + --------------- + certificate + ---- + ----------- + ---------- + ------------- + ----------- + | 1 | 1 | Info of Order | 17:05:48 | 1 | 3 | 3 | 1 | + ---- + ------------- + --------------- + ------------------- + ---- + ----------- + ------------ + ------------- + ----------- + 1 row in set (0.00 sec) + keys + ---- + -------- + ------------- + | _ prefetch_related_val | id | name | province_id | + --------------------- + ---- + -------- + ------------- + | 1 | 1 | Wuhan | 1 | 2 | Guangzhou | 2 | 1 | 3 | Shiyan city | 1 | + ------------------------- + ---- + -------- + ------------- + 3 rows in set (0.00 sec) + ---- + -------- + | id | name | + ---- + -------- + | 1 | Hubei Province | 2 | Guangdong Province | + ---- + -------- + 2 rows in set (0.00 sec)

It is worth noting that select_related can be called before prefetch_related is called, and Django will do what you want: Select t_related first, and then use the cached data prefetch_related. However, once prefetch_related has been called, select_related does not work.

 Summary

1. Because select_related () always solves the problem in a single SQL query, and prefetch_related () queries each related table, the efficiency of select_related () is usually higher than that of the latter.
2. In view of article 1, use select_related () as much as possible to solve the problem. You can only think about prefetch_related () When select_related () cannot solve the problem ().
3. You can use select_related () and prefetch_related () in a QuerySet to reduce the number of SQL queries.
4. Only select_related () before prefetch_related () is valid and will be ignored later.