Skip to main content
skill CX Codex

sql-query-optimizer · patricio0312rev — Claude Code skill: SQL tuning, install

The sql-query-optimizer skill inspects SQL execution plans, suggests indexes, and rewrites queries. Use it when slow database queries need a concrete diagnosis.

MIT 297 installs ★ 64 updated 2026-01-12

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add patricio0312rev/skills --skill sql-query-optimizer -a claude-code
Open on GitHub

What sql-query-optimizer does

sql-query-optimizer works from EXPLAIN ANALYZE output. It points out sequential scans, expensive joins, and filters that keep an index from being used, then suggests targeted indexes. The examples cover composite and covering indexes as well as column selection and join order.

The skill also includes a TypeScript benchmarking pattern built around PrismaClient. It runs alternative queries repeatedly and reports average, minimum, and maximum timings, so an original query can be compared with a rewrite. Other examples cover EXISTS, functional indexes, and index column order.

The shown benchmark imports PrismaClient from @prisma/client; that package and a working Prisma connection are required to run the script.

What it helps with

  • inspect an EXPLAIN ANALYZE plan
  • spot sequential scans in a query plan
  • design an index for filters and sorting
  • rewrite a query to fetch fewer columns
  • benchmark two SQL query versions

Example prompts for the agent

  • Analyze query.sql with EXPLAIN ANALYZE and explain the main performance bottlenecks.
  • Suggest a composite index for orders filtered by user_id and status, sorted by created_at.
  • Compare the original and rewritten queries with a PrismaClient benchmark.

How to install sql-query-optimizer

1Skills CLI (Vercel)

npx skills add patricio0312rev/skills --skill sql-query-optimizer -a claude-code

2Manually: put the files in place

~/.claude/skills/sql-query-optimizer/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/sql-query-optimizer/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about sql-query-optimizer

How do I install sql-query-optimizer in Claude Code?

Run npx skills add patricio0312rev/skills --skill sql-query-optimizer -a claude-code, then check ~/.claude/skills/sql-query-optimizer/SKILL.md.

What does sql-query-optimizer do?

It examines EXPLAIN ANALYZE plans, identifies likely causes of slow SQL, recommends indexes, and proposes query rewrites. It also provides a way to benchmark competing versions.

What is required for the benchmark example?

The example uses PrismaClient from @prisma/client, so that package and a configured Prisma connection are needed to run the TypeScript benchmark.

Source text

SKILL.md · MIT
---
name: sql-query-optimizer
description: Analyzes and optimizes SQL queries using EXPLAIN plans, index recommendations, query rewrites, and performance benchmarking. Use for "query optimization", "slow queries", "database performance", or "EXPLAIN analysis".
---

# SQL Query Optimizer

Optimize SQL queries for maximum performance.

## EXPLAIN Analysis

```sql
-- Original slow query
EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- Output analysis:
/*
Sort  (cost=15234.32..15234.34 rows=10 width=120) (actual time=245.123..245.125 rows=10 loops=1)
  Sort Key: (count(o.id)) DESC
  ->  HashAggregate  (cost=15000.00..15100.00 rows=1000 width=120) (actual time=244.891..245.023 rows=1000 loops=1)
        Group Key: u.id
        ->  Hash Left Join  (cost=1234.56..14500.00 rows=50000 width=112) (actual time=12.345..230.456 rows=50000 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..10000.00 rows=100000 width=8) (actual time=0.012..180.234 rows=100000 loops=1)
              ->  Hash  (cost=1000.00..1000.00 rows=5000 width=112) (actual time=10.234..10.234 rows=5000 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 456kB
                    ->  Seq Scan on users u  (cost=0.00..1000.00 rows=5000 width=112) (actual time=0.008..5.123 rows=5000 loops=1)
                          Filter: (created_at > '2024-01-01'::date)
                          Rows Removed by Filter: 1000
Planning Time: 0.234 ms
Execution Time: 245.234 ms
*/

-- Issues identified:
-- 1. Seq Scan on orders (no index on user_id)
-- 2. Seq Scan on users (no index on created_at)
-- 3. Full table scans expensive
```

## Index Recommendations

```sql
-- Problem: Sequential scans
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;
/*
Seq Scan on orders  (cost=0.00..10000.00 rows=50 width=100) (actual time=0.012..89.456 rows=50 loops=1)
  Filter: (user_id = 123)
  Rows Removed by Filter: 99950
*/

-- Solution: Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- After index:
/*
Index Scan using idx_orders_user_id on orders  (cost=0.29..45.32 rows=50 width=100) (actual time=0.023..0.089 rows=50 loops=1)
  Index Cond: (user_id = 123)
*/

-- Performance: 89ms → 0.09ms (990x faster!)
```

## Query Rewrites

### 1. Avoid SELECT \*

