Database query optimization in Flask - Step-by-Step Execution

Concept Flow - Database query optimization

Write Query in Flask

↓

Send Query to DB

↓

DB Executes Query

↓

Check Query Performance

↓

Optimize Query

↓

Repeat Until Fast Enough

↓

Return Results to Flask

This flow shows how a Flask app sends a database query, checks its speed, and improves it step-by-step.

Execution Sample

Flask

from flask_sqlalchemy import SQLAlchemy

# Slow query example
users = User.query.filter(User.age > 20).all()

This code fetches all users older than 20 from the database using Flask-SQLAlchemy.

Execution Table

Step	Action	Query Sent	Execution Time	Optimization Applied	Result
1	Initial query sent	SELECT * FROM users WHERE age > 20;	500ms	None	All users > 20 fetched
2	Add index on age column	Same query	50ms	Index on age	Faster fetch of users > 20
3	Select only needed columns	SELECT id, name FROM users WHERE age > 20;	30ms	Column selection	Less data fetched
4	Limit results for pagination	SELECT id, name FROM users WHERE age > 20 LIMIT 10;	10ms	Limit results	Only 10 users fetched
5	Query optimized enough	N/A	N/A	Stop optimization	Return results to Flask

💡 Optimization stops when query runs fast enough for app needs.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Execution Time	N/A	500ms	50ms	30ms	10ms	10ms
Query	N/A	SELECT * FROM users WHERE age > 20;	Same query	SELECT id, name FROM users WHERE age > 20;	SELECT id, name FROM users WHERE age > 20 LIMIT 10;	Final optimized query
Result Size	N/A	Large	Large	Smaller	Small	Small

Key Moments - 3 Insights

Why does adding an index speed up the query so much?

Adding an index lets the database find rows faster without scanning the whole table, as shown by the drop from 500ms to 50ms in execution time in the execution_table.

Why select only needed columns instead of *?

Selecting only needed columns reduces data sent over the network and memory used, making the query faster, as seen in step 3 where execution time drops from 50ms to 30ms.

What does limiting results do for performance?

Limiting results reduces the number of rows fetched, which speeds up response time and reduces load, shown in step 4 where time drops to 10ms.

Visual Quiz - 3 Questions

Look at the execution_table, what is the execution time after adding an index on the age column?

A 500ms

B 50ms

C 30ms

D 10ms

💡 Hint

Check the 'Execution Time' column at step 2 in the execution_table.

At which step does the query start selecting only specific columns instead of all columns?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Query Sent' column in the execution_table to see when SELECT changes.

If we remove the LIMIT clause in step 4, what would likely happen to execution time?

A It would increase

B It would decrease

C It would stay the same

D It would become zero

💡 Hint

Refer to the execution_table row for step 4 and think about how LIMIT affects result size.

Concept Snapshot

Database query optimization in Flask:
- Write query with SQLAlchemy
- Add indexes to speed up searches
- Select only needed columns
- Use LIMIT for pagination
- Repeat optimization until query is fast
- Return results to Flask app

Full Transcript

This visual trace shows how a Flask app sends a database query and improves it step-by-step. Initially, the query selects all users older than 20, taking 500ms. Adding an index on the age column reduces time to 50ms by helping the database find rows faster. Selecting only needed columns reduces data size and time to 30ms. Adding a LIMIT clause fetches fewer rows, speeding up to 10ms. Optimization stops when the query runs fast enough. Variables like execution time and query text change at each step. Key moments explain why indexes, column selection, and limits improve speed. Quiz questions check understanding of these steps.

Practice

1. What is the main benefit of using query filters in Flask SQLAlchemy?

easy

A. To increase the number of database connections

B. To slow down the query execution

C. To make the database schema more complex

D. To reduce the amount of data retrieved from the database

Solution

1. Step 1: Understand query filters

   Query filters limit the rows returned by a database query based on conditions.

2. Step 2: Effect on data retrieval

   By filtering, only relevant data is fetched, reducing load and improving speed.

