How To: Create Priority Matrix for Database Optimization
Learn how to generate a data-driven optimization matrix that ranks database issues by impact, risk, and effort.
1
2
3
The Prompt
Paste this into Warp’s AI input:
# **Comprehensive Database Query Analysis and Optimization Guide**
This guide provides a structured, repeatable approach for analyzing, profiling, and optimizing database performance within your application.
---
## **PHASE 1: Query Discovery & Cataloging**
### **Step 1 — Identify All Queries**
Scan the entire codebase and locate every SQL or ORM-based query. This includes:
- All **raw SQL queries**, including stored procedures
- **ORM-generated queries** (capture the actual SQL being produced)
- **Dynamic query builders** and their permutations
- **Background job queries** that may run at scale
- **Admin or reporting queries** that could lock tables
### **Step 2 — Document Key Details**
For each query discovered, record the following:
- **File location and function name**
- **Frequency of execution** (per request, batch job, or cron schedule)
- **Typical data volume processed**
---
## **PHASE 2: Performance Analysis**
For each identified query, generate and analyze a detailed **execution plan**.
### **Execution Plan Commands**
```sql
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON);
-- MySQL
EXPLAIN FORMAT=JSON;
```
### **Extract the Following Metrics**
- Total execution time
- Rows examined vs. rows returned ratio
- Index usage (full table scans, index scans, seeks)
- Join methods (nested loop, hash, merge)
- Memory usage and temporary file creation
- Buffer pool hit ratio
---
## **PHASE 3: Identify Specific Problems**
### **1. N+1 Query Detection**
**Problem:** Loading users and their posts separately
**Found in:** `/api/users/controller.js:45`
**Impact:** 100 queries for 100 users instead of one batched query
#### **Current Implementation**
```js
const users = await db.query('SELECT * FROM users');
for (const user of users) {
user.posts = await db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]);
}
```
#### **Optimized Version**
```js
const usersWithPosts = await db.query(`
SELECT u.*,
COALESCE(json_agg(p.*) FILTER (WHERE p.id IS NOT NULL), '[]') AS posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;
`);
```
---
### **2. Missing Index Analysis**
**Finding:** Full table scan on `orders` table (2M rows)
**Query:**
```sql
SELECT * FROM orders WHERE status = 'pending' AND created_at > ?;
```
**Recommendation:**
```sql
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
```
**Impact:** Query time reduced from **3.2s → 0.045s**
---
### **3. Inefficient JOIN Patterns**
**Problem:** Queries join through unnecessary intermediate tables.
**Solution:** Simplify relationships using direct joins or indexed subqueries where possible.
---
### **4. Subquery Optimization**
**Inefficient Query:**
```sql
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id);
```
**Optimized (Using Window Function):**
```sql
WITH product_stats AS (
SELECT *,
AVG(price) OVER (PARTITION BY category_id) AS avg_category_price
FROM products
)
SELECT * FROM product_stats WHERE price > avg_category_price;
```
---
## **PHASE 4: Advanced Optimizations**
### **Caching Strategies**
Use caching for:
- User-specific data with low update frequency
- Expensive aggregations that can be pre-computed
#### **Implementation**
```js
// Add caching layer with TTL
const getCachedOrQuery = async (key, query, ttl = 3600) => {
const cached = await redis.get(key);
if (cached) return JSON.parse(cached);
const result = await db.query(query);
await redis.setex(key, ttl, JSON.stringify(result));
return result;
};
```
---
### **Recommended Connection Configuration**
```json
{
"connectionLimit": 50,
"queueLimit": 100,
"acquireTimeout": 30000,
"waitForConnections": true,
"idleTimeout": 300000,
"enableKeepAlive": true,
"keepAliveInitialDelay": 10
}
```
---
### **Batch Operation Optimization**
**Problem:** Records are inserted one by one
**Found in:** `/jobs/import-data.js`
**Current Implementation:**
1000 individual `INSERT` statements
**Optimized:**
```sql
INSERT INTO users (name, email, created_at) VALUES
($1, $2, $3),
($4, $5, $6),
... -- batch in groups of 1000
```
---
### **Pagination Optimization**
```sql
SELECT * FROM posts
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;
```
---
## **PHASE 5: Monitoring & Maintenance**
### **1. Slow Query Logging Setup**
#### **PostgreSQL**
```sql
ALTER SYSTEM SET log_min_duration_statement = '1000'; -- Log queries over 1s
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_duration = on;
```
#### **MySQL**
```sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'TABLE';
```
---
### **2. Query Performance Testing**
```js
describe('Query Performance', () => {
test('User listing should complete under 100ms', async () => {
const start = Date.now();
await db.query('SELECT * FROM users LIMIT 1000');
expect(Date.now() - start).toBeLessThan(100);
});
});
```
---
## **PHASE 6: Deliverables**
### **Deliverable Outputs**
- **Optimization Script:** A single SQL file with all index creations, ordered by performance impact.
- **Code Changes PR:** Includes all query optimizations with before/after comparison results.
### **Performance Report**
- Baseline metrics vs. optimized metrics
- Expected resource savings (CPU, memory, I/O)
- Risk assessment for each change
### **Monitoring Dashboard**
Define recurring queries to track query performance over time.
---
## **PRIORITY MATRIX**
Rank each optimization by:
- **Impact:** Query frequency × time saved
- **Risk:** Low / Medium / High
- **Effort:** Quick fix / Moderate / Complex refactor
> Focus on **high-impact**, **low-risk**, **low-effort** items first.
---
### **Summary**
This workflow ensures the AI:
1. Systematically identifies all queries
2. Analyzes them using database-specific profiling tools
3. Provides **actionable, tested solutions**
4. Considers the full application context
5. Delivers **implementation-ready optimizations**
Warp will locate all SQL usage, test each query, and score them using explain-plan data.
A well-tuned database is the heart of your web stack — don’t skip it.
Last updated
Was this helpful?