Database query optimization workflow
Identify slow queries, profile bottlenecks, and apply fixes.
Every application has slow queries — they're usually discovered during a user complaint, a scaling outage, or a performance audit. This template maps the systematic approach: identify the slow query, profile its execution plan, hunt for missing indexes or stale statistics, rewrite if necessary, and finally deploy the optimized version with confidence.
The key insight is the execution plan: it shows whether the database is doing a cheap index lookup or an expensive full table scan. From there, the path is clear — add an index, update statistics, or rewrite the logic. Guessing without the plan always wastes time.
When to use this template
- Performance triage — when a user reports slowness, walk through the steps to isolate the query and measure the problem before optimizing.
- Query review checklist — before deploying new analytics, complex reporting queries, or high-frequency endpoints, walk the team through this workflow to catch performance issues early.
- On-call runbook — when alerts fire for slow database operations, use this diagram as a checklist to avoid missing the obvious (index, statistics) while hunting for deeper issues.
How to adapt it
Customize for your database system and stack:
- Add database-specific steps — PostgreSQL:
ANALYZE,EXPLAIN VERBOSE, partial indexes. MySQL:ANALYZE TABLE, slow query log, innodb_stats. SQL Server: execution plan hints, filtered statistics. - Insert monitoring integration — after "Deploy optimized query", add a step to compare metrics before and after (query time, CPU, locks, throughput).
- Extend with team communication — add a step to document the optimization in your wiki so other engineers see the pattern and avoid the same bottleneck in new code.
Visual edits regenerate clean Mermaid code so you can adapt this to your database and workflow without syntax overhead.
Mermaid code
Copy it anywhere Mermaid is supported — GitHub, Notion, or your docs.
flowchart TD
A[Identify slow query] --> B[Enable query profiling]
B --> C[Run query with EXPLAIN]
C --> D[Analyze execution plan]
D --> E{Full table scan?}
E -->|Yes| F[Check if index exists]
F --> G{Index on join key?}
G -->|No| H[Create index]
G -->|Yes| I[Query plan still slow?]
E -->|No| I
H --> I
I -->|Yes| J{Missing statistics?}
J -->|Yes| K[Update table stats]
J -->|No| L[Review query logic]
K --> M[Re-run EXPLAIN]
L --> N{Rewrite with JOIN optimization?}
N -->|Yes| O[Test rewritten query]
N -->|No| P[Add result caching]
O --> Q[Verify performance gain]
M --> Q
P --> Q
Q --> R[Deploy optimized query]
Frequently asked questions
- What is database query optimization?
- It's the process of finding slow SQL queries and making them faster by analyzing execution plans, identifying missing indexes, updating statistics, and sometimes rewriting the query logic. A slow query wastes CPU, blocks other requests, and degrades user experience — optimization is often the highest-ROI performance work.
- Why start with EXPLAIN and the execution plan?
- EXPLAIN shows the database's strategy for running your query — whether it's doing a fast index lookup or a slow full table scan. The execution plan reveals bottlenecks before you guess. Without it, you're optimizing blind.
- How do I decide between adding an index and rewriting the query?
- Start with indexes — they're the fastest, lowest-risk wins. If the execution plan shows a full table scan on a filter column, add an index and re-test. Only rewrite the query if the index doesn't help or if the logic is fundamentally expensive (cartesian product, subquery in WHERE). Visual edits regenerate clean code.
- When should I use caching instead of optimizing the database?
- Caching (Redis, memcached) works best for queries that don't change often and are hit frequently — like product metadata or user profiles. Database optimization is better for high-cardinality queries that return different results (user-specific data, analytics). Often you need both: optimize the query, then cache the result.
Related templates
Database indexing strategy
Decision tree for choosing single-column, composite, and specialized indexes.
Auto-scaling decision tree
CPU, memory, request volume, and cost trade-off decisions.
Database backup and recovery process
Disaster recovery decision flow from incident to restore.