# Optimizing SQL

Optimize SQL query performance through EXPLAIN analysis, indexing strategies, and query...

- Repository: https://github.com/ancoleman/ai-design-components (526 stars)
- Category: Testing
- License: MIT
- Last updated: 2025-12-11
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add ancoleman/ai-design-components --skill optimizing-sql -g -a claude-code -y`
Codex: `npx skills add ancoleman/ai-design-components --skill optimizing-sql -g -a codex -y`
Cursor: `npx skills add ancoleman/ai-design-components --skill optimizing-sql -g -a cursor -y`
Gemini CLI: `npx skills add ancoleman/ai-design-components --skill optimizing-sql -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add ancoleman/ai-design-components`

## Description

Optimize SQL query performance through EXPLAIN analysis, indexing strategies, and query rewriting for PostgreSQL, MySQL, and SQL Server. Use when debugging slow queries, analyzing execution plans, or improving database performance.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# SQL Optimization

Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting.

## When to Use This Skill

Trigger this skill when encountering:
- Slow query performance or database timeouts
- Analyzing EXPLAIN plans or execution plans
- Determining index requirements
- Rewriting inefficient queries
- Identifying query anti-patterns (N+1, SELECT *, correlated subqueries)
- Database-specific optimization needs (PostgreSQL, MySQL, SQL Server)

## Core Optimization Workflow

### Step 1: Analyze Query Performance

Run execution plan analysis to identify bottlenecks:

**PostgreSQL:**
```sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
```

**MySQL:**
```sql
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;
```

**SQL Server:**
Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L)

**Key Metrics to Monitor:**
- **Cost**: Estimated resource consumption
- **Rows**: Number of rows processed (estimated vs actual)
- **Scan Type**: Sequential scan vs index scan
- **Execution Time**: Actual time spent on operation

For detailed execution plan interpretation, see `references/explain-guide.md`.

### Step 2: Identify Optimization Opportunities

**Common Red Flags:**

| Indicator | Problem | Solution |
|-----------|---------|----------|
| Seq Scan / Table Scan | Full table scan on large table | Add index on filter columns |
| High row count | Processing excessive rows | Add WHERE filter or index |
| Nested Loop with large outer table | Inefficient join algorithm | Index join columns |
| Correlated subquery | Subquery executes per row | Rewrite as JOIN or EXISTS |
| Sort operation on large result set | Expensive sorting | Add index matching ORDER BY |

For scan type interpretation, see `references/scan-types.md`.

### Step 3: Apply Indexing Strategies

**Index Decision Framework:**

The rest: https://github.com/ancoleman/ai-design-components/blob/HEAD/skills/optimizing-sql/SKILL.md