Database query optimization with select_related in Django - Step-by-Step Execution

Concept Flow - Database query optimization with select_related

Start Query

↓

Check for ForeignKey

Yes↓

Use select_related

↓

Single SQL Join Query

↓

Fetch Related Data Together

↓

Return Optimized Result

↓

End

The flow shows how select_related checks for foreign keys and fetches related data in one SQL query using joins, improving performance.

Execution Sample

Django

books = Book.objects.select_related('author').all()
for book in books:
    print(book.title, book.author.name)

This code fetches books and their authors in one query, then prints each book's title and author's name.

Execution Table

Step	Action	SQL Query	Result	Notes
1	Call select_related('author')	SELECT * FROM book JOIN author ON book.author_id = author.id	Query prepared with join	One SQL query with join ready
2	Execute query	Same as above	Database returns combined rows	Data for book and author together
3	Iterate over books	No new query	Access book.title and book.author.name	No extra queries due to select_related
4	Print output	No query	Prints book title and author name	Efficient data access
5	End iteration	No query	All data processed	No additional DB hits

💡 All books and their authors fetched in one query, no extra queries triggered during iteration

Variable Tracker

Variable	Start	After Query	After Iteration 1	After Iteration 2	Final
books	QuerySet (not evaluated)	List of Book objects with author loaded	Book object 1 with author	Book object 2 with author	All Book objects with authors

Key Moments - 2 Insights

Why does select_related reduce the number of queries?

Because select_related uses a SQL JOIN to fetch related objects in one query, as shown in execution_table step 1 and 2, avoiding separate queries per object.

What happens if we don't use select_related when accessing related fields?

Without select_related, each access to a related field triggers a new query, causing many queries during iteration, unlike the single query in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution table, what SQL query is executed at step 2?

A SELECT * FROM book JOIN author ON book.author_id = author.id

B SELECT * FROM book

C SELECT * FROM author

D No SQL query is executed

💡 Hint

Check the SQL Query column in execution_table row 2

At which step does the code avoid extra database queries when accessing author data?

A Step 1

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the Result and Notes columns in execution_table row 3

If select_related was not used, how would the execution table change?

A The initial query would include a JOIN

B No queries would be executed

C More queries would appear during iteration steps

D The query would fetch unrelated data

💡 Hint

Refer to key_moments about queries triggered without select_related

Concept Snapshot

Use select_related('related_field') to fetch related objects in one SQL JOIN query.
This reduces the number of database queries during iteration.
Without it, accessing related fields triggers extra queries.
Ideal for ForeignKey and OneToOne relationships.
Improves performance by loading related data eagerly.

Full Transcript

This visual trace shows how Django's select_related optimizes database queries by using a SQL JOIN to fetch related objects in one query. Initially, select_related prepares a query joining the main and related tables. When executed, the database returns combined rows with all needed data. Iterating over the results accesses related fields without extra queries, making data retrieval efficient. Without select_related, each related field access would cause a new query, slowing down the app. This technique is best for ForeignKey or OneToOne relations to improve performance.

Practice

1. What is the main purpose of using select_related in Django queries?

easy

A. To reduce the number of database queries by joining related tables

B. To create new database tables automatically

C. To delete related objects from the database

D. To update multiple records at once

Solution

1. Step 1: Understand what select_related does

   select_related is used to fetch related objects in a single database query by joining tables.

2. Step 2: Identify the main benefit

   This reduces the number of queries and improves performance when accessing related data.

3. Final Answer:

   To reduce the number of database queries by joining related tables -> Option A

4. Quick Check:

   select_related reduces queries = D [OK]

Hint: Remember: select_related joins tables to reduce queries [OK]

Common Mistakes:

• Thinking select_related creates or deletes tables
• Confusing select_related with update or delete operations
• Assuming select_related works for many-to-many relations

2. Which of the following is the correct syntax to use select_related to fetch related author objects in a Book model query?

