---
name: sql-database-query-analyzer
description: "A performance diagnosis of one SQL query: the expected execution plan, where the time goes, which indexes would change it, and a rewrite path toward your target time, for the engine you run. Use when a SQL query is slow and the developer or DBA needs to know where the time goes, what the plan looks like, and which index or rewrite fixes it. Do not use for writing a new query from a description, or schema design for a new application. Related topics: coding, sql."
---

# SQL Database Query Analyzer

A performance diagnosis of one SQL query: the expected execution plan, where the time goes, which indexes would change it, and a rewrite path toward your target time, for the engine you run.

## When to use

Use this skill when a SQL query is slow and the developer or DBA needs to know where the time goes, what the plan looks like, and which index or rewrite fixes it. Do not use it for writing a new query from a description, or schema design for a new application.

## 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. Give the engine-specific command to capture a real plan, then predict the plan from the query and the table sizes supplied.
3. Identify the dominant operations: large scans with selective predicates, functions on indexed columns, implicit casts, OR conditions, unsupported sorts, correlated subqueries.
4. When enabled, write each index recommendation as a CREATE INDEX statement with the column order justified and the write cost noted.
5. Propose rewrites that change the plan, not the formatting, and show the before and after for each.
6. State whether the target time is reachable and which single change moves the needle most.

## Inputs

- **SQL query** (longer free text)
- **Database engine** (choose one: MySQL, PostgreSQL, SQL Server, Oracle, SQLite)
- **Explain level** (choose one: Basic, Detailed, Full)
- **Index recommendations** (choose one: Yes, No)
- **Target execution time (milliseconds)** (number from 1 to 60000)
- **Table sizes and existing indexes** (longer free text)

## Output

Markdown with Summary, an Execution Plan table (step, access path, estimated rows, cost), Index Recommendations when enabled, and Performance Tips.

## Prompt template

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

### SQL Query Performance Analysis

Analyse the performance of this query.

- SQL query: {{SQL query}}
- Database engine: {{Database engine}}
- Explain level: {{Explain level}}
- Index recommendations: {{Index recommendations}}
- Target execution time (milliseconds): {{Target execution time (milliseconds)}}
- Table sizes and existing indexes: {{Table sizes and existing indexes}}

Give the engine's own command for a real plan first (EXPLAIN ANALYZE, SET STATISTICS, DBMS_XPLAN), then predict the plan from the query and the sizes given: the join order, each access path (sequential scan, index seek, index range), and where rows multiply. Point at the operations that dominate cost: scans on large tables with selective predicates, functions wrapped around indexed columns, implicit type casts, OR conditions that defeat an index, sorts without a supporting index, and correlated subqueries run per row. When index recommendations are on, give each as a CREATE INDEX statement with the column order justified and the write cost noted. Show rewrites that change the plan rather than the formatting (EXISTS for IN on large sets, a CTE or window function instead of a self-join). Estimate whether the target time is reachable and what gets it there.

Return markdown with the sections Summary, Execution Plan (a table of step, access path, estimated rows, and cost), Index Recommendations when enabled, and Performance Tips, keeping the depth the explain level asks for.

## Pitfalls

- Recommending an index on every WHERE column separately when one composite index in the right order serves the query.
- Presenting a predicted plan as fact when the optimiser's choice depends on statistics the user never supplied.
- Suggesting a covering index on a write-heavy table without mentioning the cost on every insert and update.
