## Select_related
`select_related` is an optional performance booster by which further access to the property of foreign_keys in a **Queryset**, won't hit the database.
[**Design philosophies**][1]
> This is also why the select_related() QuerySet method exists. It’s an optional performance booster for the common case of selecting “every related object.”
[**Django official doc**][2]
> Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.
As pointed in the definition, using `select_related` only is allowed in **foreign_key relationships**. Ignoring this rule will face you with below exception:
```python
In [21]: print(Book.objects.select_related('name').all().query)
FieldError: Non-relational field given in select_related: 'name'. Choices are: author
```
### Let's dive into it with an example:
Here are my `models.py`. (It's the same as Question asked)
```python
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=50)
def __str__(self):
return self.name
__repr__ = __str__
class Book(models.Model):
name = models.CharField(max_length=50)
author = models.ForeignKey(Author, related_name='books', on_delete=models.DO_NOTHING)
def __str__(self):
return self.name
__repr__ = __str__
```
- **Fetching all books and their author's using the `relect_related` booster:**
```python
In [25]: print(Book.objects.select_related('author').all().explain(verbose=True, analyze=True))
Hash Join (cost=328.50..548.39 rows=11000 width=54) (actual time=3.124..8.013 rows=11000 loops=1)
Output: library_book.id, library_book.name, library_book.author_id, library_author.id, library_author.name
Inner Unique: true
Hash Cond: (library_book.author_id = library_author.id)
-> Seq Scan on public.library_book (cost=0.00..191.00 rows=11000 width=29) (actual time=0.008..1.190 rows=11000 loops=1)
Output: library_book.id, library_book.name, library_book.author_id
-> Hash (cost=191.00..191.00 rows=11000 width=25) (actual time=3.086..3.086 rows=11000 loops=1)
Output: library_author.id, library_author.name
Buckets: 16384 Batches: 1 Memory Usage: 741kB
-> Seq Scan on public.library_author (cost=0.00..191.00 rows=11000 width=25) (actual time=0.007..1.239 rows=11000 loops=1)
Output: library_author.id, library_author.name
Planning Time: 0.234 ms
Execution Time: 8.562 ms
In [26]: print(Book.objects.select_related('author').all().query)
SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_author"."id", "library_author"."name" FROM "library_book" INNER JOIN "library_author" ON ("library_book"."author_id" = "library_author"."id")
```
As you can see, using select_related cause an **INNER JOIN** on the provided foreign keys(Here was `author`).
The execution time which the time of:
- Running the query using the quickest plan which has been chosen by the planner
- Returning the results
Is **8.562 ms**
On the other hand:
- **Fetching all books and their author's without using the relect_related booster:**
```python
In [31]: print(Book.objects.all().explain(verbose=True, analyze=True))
Seq Scan on public.library_book (cost=0.00..191.00 rows=11000 width=29) (actual time=0.017..1.349 rows=11000 loops=1)
Output: id, name, author_id
Planning Time: 1.135 ms
Execution Time: 2.536 ms
In [32]: print(Book.objects.all().query)
SELECT "library_book"."id", "library_book"."name", "library_book"."author_id" FROM "library_book
```
As you can see, It's just a simple **SELECT** query on book models that only contains **author_id**. The execution time, in this case, is **2.536 ms**.
**As mentioned in the Django [doc][5]:**
Further access to the foreign-key properties will cause another hit on the database: (CUZ we don't have them already)
```python
In [33]: books = Book.objects.all()
In [34]: for book in books:
...: print(book.author) # Hit the database
```
See Also [Database access optimization][5] and [`explain()`][6] in QuerySet API reference
**[Django Database Caching:][7]**
> Django comes with a robust cache system that lets you save dynamic pages so they don’t have to be calculated for each request. For convenience, Django offers different levels of cache granularity: You can cache the output of specific views, you can cache only the pieces that are difficult to produce, or you can cache your entire site.
> Django also works well with “downstream” caches, such as Squid and browser-based caches. These are the types of caches that you don’t directly control but to which you can provide hints (via HTTP headers) about which parts of your site should be cached, and how.
You should read those docs to find out which of them suits you most.
________________________________________________________________________________
**PS1:** for gaining further information about the planner and how it's work see [
[To see links please register here]
][3] and [Using EXPLAIN][4])
________________________________________________________________________________
[1]:
[To see links please register here]
[2]:
[To see links please register here]
[3]:
[To see links please register here]
[4]:
[To see links please register here]
[5]:
[To see links please register here]
[6]:
[To see links please register here]
[7]:
[To see links please register here]