Querying with filter and filter_by in Flask - Performance & Optimization

Performance: Querying with filter and filter_by

MEDIUM IMPACT

This affects how quickly the database returns filtered data and how much processing the server does before sending results.

Filtering database records in Flask SQLAlchemy

Flask

User.query.filter(User.name.like('john%')).all()

Starts with fixed string, allowing index use and faster query execution.

📈 Performance GainReduces query time by 80%, improving LCP

Filtering database records in Flask SQLAlchemy

Flask

User.query.filter(User.name.like('%john%')).all()

Using a wildcard at the start of LIKE disables index use, causing full table scans.

📉 Performance CostBlocks rendering for 100+ ms on large tables due to slow query

Performance Comparison

Pattern	DOM Operations	Reflows	Paint Cost	Verdict
filter with leading wildcard	N/A	N/A	Delays initial paint due to slow data	[X] Bad
filter with indexed prefix	N/A	N/A	Faster data arrival improves paint	[OK] Good
filter for equality	N/A	N/A	Slightly slower server processing	[!] OK
filter_by for equality	N/A	N/A	Optimized server processing	[OK] Good

Rendering Pipeline

The database query runs before the browser rendering starts. Slow queries delay data arrival, increasing LCP. Efficient filters reduce server processing time, speeding up data delivery and page paint.

→Server Processing

→Network Transfer

→Browser Rendering

⚠️ BottleneckServer Processing (slow database queries)

Core Web Vital Affected

LCP

This affects how quickly the database returns filtered data and how much processing the server does before sending results.

Optimization Tips

1Avoid leading wildcards in LIKE filters to enable index use.

2Use filter_by for simple equality filters to reduce server processing time.

3Index columns used in filters to speed up database queries and improve LCP.

Performance Quiz - 3 Questions

Which filter pattern is faster for simple equality in Flask SQLAlchemy?

A filter(User.age == 30)

B filter_by(age=30)

C filter(User.age.like('%30%'))

D filter(User.age != 30)

DevTools: Network and Performance panels

How to check: 1. Open DevTools and go to Network tab. 2. Reload page and find API/database request. 3. Check request duration and response size. 4. Use Performance tab to see when main content paints.

What to look for: Look for long server response times delaying Largest Contentful Paint (LCP). Faster queries show shorter wait times and quicker paint.