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

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

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.

4. Review the Matrix

   The output includes:

   • Query locations
   • Performance metrics
   • Recommended fixes
   • A graph mapping impact vs effort

Caution

A well-tuned database is the heart of your web stack — don’t skip it.