---
name: sql-query-optimizer
description: "Rewrites a slow SQL query and recommends indexes, with the reasoning behind each change and how the execution plan should differ afterward. Use when a developer has a slow SQL query and wants a faster rewrite plus index suggestions for their specific database engine. Do not use for choosing a join type or designing a schema from scratch. Related topics: sql, database, performance."
---

# SQL Query Optimizer

Rewrites a slow SQL query and recommends indexes, with the reasoning behind each change and how the execution plan should differ afterward.

## When to use

Use this skill when a developer has a slow SQL query and wants a faster rewrite plus index suggestions for their specific database engine. Do not use it for choosing a join type or designing a schema from scratch.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Scan the query for filters that defeat an index, correlated subqueries and over-wide selects.
3. Propose the minimum set of indexes with exact CREATE INDEX statements and column order.
4. Rewrite the query while preserving results, flagging any change in NULL or duplicate behaviour.
5. Describe the expected plan change and the write cost of each new index.

## Inputs

- **Database engine** (choose one: PostgreSQL, MySQL, SQL Server, SQLite, Oracle)
- **Query type** (choose one: SELECT, INSERT, UPDATE, DELETE, Complex JOIN)
- **The slow query** (longer free text)
- **Approximate rows in the main table** (number from 1000 to 100000000)
- **Tables and current indexes** (longer free text)
- **Suspected bottlenecks** (choose any: Missing indexes, Subqueries, JOIN order, N+1 pattern)
- **Explain the execution plan changes** (choose one: Yes, No)

## Output

The optimized query, CREATE INDEX statements, and a numbered list of changes with reasons, plus an execution plan comparison when requested.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### SQL Query Tuning

Make this query faster without changing its results.

- Database engine: {{Database engine}}
- Query type: {{Query type}}
- The slow query: {{The slow query}}
- Approximate rows in the main table: {{Approximate rows in the main table}}
- Tables and current indexes: {{Tables and current indexes}}
- Suspected bottlenecks: {{Suspected bottlenecks}}
- Explain the execution plan changes: {{Explain the execution plan changes}}

Read the query for the usual causes in this order: filters that cannot use an index (functions on indexed columns, leading wildcards), missing or mis-ordered composite indexes, correlated subqueries that can become joins, and selecting more columns or rows than needed. Estimate the effect of each change in terms of rows scanned given the table size. Keep results identical, and say so when a rewrite could change NULL handling or duplicate rows.

Return the optimized query, the exact CREATE INDEX statements, and a numbered list of changes with the reason each helps. If requested, describe how the plan should change, for example from a sequential scan to an index seek. Mention the write cost of every new index.

## Pitfalls

- Adding an index for every column, which slows writes and bloats storage.
- Wrapping an indexed column in a function so the index is ignored.
- Rewriting a subquery as a join in a way that duplicates rows.