```sql
-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = 123;

-- ✅ Good: Fetch only needed columns
SELECT id, email, name FROM users WHERE id = 123;

-- Performance: 50% faster, less network transfer
```

### 2. Use EXISTS Instead of IN

```sql
-- ❌ Slow: Subquery executed fully
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- ✅ Fast: Short-circuits on first match
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- Performance: 3x faster on large datasets
```

### 3. Avoid Functions on Indexed Columns

```sql
-- ❌ Bad: Index not used
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- ✅ Good: Index scan possible
SELECT * FROM users WHERE email = '[email protected]';

-- Or create functional index:
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
```

### 4. Use Covering Indexes

```sql
-- Query needs: id, email, name
SELECT id, email, name FROM users WHERE email = '[email protected]';

-- Create covering index (includes all needed columns)
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (id, name);

-- Result: Index-only scan (no table access needed)
```

### 5. Optimize JOIN Order

```sql
-- ❌ Bad: Large table first
SELECT * FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = '[email protected]';

-- ✅ Good: Filter first, join second
SELECT * FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.email = '[email protected]';

-- Or use CTE for clarity:
WITH filtered_users AS (
  SELECT id FROM users WHERE email = '[email protected]'
)
SELECT o.* FROM orders o
JOIN filtered_users u ON u.id = o.user_id;
```

## Composite Indexes

```sql
-- Query pattern: WHERE user_id = X AND status = 'active' ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

-- Index column order matters!
-- Rule: Equality filters → Range filters → Sort columns

-- Example queries that use this index:
-- 1. SELECT * FROM orders WHERE user_id = 123;  ✅
-- 2. SELECT * FROM orders WHERE user_id = 123 AND status = 'active';  ✅
-- 3. SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;  ✅
-- 4. SELECT * FROM orders WHERE status = 'active';  ❌ (doesn't start with user_id)
```

## Query Performance Benchmarking

