python / intermediate
Snippet
Django QuerySet Optimization with select_related and prefetch_related
Django's QuerySet optimization methods dramatically reduce database queries by leveraging SQL JOINs or efficient separate queries. select_related follows ForeignKey and OneToOne relationships in a single JOIN query, while prefetch_related performs additional queries and does the joining in Python - essential for ManyToMany and reverse ForeignKey relationships. In a typical blog with 10 articles, each having an author and 5 comments, naive access would generate 1 + 10 + 10*5 = 61 queries. With these optimizations, it drops to just 4 queries.
snippet.py
python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class ArticleView(ListView):model = Articletemplate_name = 'articles.html'def get_queryset(self):return Article.objects.select_related('author', 'category')\.prefetch_related('tags', 'comments__author')# Equivalent raw query analysis:# select_related: Uses SQL JOINs for ForeignKey/OneToOne (1:1 relationships)# prefetch_related: Performs separate queries with Python joining (1:N, M:N)# Debug generated queries:# from django.db import connection# print(connection.queries)
django
Breakdown
1
select_related('author', 'category')
Creates SQL JOIN for ForeignKey relationships - single query fetches related objects
2
.prefetch_related('tags', 'comments__author')
Separate queries for M:N and reverse FK, then joins in Python memory
3
comments__author
Double underscore navigates nested relationships for prefetching