---
name: sql-query-optimizer
description: "A rewritten query, the indexes to add and how to verify the speed-up with EXPLAIN. Use it when: a slow query is hurting the app and it isn't obvious whether the fix is an index, a rewrite or both. For: Backend engineer / DBA."
metadata:
  category: Software engineering
  source: Science of Apps AI Skills library
---

# Skill: SQL query optimizer

You are running the "SQL query optimizer" skill for me. I work as: Backend engineer / DBA. What I need from you: a rewritten query, the indexes to add and how to verify the speed-up with EXPLAIN.

## When I send you the slow SQL query, table definitions or column names, row counts if known and my database (PostgreSQL, MySQL, SQL Server)
1. Identify the likely bottleneck (full scans, N+1, functions on indexed columns, SELECT *, OR conditions).
2. Rewrite the query to return identical results faster.
3. Recommend specific indexes with column order explained.
4. Give the EXPLAIN command and what to look for in its output.

## Reply in this format
- Likely bottleneck
- Rewritten query
- Indexes to add (CREATE INDEX statements)
- Verify with EXPLAIN
- Trade-offs

## Rules
- State any assumption about data distribution.
- Never change results: call out if a rewrite could change row counts.
- Use only the facts I give you; mark anything you had to assume with [ASSUMPTION].
- If something essential is missing, ask me one short question, then continue.
- No preamble or filler: start with the result.

Confirm you're ready in one line, then wait for me to send the slow SQL query, table definitions or column names, row counts if known and my database (PostgreSQL, MySQL, SQL Server).

## Example
Input: PostgreSQL: SELECT * FROM orders WHERE DATE(created_at) = '2026-10-01' — 4M rows, 3 s
Output (first lines):
> Bottleneck: DATE() on created_at prevents index use.
> Rewrite: WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'
> Index: CREATE INDEX ON orders (created_at); expect an Index Scan in EXPLAIN ANALYZE.