3. Final Answer:

   To reduce the amount of data retrieved from the database -> Option D

4. Quick Check:

   Filters reduce data load = B [OK]

Hint: Filters limit data fetched, making queries faster [OK]

Common Mistakes:

• Thinking filters increase data size
• Confusing filters with database schema changes
• Assuming filters slow down queries

2. Which of the following is the correct syntax to limit query results to 10 records in Flask SQLAlchemy?

easy

A. User.limit(10).query.all()

B. User.query.all().limit(10)

C. User.query.limit(10).all()

D. User.query.filter(10).all()

Solution

1. Step 1: Identify correct method chaining

   In Flask SQLAlchemy, limit() is called on the query object before all().

2. Step 2: Check each option

   User.query.limit(10).all() correctly calls limit(10) before all(). Others misuse method order or methods.

3. Final Answer:

   User.query.limit(10).all() -> Option C

4. Quick Check:

   Limit before all() = C [OK]

Hint: Use limit() before all() to restrict records [OK]

Common Mistakes:

• Calling limit() after all()
• Using filter() instead of limit()
• Incorrect method chaining order

3. Given the code:

users = User.query.filter(User.age > 30).limit(5).all()

What does this query return?

medium

A. All users older than 30

B. First 5 users older than 30

C. 5 users regardless of age

D. Syntax error due to filter condition

Solution

1. Step 1: Analyze filter condition

   The filter selects users where age is greater than 30.

2. Step 2: Understand limit effect

   The limit(5) restricts the result to the first 5 matching users.

3. Final Answer:

   First 5 users older than 30 -> Option B

4. Quick Check:

   Filter + limit = first 5 matching users [OK]

Hint: Filter narrows data, limit caps number returned [OK]

Common Mistakes:

• Ignoring the limit and thinking all filtered users return
• Confusing filter syntax with bitwise operator
• Assuming limit applies before filter

4. What is wrong with this Flask SQLAlchemy query?

users = User.query.filter(User.name == 'Alice').all().limit(3)

medium

A. Calling limit() after all() causes an error

B. Filter condition syntax is incorrect

C. Missing parentheses in filter method

D. No error, query is correct

Solution

1. Step 1: Understand method chaining order

   In Flask SQLAlchemy, limit() must be called before all() because all() executes the query and returns a list.

2. Step 2: Identify error cause

   Calling limit() on a list (result of all()) causes an AttributeError.

3. Final Answer:

   Calling limit() after all() causes an error -> Option A

4. Quick Check:

   limit() after all() = error [OK]

Hint: Call limit() before all() to avoid errors [OK]

Common Mistakes:

• Calling limit() after all()
• Thinking all() returns a query object
• Misplacing filter parentheses

5. You want to optimize a query to fetch only the usernames of active users, limiting results to 20. Which is the best approach?

hard

A. User.query.with_entities(User.username).filter(User.active == True).limit(20).all()

B. User.query.filter(User.active == True).all()[:20]

C. User.query.all().filter(User.active == True).limit(20)

D. User.query.filter(User.active == True).limit(20).all()

Solution

1. Step 1: Select only needed fields

   Using with_entities(User.username) fetches only usernames, reducing data load.

2. Step 2: Apply filter and limit correctly

   Filter active users and limit results to 20 before executing with all().

3. Step 3: Evaluate other options

   User.query.filter(User.active == True).limit(20).all() fetches full user objects, not optimized. User.query.all().filter(User.active == True).limit(20) calls all() before filter, causing error. User.query.filter(User.active == True).all()[:20] fetches all then slices in Python, inefficient.

4. Final Answer:

   User.query.with_entities(User.username).filter(User.active == True).limit(20).all() -> Option A

5. Quick Check:

   Select fields + filter + limit = D [OK]

Hint: Use with_entities() to select fields, then filter and limit [OK]

Common Mistakes:

• Fetching full objects instead of specific fields
• Calling all() before filter or limit
• Slicing results in Python instead of limiting in query