MySQL Optimization Techniques Every Python Backend Engineer Should Know

The 400ms Query That Shouldn't Exist

A credential listing endpoint was taking 400ms at CertifyMe. We had proper indexes. We had query caching. We even had a Redis layer on top. The Redis cache was cold 30% of the time, and 400ms × 30% × 10,000 daily requests = a lot of unnecessary latency.

Running EXPLAIN on the query revealed the problem instantly: a "Using filesort" and "Using temporary" in the Extra column. The ORDER BY clause was sorting on a column that wasn't in our composite index. One index change: 400ms → 12ms. That's the power and frustration of MySQL optimization — the fix is often trivial once you find the problem.

Always Start with EXPLAIN

Before optimizing anything, run EXPLAIN. It tells you exactly how MySQL plans to execute your query — which indexes it uses, how many rows it estimates scanning, and what expensive operations it performs:

-- Your slow query
EXPLAIN SELECT c.id, c.title, c.issued_at, u.email
FROM credentials c
JOIN users u ON c.user_id = u.id
WHERE c.org_id = 42
  AND c.status = 'active'
ORDER BY c.issued_at DESC
LIMIT 20;

-- Key columns to look for in EXPLAIN output:
-- type: ALL = full table scan (bad), ref/range/index = good
-- key: which index is being used (NULL = no index = problem)
-- rows: estimated rows scanned (lower is better)
-- Extra: "Using filesort" and "Using temporary" are red flags

Composite Index Strategy

Single-column indexes are a starting point. Real performance comes from composite indexes designed around your actual query patterns. The rule: columns in your WHERE clause first, then ORDER BY columns, following selectivity order (most selective first):

-- For the query: WHERE org_id = ? AND status = ? ORDER BY issued_at DESC
-- BAD: separate indexes (MySQL can only use one per query)
CREATE INDEX idx_org ON credentials(org_id);
CREATE INDEX idx_status ON credentials(status);

-- GOOD: composite index matching the query pattern
-- org_id first (equality filter), status second, issued_at last (for ORDER BY)
CREATE INDEX idx_org_status_issued ON credentials(org_id, status, issued_at);

-- MySQL can now satisfy the entire query from the index
-- without touching the table data — an "index-only scan"

The key insight: a composite index (a, b, c) can be used for queries filtering on a, a, b, or a, b, c — but not b alone or b, c. Design indexes around your most common query patterns, not around individual columns.

The N+1 Query Problem

N+1 is the silent killer of Python backend performance. It happens when you fetch N records and then issue N additional queries to fetch related data — one per record. It looks like this in SQLAlchemy:

# N+1 anti-pattern
# 1 query to get credentials
credentials = db.session.query(Credential).filter_by(org_id=42).all()
for cred in credentials:
    # N queries — one per credential!
    print(cred.user.email)

# Fix: eager loading with joinedload
from sqlalchemy.orm import joinedload

credentials = (
    db.session.query(Credential)
    .options(joinedload(Credential.user))  # single JOIN query
    .filter_by(org_id=42)
    .all()
)
# Now accessing cred.user.email generates zero additional queries

To detect N+1 in your Flask app, log all SQL queries in development:

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Pagination: Do It Right

OFFSET-based pagination is a trap. SELECT ... LIMIT 20 OFFSET 10000 makes MySQL scan and discard 10,000 rows before returning your 20. The further into the dataset you paginate, the slower it gets.

-- Offset pagination — gets slower as offset increases
SELECT * FROM credentials ORDER BY id DESC LIMIT 20 OFFSET 10000;

-- Keyset / cursor pagination — always O(log n) regardless of page
-- "Give me the next 20 records after id = 9980"
SELECT * FROM credentials
WHERE id < 9980
ORDER BY id DESC
LIMIT 20;

For public-facing APIs with infinite scroll or "load more" patterns, keyset pagination is always the right choice. OFFSET pagination is only acceptable for admin interfaces with limited page depth (under 100 pages).

Covering Indexes for Analytics Queries

A covering index contains all the columns a query needs — so MySQL can answer the query entirely from the index without reading the main table at all. This is the fastest possible read path:

-- This query needs: org_id (filter), issued_at (order), id (select)
SELECT id, issued_at FROM credentials
WHERE org_id = 42
ORDER BY issued_at DESC
LIMIT 100;

-- A covering index on (org_id, issued_at, id) means:
-- 1. MySQL navigates the B-tree using org_id
-- 2. Reads issued_at from the index for ordering
-- 3. Returns id directly from the index
-- No table data access at all — pure index scan
CREATE INDEX idx_covering ON credentials(org_id, issued_at, id);