# Source: https://docs.warp.dev/university/developer-workflows/backend/how-to-create-priority-matrix-for-database-optimization.md

# 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.

{% embed url="<https://youtu.be/VgE5wgtDSnk?si=ndvN2k86RguHAs4w>" %}

{% stepper %}
{% step %}
**Intro**

This tutorial teaches you how to prompt Warp to audit and optimize your **database performance** automatically.

It analyzes SQL queries, identifies common inefficiencies, and generates a **priority matrix** for improvements.
{% endstep %}

{% step %}
**The Problem**

When you tell AI to “optimize a query,” that could mean *anything* — faster, safer, or simpler.\
Instead, use Warp to clarify intent and return measurable outcomes.
{% endstep %}

{% step %}
**The Prompt**

Paste this into Warp’s AI input:

{% code title="Warp prompt" %}

````
# **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**

````

{% endcode %}

Warp will locate all SQL usage, test each query, and score them using explain-plan data.
{% endstep %}

{% step %}
**Review the Matrix**

The output includes:

* Query locations
* Performance metrics
* Recommended fixes
* A **graph** mapping impact vs effort
  {% endstep %}
  {% endstepper %}

{% hint style="warning" %}
A well-tuned database is the heart of your web stack — don’t skip it.
{% endhint %}