```typescript
// scripts/benchmark-queries.ts
import { PrismaClient } from "@prisma/client";
import { performance } from "perf_hooks";

const prisma = new PrismaClient();

async function benchmarkQuery(
  name: string,
  query: () => Promise<any>,
  iterations: number = 10
) {
  const times: number[] = [];

  for (let i = 0; i < iterations; i++) {
    const start = performance.now();
    await query();
    const end = performance.now();
    times.push(end - start);
  }

  const avg = times.reduce((a, b) => a + b, 0) / times.length;
  const min = Math.min(...times);
  const max = Math.max(...times);

  console.log(`\n${name}:`);
  console.log(`  Avg: ${avg.toFixed(2)}ms`);
  console.log(`  Min: ${min.toFixed(2)}ms`);
  console.log(`  Max: ${max.toFixed(2)}ms`);

  return { avg, min, max };
}

// Compare queries
async function compareQueries() {
  console.log("🔍 Benchmarking queries...\n");

  // Query 1: Original
  const result1 = await benchmarkQuery("Original Query", async () => {
    return prisma.$queryRaw`
      SELECT u.*, COUNT(o.id) as order_count
      FROM users u
      LEFT JOIN orders o ON o.user_id = u.id
      GROUP BY u.id
      LIMIT 10
    `;
  });

  // Query 2: Optimized
  const result2 = await benchmarkQuery("Optimized Query", async () => {
    return prisma.$queryRaw`
      SELECT u.id, u.email, u.name,
             (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
      FROM users u
      LIMIT 10
    `;
  });

  // Comparison
  const improvement = (
    ((result1.avg - result2.avg) / result1.avg) *
    100
  ).toFixed(1);
  console.log(`\n📊 Improvement: ${improvement}% faster`);
}

compareQueries();
```

## Query Optimization Checklist

```typescript
interface QueryOptimization {
  query: string;
  issues: string[];
  recommendations: string[];
  estimatedImprovement: string;
}

const optimizations: QueryOptimization[] = [
  {
    query: "SELECT * FROM orders WHERE user_id = $1",
    issues: [
      "Missing index on user_id",
      "SELECT * fetches unnecessary columns",
    ],
    recommendations: [
      "CREATE INDEX idx_orders_user_id ON orders(user_id)",
      "SELECT id, total, status instead of *",
    ],
    estimatedImprovement: "90% faster",
  },
  {
    query: "SELECT COUNT(*) FROM orders",
    issues: ["Full table scan", "No WHERE clause filtering"],
    recommendations: [
      "Add WHERE clause to filter rows",
      "Consider approximate count for large tables",
    ],
    estimatedImprovement: "70% faster",
  },
];
```

## Automated Slow Query Detection

```typescript
// scripts/detect-slow-queries.ts
async function detectSlowQueries() {
  // Enable slow query logging in PostgreSQL
  await prisma.$executeRaw`
    ALTER DATABASE mydb SET log_min_duration_statement = 100;
  `;

  // Query pg_stat_statements for slow queries
  const slowQueries = await prisma.$queryRaw<any[]>`
    SELECT
      query,
      calls,
      total_exec_time / 1000 as total_time_seconds,
      mean_exec_time / 1000 as mean_time_ms,
      max_exec_time / 1000 as max_time_ms
    FROM pg_stat_statements
    WHERE mean_exec_time > 100  -- > 100ms
    ORDER BY mean_exec_time DESC
    LIMIT 20
  `;

  console.log("🐌 Slow Queries Detected:\n");
  slowQueries.forEach((q, i) => {
    console.log(`${i + 1}. ${q.query.substring(0, 80)}...`);
    console.log(`   Calls: ${q.calls}`);
    console.log(`   Avg: ${q.mean_time_ms.toFixed(2)}ms`);
    console.log(`   Max: ${q.max_time_ms.toFixed(2)}ms\n`);
  });
}
```

## Best Practices

1. **Always use EXPLAIN**: Understand query plans
2. **Index foreign keys**: Essential for joins
3. **Avoid SELECT \***: Fetch only needed columns
4. **Use composite indexes**: Multi-column queries
5. **Consider covering indexes**: Eliminate table access
6. **Batch operations**: Reduce round trips
7. **Monitor regularly**: Track slow queries

## Output Checklist

- [ ] EXPLAIN plan analyzed
- [ ] Missing indexes identified
- [ ] Query rewrite suggestions
- [ ] Performance benchmarks
- [ ] Before/after metrics
- [ ] Index creation scripts
- [ ] Slow query monitoring
- [ ] Optimization priority list

© patricio0312rev · MIT · patricio0312rev/skills

Similar skills

All skills for Data and databases →

sql-query-optimizer by other authors comes first.

sql-query-optimizer Jeremy Longshore Walks through SQL query optimization and writes the code for you. It turns on by itself when your prompt mentions sql query optimizer, sql optimizer, or just sql. CX Codex +1 1installs sql-query-optimization aj-geddes sql-query-optimization is a Claude Code skill that analyzes slow SQL queries and suggests faster rewrites for PostgreSQL and MySQL. CX Codex +1 529installs mongodb-query-optimizer mongodb mongodb-query-optimizer — a skill for analyzing slow MongoDB queries and recommending indexes. Works on Claude Code, Codex, Cursor, GitHub Copilot and more. Activates when you ask why a query is slow or how to speed it up. CX Codex +1 7,700installs database-query-optimization aj-geddes database-query-optimization — a skill that walks your agent through slow SQL: execution plans, indexes, and where the time actually goes. Useful when the database drags. CX Codex +1 594installs sql-pro https://github.com/Jeffallan sql-pro optimizes slow SQL queries, designs database schemas, and fixes performance bottlenecks. Built for EXPLAIN plan tuning and cross-dialect migrations. CX Codex +1 6,900installs sql-best-practices Mindrally sql-best-practices is a Claude Code skill with guidelines for SQL formatting, indexing, explicit joins, and injection defenses in database workflows. CX Codex +1 845installs database-optimizer davila7 database-optimizer — a Claude skill that digs into database bottlenecks: execution plans, indexes, N+1, caching and partitioning. Useful when queries are slow and the reason isn't obvious. CX Codex +1 286installs

From the same set148

The whole set →
data-integrity-auditor data-integrity-auditor is a Claude Code skill that finds database integrity problems and suggests fixes. It responds to requests about orphaned rows, broken relationships, and data quality. CX Codex +1 306installs queue-job-processor queue-job-processor sets up background job processing with BullMQ and Redis, covering typed queues, worker concurrency, and retry strategies. CX Codex +1 300installs api-mock-server api-mock-server configures mock API servers for testing and frontend development using MSW, json-server, or custom handlers. CX Codex +1 300installs guardrails-safety-filter-builder guardrails-safety-filter-builder builds a safety layer for an LLM app: masks personal data, catches prompt injection attempts and returns a refusal when a topic is off-policy. CX Codex +1 299installs dependency-vulnerability-triage dependency-vulnerability-triage is a Claude Code skill that turns npm audit and Snyk results into a prioritized patch plan with severity ratings, safe upgrade paths, and breaking-change analysis. CX Codex +1 296installs rfc-generator rfc-generator turns a technical proposal into a structured RFC: problem, solution, alternatives, risks and a rollout plan, instead of a blank page. CX Codex +1 294installs backup-restore-runbook-generator The backup-restore-runbook-generator skill builds database disaster-recovery runbooks with backup scripts, restore steps, and validation checks. Use it when planning recovery procedures. CX Codex +1 294installs cors-configuration cors-configuration sets up CORS for an API: allowed origins, response headers, preflight handling and credentials, with worked Express and Next.js examples. CX Codex +1 294installs

Project sponsors

Companies that keep this analytics open