Differences between select_related and prefetch_related in Django
Tags: django, python, database, sql
Published: 2024-06-16 02:19:38

---
title: Differences between select_related and prefetch_related in Django
published: true
date: 2024-06-16 22:00:00 UTC
tags: django,python,database,sql
canonical_url: https://coffeebytes.dev/en/differences-between-select_related-and-prefetch_related-in-django/
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u3isiegus69vnpdkhp71.jpg
---

Image credits to [ときわた](https://www.pixiv.net/en/users/5300811)

The *select_related* and *prefetch_related* methods **are used to reduce the number of queries made to the database**. This translates into response time for each view. In addition, using these methods is one of the [actions to implement to improve the performance of a Django application](https://coffeebytes.dev/en/is-your-django-application-slow-maximize-its-performance-with-these-tips/)

Just remember that there are better things to do for your app than to [obsess about its performance](https://coffeebytes.dev/en/dont-obsess-about-your-web-application-performance/).

## select\_related

The _select\_related_ method is **used to follow a relationship of type ForeignKey or OneToOneField to the respective objects it points to and obtain them.**.

When using _select\_related_ we will have a longer query, however, the advantage is that it will no longer be necessary to access the database again to obtain the objects of the related model.

![Simplified diagram of how Django select_related works](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o3xcx7ifog76559fy4lo.png)

Simplified diagram of how select\_related works

Consider this example:

```python
from django.db import models

class Main(models.Model):
    name = models.CharField(max_length=256)

class Derivative(models.Model):
    name = models.CharField(max_length=256)
    main = models.ForeignKey(
        "Main", related_name="derivatives", on_delete=models.CASCADE
    )
```

If we try to access the object pointed to by the Foreign Key relationship, a new database query will be generated. _select\_related_ avoids that extra query for each object.

```html
{% for object in queryset %}
    <p>{{object.name}}</p>
    <small>{{object.main.name}}</small>
{% endfor %}
```

For example, if we have three Derived objects related to a single main object:

- A main query that retrieves all objects Derivative
- Three queries, exactly the same, one for each time we access the main object from the Derived object.

### Use in a query

To use _select\_related_ we call it from our query, passing it the name of the field that corresponds to our relationship with the other model.

```python
Derivative.objects.select_related("main")
```

### Internal operation of select\_related

How _select\_related_ works internally, _select\_related_ replaces multiple queries being performed by a single INNER JOIN at the database level:

```sql
SELECT "my_app_derivative"."id",
       "my_app_derivative"."name",
       "my_app_derivative"."main_id"
  FROM "my_app_derivative"

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'
```

This reduces multiple SQL queries to a single, longer query.

```sql
SELECT "my_app_derivative"."id",
       "my_app_derivative"."name",
       "my_app_derivative"."main_id",
       "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_derivative"
 INNER JOIN "my_app_main"
    ON ("my_app_derivative"."main_id" = "my_app_main"."id")
```

## prefetch\_related

If the _select\_related_ method retrieves a single object from a single relationship field, **the _prefetch\_related_ method is used when we have a multiple relationship with another model** , i.e. a relationship of type \*\*_ManyToMany_ or a reverse _ForeignKey_.

![Simplified diagram of how Django prefetch_related works](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jgyuop69qpmie1b0cgzj.png)

Simplified diagram of how prefetch\_related works

Consider this example, note the _ManyToManyField_ field towards the _Principal_ model.


```python
from django.db import models

class Main(models.Model):
    name = models.CharField(max_length=256)

class ManyToManyModel(models.Model):
    name = models.CharField(max_length=256)
    ManyToManyRel = models.ManyToManyField("Main", related_name="multiples")
```

If we access the field that represents the multiple relation of our object, without using _prefetch\_related_, we will be impacting the database with a new query.

```html
{% for object in queryset %}
    <p>{{object.name}}</p>
    {% for main in object.ManyToManyRel.all %}
      <!-- New query each iteration -->
      <p><small>{{main.name}}</small></p>
    {% endfor %}
{% endfor %}
```

### Use in a query

To use the _prefetch\_related_ method call it at the end of our query, choosing the field that represents the many-to-many relationship in our object.

```python
queryset = ManyToManyModel.objects.prefetch_related("ManyToManyRel")
```

### Inner workings of prefetch\_related

How does _prefecth\_related_ work internally? The **_prefetch\_related_ method replaces the multiple SQL queries by only 2 SQL queries: one for the main query and the other for the related objects, then it will join the data using Python**.

```sql
SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '2'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '3'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '4'
```

The multiple queries above are reduced to only 2 SQL queries.

```sql
SELECT "my_app_manytomanyrel"."id",
       "my_app_manytomanyrel"."name"
  FROM "my_app_manytomanyrel"

SELECT ("my_app_manytomanyrel_main"."manytomanyrel_id") AS "*prefetch_related*val_manytomanyrel_id",
       "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" IN ('1', '2', '3', '4')
```

## Other related resources

- [¿What’s the difference between select\_related and prefetch\_related in django IRM?](https://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm)
- [Select related vs Prefecth related](https://buildatscale.tech/select_related-vs-prefetch_related/)
- [API Queryset reference](https://docs.djangoproject.com/en/dev/ref/models/querysets/)