Database query optimization in Flask

Introduction

Database query optimization helps your app get data faster. It makes your website or app feel quick and smooth.

When your app loads data slowly from the database.

When you want to reduce the load on your database server.

When you notice your app uses too much memory or CPU during data fetching.

When you want to improve user experience by showing results faster.

When your database queries return too much unnecessary data.

Syntax

Flask

from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.orm import load_only

db = SQLAlchemy()

# Example of optimized query using filter and limit
result = db.session.query(User).filter(User.active == True).limit(10).all()

Use filters to get only the data you need, like filtering active users.

Limit the number of results to avoid loading too much data at once.

Examples

Get all users who are active. This avoids loading inactive users.

Flask

users = User.query.filter_by(active=True).all()

Get the 5 most recently created users. This limits data and sorts it.

Flask

users = User.query.order_by(User.created_at.desc()).limit(5).all()

Load only the 'id' and 'name' fields to reduce data size.

Flask

from sqlalchemy.orm import load_only
users = User.query.options(load_only('id', 'name')).all()

Sample Program

This Flask app creates a simple User table in memory. It adds some users with active or inactive status. Then it runs an optimized query to get only active users and prints their names.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    active = db.Column(db.Boolean, default=True)

with app.app_context():
    db.create_all()
    # Add sample users
    db.session.add_all([
        User(name='Alice', active=True),
        User(name='Bob', active=False),
        User(name='Carol', active=True),
        User(name='Dave', active=True),
        User(name='Eve', active=False)
    ])
    db.session.commit()

    # Optimized query: get only active users
    active_users = User.query.filter_by(active=True).all()
    for user in active_users:
        print(f"User: {user.name}, Active: {user.active}")

Important Notes

Always filter your queries to get only needed data.

Use query options like load_only to reduce data size.

Limit results when you only need a few records.

Summary

Optimizing queries makes your app faster and lighter.

Use filters, limits, and field selection to reduce data load.

Test queries to see how much data they return and improve them.