easy

A. Book.objects.select_related['author'].all()

B. Book.objects.select_related.author().all()

C. Book.objects.select_related('author')()

D. Book.objects.select_related('author').all()

Solution

1. Step 1: Recall the correct method call syntax

   select_related is a queryset method that takes related field names as string arguments inside parentheses.

2. Step 2: Check each option

   Book.objects.select_related('author').all() uses correct method call with parentheses and string argument. Others misuse dot notation, brackets, or call syntax.

3. Final Answer:

   Book.objects.select_related('author').all() -> Option D

4. Quick Check:

   Method call with string arg = C [OK]

Hint: Use parentheses and quotes: select_related('field') [OK]

Common Mistakes:

• Using dot notation instead of parentheses
• Using square brackets instead of parentheses
• Calling select_related without parentheses

3. Given these models:

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

What will this code print?

books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name)

medium

A. Prints author names but runs one query per book

B. Raises an error because select_related is used incorrectly

C. Prints all author names with only one database query

D. Prints book titles instead of author names

Solution

1. Step 1: Understand select_related effect on queries

   Using select_related('author') fetches books and their related authors in one query.

2. Step 2: Analyze the loop output

   The loop prints book.author.name for each book, showing author names without extra queries.

3. Final Answer:

   Prints all author names with only one database query -> Option C

4. Quick Check:

   select_related joins tables = A [OK]

Hint: select_related fetches related data in one query [OK]

Common Mistakes:

• Thinking select_related causes multiple queries
• Confusing select_related with prefetch_related
• Expecting book titles instead of author names

4. What is wrong with this Django query?

books = Book.objects.select_related('publisher').all()

Assuming Book has no publisher foreign key field.

medium

A. It will run but ignore the 'publisher' argument

B. It will raise a FieldError because 'publisher' is not a valid related field

C. It will fetch all books and publishers anyway

D. It will cause a syntax error

Solution

1. Step 1: Check if 'publisher' is a related field on Book

   Since Book has no publisher foreign key, this field does not exist.

2. Step 2: Understand select_related behavior with invalid fields

   Using an invalid field name in select_related raises a FieldError.

3. Final Answer:

   It will raise a FieldError because 'publisher' is not a valid related field -> Option B

4. Quick Check:

   Invalid field in select_related = FieldError = B [OK]

Hint: Check related field names exist before using select_related [OK]

Common Mistakes:

• Assuming invalid fields are ignored
• Expecting silent failure or warnings
• Confusing syntax errors with runtime FieldErrors

5. You have these models:

class Publisher(models.Model):
    name = models.CharField(max_length=100)

class Author(models.Model):
    name = models.CharField(max_length=100)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

How do you optimize a query to get all books with their authors and authors' publishers in the fewest queries?

hard

A. Book.objects.select_related('author', 'author__publisher').all()

B. Book.objects.select_related('author').select_related('publisher').all()

C. Book.objects.prefetch_related('author', 'author__publisher').all()

D. Book.objects.select_related('publisher').all()

Solution

1. Step 1: Identify the related fields to join

   We want to fetch author and the publisher related to that author in one query.

2. Step 2: Use nested select_related syntax

   Use select_related('author', 'author__publisher') to join both foreign keys in one query.

3. Step 3: Evaluate other options

   Book.objects.select_related('author').select_related('publisher').all() is invalid because publisher is not directly on Book. Book.objects.prefetch_related('author', 'author__publisher').all() uses prefetch_related which is less efficient here. Book.objects.select_related('publisher').all() misses author relation.

4. Final Answer:

   Book.objects.select_related('author', 'author__publisher').all() -> Option A

5. Quick Check:

   Nested select_related joins = A [OK]

Hint: Chain related fields with double underscores in select_related [OK]

Common Mistakes:

• Trying to select_related unrelated fields directly
• Using prefetch_related instead of select_related for foreign keys
• Missing nested relation syntax with double underscores