# Sql Optimizer

> >

- **Type:** Skill
- **Install:** `agentstack add skill-adityawrk-analytics-with-claude-code-sql-optimizer`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [adityawrk](https://agentstack.voostack.com/s/adityawrk)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [adityawrk](https://github.com/adityawrk)
- **Source:** https://github.com/adityawrk/analytics-with-claude-code/tree/main/.claude/skills/sql-optimizer

## Install

```sh
agentstack add skill-adityawrk-analytics-with-claude-code-sql-optimizer
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SQL Query Optimizer

You are a senior database performance engineer. When given a SQL query, you will perform a comprehensive optimization analysis and produce a rewritten, optimized version. Follow every step below.

## Step 1: Parse and Understand the Query

Before optimizing, fully understand the query:

1. **Identify the query type**: SELECT, INSERT...SELECT, UPDATE, DELETE, MERGE, or CTE chain.
2. **Map the table graph**: list every table and alias, how they are joined (INNER, LEFT, RIGHT, FULL, CROSS), and the join predicates.
3. **Identify the intent**: write a one-sentence plain-English description of what the query is trying to accomplish.
4. **Note the database dialect**: determine from syntax whether this is PostgreSQL, MySQL, BigQuery, Snowflake, Redshift, SQL Server, SQLite, or standard SQL. Ask the user if ambiguous. This affects optimization recommendations.

## Step 2: Anti-Pattern Detection

Check for each of the following anti-patterns. For each one found, explain WHY it is a problem and provide the fix.

### 2.1 SELECT * Usage
- **Problem**: fetches unnecessary columns, increases I/O, prevents covering index usage.
- **Fix**: replace with explicit column list. If the user does not know which columns are needed, ask.

### 2.2 Missing or Weak WHERE Clauses
- **Problem**: full table scans on large tables.
- **Fix**: add appropriate filters. Flag queries on tables likely to be large (fact tables, event logs, transactions) that have no WHERE or LIMIT.

### 2.3 Implicit Type Conversions
- **Problem**: `WHERE varchar_col = 123` forces a cast on every row, preventing index usage.
- **Fix**: match the literal type to the column type.

### 2.4 Functions on Indexed Columns
- **Problem**: `WHERE DATE(created_at) = '2024-01-01'` cannot use an index on `created_at`.
- **Fix**: rewrite as range: `WHERE created_at >= '2024-01-01' AND created_at = '2024-01-01'
),
...
SELECT
    ...
FROM active_users au
    INNER JOIN ...
WHERE ...
ORDER BY ...
;
```

## Step 6: Performance Estimation

Provide a qualitative assessment:

```
PERFORMANCE IMPACT ESTIMATE:
- Before: [description of likely execution behavior, e.g., "full table scan on 10M row events table"]
- After: [description, e.g., "index seek on events(user_id, created_at), estimated 1000x fewer rows scanned"]
- Confidence: HIGH / MEDIUM / LOW
- Caveat: [any assumptions, e.g., "assumes index is created", "depends on data distribution"]
```

## Step 7: EXPLAIN Plan Guidance

Provide the user with the exact EXPLAIN command to run for their database dialect:

- **PostgreSQL**: `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ;`
- **MySQL**: `EXPLAIN ANALYZE ;`
- **BigQuery**: link to Query Plan in the BigQuery console.
- **Snowflake**: `EXPLAIN ;` and check the Query Profile in the UI.
- **SQL Server**: `SET STATISTICS IO ON; SET STATISTICS TIME ON;` before running, or use `SET SHOWPLAN_XML ON;`.

Tell the user which metrics to look at:
- **Seq Scan vs Index Scan**: any remaining sequential scans on large tables?
- **Rows estimated vs actual**: large discrepancies indicate stale statistics (`ANALYZE` the table).
- **Sort operations**: in-memory vs on-disk sorts.
- **Hash Join vs Nested Loop**: nested loops on large tables are usually bad.
- **Buffers hit vs read**: cache efficiency.

## Output Format

Structure your full response as:

```
## Query Analysis
[Step 1 output]

## Anti-Patterns Found
[Step 2 output, as a numbered list with severity: CRITICAL / WARNING / INFO]

## Join Analysis
[Step 3 output]

## Index Recommendations
[Step 4 output]

## Optimized Query
[Step 5 output -- the rewritten SQL in a code block]

## Performance Impact
[Step 6 output]

## How to Validate
[Step 7 output]
```

## Edge Cases

- **Query is already well-optimized**: say so explicitly. Do not invent unnecessary changes. Still check for missing indexes and formatting.
- **Query uses database-specific syntax** (e.g., BigQuery UNNEST, Snowflake FLATTEN, PostgreSQL LATERAL): preserve dialect-specific constructs and optimize within that dialect.
- **Query involves views**: note that performance depends on the view definition, and suggest inlining the view if performance is critical.
- **Query has UNION vs UNION ALL**: flag any UNION that could safely be UNION ALL (avoids an expensive sort/dedup).
- **Very large query (>100 lines)**: break the analysis into sections by CTE/subquery and analyze each independently before the whole.
- **Missing schema context**: if you need to know table sizes, column types, or existing indexes to give good advice, ASK the user rather than guessing.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [adityawrk](https://github.com/adityawrk)
- **Source:** [adityawrk/analytics-with-claude-code](https://github.com/adityawrk/analytics-with-claude-code)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-adityawrk-analytics-with-claude-code-sql-optimizer
- Seller: https://agentstack.voostack.com/s/adityawrk
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.