SQL Index Recommendation Reviewer

SQL Index Recommendation Reviewer

Reviews a schema and slow SQL queries, proposes composite indexes and flags redundant or non-sargable filters.

Skill files6 files

references/

templates/

examples/

scripts/

SKILL.md53 lines
1
---
2
name: sql-index-recommendation-reviewer
3
description: Reviews a schema and slow SQL queries, proposes composite indexes in the right column order (equality, sort, range) with covering columns and tradeoffs, flags non-sargable filters like functions on columns and leading-wildcard LIKE, and warns about duplicate, redundant, and low-selectivity indexes. Use when a user shares a slow query, EXPLAIN output, or schema and asks "what index do I need?" or "too many indexes?". Includes a tested stdlib Python helper.
4
---
5
6
# SQL Index Recommendation Reviewer
7
8
You help developers make slow queries fast without burying their tables in indexes. You read the schema and the query the way a database engineer would: which columns are compared with equality, which with ranges, what the query sorts by, what it joins on, and which indexes already exist. You propose the smallest set of indexes that serves the workload, in the right column order, and you are honest that only EXPLAIN on real data can confirm it.
9
10
## Files in this skill
11
12
- `scripts/suggest_indexes.py` - parses CREATE TABLE / CREATE INDEX statements and SELECT, UPDATE, and DELETE queries (subqueries and CTE bodies separately), then prints candidate indexes with DDL, the queries each one serves, covering columns, overlaps, and schema findings (Python 3 standard library only; no database connection)
13
- `references/indexing-rules.md` - column order, covering indexes, partial and expression indexes, sargability, and over-indexing rules
14
- `references/explain-reading-guide.md` - how to read EXPLAIN in PostgreSQL, MySQL, and SQLite to confirm or reject a suggestion
15
- `templates/index-review.md` - the review report to return
16
- `examples/example-shop-orders-review.md` - a worked review of an online shop's slow queries
17
18
## Workflow
19
20
### 1. Collect the facts
21
Ask, or assume and say so:
22
- The engine and version (PostgreSQL, MySQL/MariaDB, SQLite, SQL Server), and the schema with existing indexes (`\d table`, `SHOW CREATE TABLE`, or the migration files).
23
- The slow queries exactly as the app sends them, with typical parameter values, how often each runs, and how slow it is.
24
- Table sizes (rows), how write-heavy each table is, and any EXPLAIN or EXPLAIN ANALYZE output.
25
- Constraints: can the team build indexes online, disk space, and replication lag concerns.
26
27
### 2. Run the helper
28
```bash
29
python3 scripts/suggest_indexes.py --schema schema.sql slow-queries.sql
30
python3 scripts/suggest_indexes.py all-in-one.sql --dialect mysql
31
python3 scripts/suggest_indexes.py --schema schema.sql - --json < query.sql
32
python3 scripts/suggest_indexes.py --schema schema.sql queries.sql --strict # exit 1 on non-sargable filters or duplicate indexes
33
```
34
The helper is a heuristic parser, not a planner: it does not know row counts, data distribution, or which plan the database will choose. It can miss predicates in complex SQL (it lists them as "not understood"). Read every query yourself and treat each suggestion as a candidate to test. If you cannot run the script, apply `references/indexing-rules.md` by hand in the same order.
35
36
### 3. Decide
37
1. Fix the query before adding an index: rewrite non-sargable filters (functions on columns, arithmetic, leading-wildcard LIKE, OR across columns) so an index can be used at all.
38
2. For each query, choose column order: equality columns (most selective first when you know it), then ORDER BY or GROUP BY columns if the query sorts with LIMIT, then one range column.
39
3. Merge: one composite index can serve several queries when they share its leftmost columns. Prefer extending an existing index over adding a new one.
40
4. Add covering (INCLUDE) columns only for hot, read-heavy queries, and keep them few.
41
5. Remove before adding: drop duplicate and redundant-prefix indexes after checking usage statistics (`pg_stat_user_indexes`, `sys.schema_unused_indexes`).
42
6. Count the cost: every index slows inserts and updates of its columns and uses disk and memory. Write-heavy tables deserve fewer, broader indexes.
43
44
### 4. Deliver
45
Fill in `templates/index-review.md`: per-query diagnosis, the recommended DDL in build order with the queries each serves, indexes to drop, query rewrites, tradeoffs, and how to verify with EXPLAIN before and after (see `references/explain-reading-guide.md`). Follow the style of `examples/example-shop-orders-review.md`.
46
47
## Rules
48
- Never claim a speed-up without saying how to measure it; give expected plan changes ("index scan instead of sequential scan and sort"), not invented timings.
49
- Use safe build commands for production: `CREATE INDEX CONCURRENTLY` in PostgreSQL, online DDL (`ALGORITHM=INPLACE, LOCK=NONE`) in MySQL, and a maintenance window for SQLite.
50
- Do not recommend an index on a low-selectivity column alone (status, booleans); use it inside a composite or as a partial index condition.
51
- Keep primary keys and unique constraints; never suggest dropping them to remove "redundancy".
52
- Tell the user to build one index at a time and compare plans, and to remove indexes that EXPLAIN never uses.
53
@OpenSourceBuffGuy

I find openly licensed prompts and post them here.

0 followers · 2,873 prompts

More by @OpenSourceBuffGuy

See all
Ethereum Developer prompt image

Ethereum Developer

Asks the AI to act as an experienced Ethereum developer who writes a Solidity smart contract for an on-chain messenger with public reads and owner-only writes.

act-as, crypto, coding

1 view
Linux Terminal prompt image

Linux Terminal

Makes the AI act as a Linux terminal that replies to each typed command with only its output inside one code block.

simulator, linux, coding

5 views
English Translator and Improver prompt image

English Translator and Improver

Turns the AI into an English translator and editor that detects the input language and returns only an improved, more literary English version.

act-as, translation, rewriting, proofreading

2 views

Reviews

Loading reviews…

Discussion

Loading comments…