prefetch_relatedとselect_relatedの違い

Djangoのモデルでリレーション先のデータの取得の方法に、prefetch_relatedとselect_relatedがある。それぞれどのようなSQLが発行されるのかを確認した。

以下のモデル定義を使って確認する。

from django.db import models


class Organization(models.Model):
    value = models.IntegerField(default=1)


class User(models.Model):
    organization = models.ForeignKey(Organization)


class Tag(models.Model):
    name = models.CharField(max_length=20, default='FISH')
    organizations = models.ManyToManyField(Organization)

select_related

In [1]: from fish.accounts.models import *

In [2]: User.objects.select_related('organization').all()
Out[2]: <QuerySet []>

2017-01-29T23:42:29.830844Z        39 Query     SELECT `accounts_user`.`id`, `accounts_user`.`organization_id`, `accounts_organization`.`id`, `accounts_organization`.`value` FROM `accounts_user` INNER JOIN `accounts_organization` ON (`accounts_user`.`organization_id` = `accounts_organization`.`id`) LIMIT 21

prefetch_related

In [3]: User.objects.prefetch_related('organization').all()
Out[3]: <QuerySet []>

2017-01-29T23:43:24.614393Z        39 Query     SELECT `accounts_user`.`id`, `accounts_user`.`organization_id` FROM `accounts_user` LIMIT 21

In [16]: Tag.objects.prefetch_related('organizations')
Out[16]: <QuerySet [<Tag: Tag object>]>

2017-01-29T23:48:22.894230Z        43 Query     SELECT `accounts_tag`.`id`, `accounts_tag`.`name` FROM `accounts_tag` LIMIT 21
2017-01-29T23:48:22.896554Z        43 Query     SELECT (`accounts_tag_organizations`.`tag_id`) AS `_prefetch_related_val_tag_id`, `accounts_organization`.`id`, `accounts_organization`.`value` FROM `accounts_organization` INNER JOIN `accounts_tag_organizations` ON (`accounts_organization`.`id` = `accounts_tag_organizations`.`organization_id`) WHERE `accounts_tag_organizations`.`tag_id` IN (1)