SQL is the one language every developer thinks they know — until a query that looks fine takes 40 seconds on a table with a few million rows. The good news: LLMs are surprisingly strong at SQL, but only if you ask the right way. A vague "optimize this query" gets you cosmetic changes; a prompt that hands the model the plan, the schema, and the constraints gets you a rewrite that actually survives production.
This roundup is a working set of prompts I use daily against PostgreSQL, MySQL, and SQLite. Each one includes a real example and a note on when to reach for it. Copy them, adapt the placeholders, and keep the ones that pay off.
1. The schema-aware JOIN builder
LLMs guess column names unless you force them not to. Start every session with the schema.
Prompt:
Here is my schema (DDL only, no data):
<paste CREATE TABLE statements>
Write a query that returns <goal>.
Rules: use only columns that exist in the DDL; alias every table; prefer explicit JOIN ... ON over comma joins; if a column is ambiguous, qualify it. If something is impossible with this schema, say so instead of inventing a column.
Example: "Return each customer's most recent paid order and the total they spent in the last 90 days." The model must join customers, orders, payments, and pick MAX(paid_at) per customer — the DDL constraint stops it from hallucinating a last_order_id that doesn't exist.
2. Window functions without the panic
Window functions are where most developers stall. The trick is to describe the partition and the ordering in plain English and let the model translate.
Prompt:
Rewrite this using a window function instead of a self-join:
<query>
I need: running total per account ordered by created_at, plus each row's rank within its account by amount descending. Use PARTITION BY and ORDER BY explicitly. Explain the frame clause you chose and why (ROWS vs RANGE).
Example: a self-join that computes a running balance becomes SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The model will explain why ROWS avoids the duplicate-timestamp trap that RANGE falls into.
3. EXPLAIN ANALYZE decoder
This is the highest-leverage prompt in the list. Most slow queries are slow for one of three reasons: a seq scan where an index should be, a bad row estimate, or a nested loop over too many rows.
Prompt:
Here is EXPLAIN (ANALYZE, BUFFERS) output:
<paste>
And the query:
<paste>
Identify: (1) the node with the highest actual time, (2) any place where estimated rows differ from actual by more than 10x, (3) whether a nested loop is being chosen over a hash join and why. Then propose the smallest change that fixes the worst node. Do not rewrite the whole query yet.
Example insight: a plan showing rows=1 estimated but rows=48000 actual on a filter means stale statistics — ANALYZE on that table often fixes it before you touch a single index.
4. Index detective
Prompt:
Given this query and its plan, list candidate indexes in priority order. For each: the exact CREATE INDEX statement, which predicate or join it serves, and the write-cost trade-off. Flag any index that would be redundant with an existing one.
Example: for WHERE tenant_id = ? AND status = 'open' ORDER BY created_at DESC LIMIT 20, the model proposes a composite (tenant_id, status, created_at DESC). It should also warn that a standalone index on status is nearly useless at low selectivity.
5. The N+1 hunter
ORM code hides N+1 queries until production traffic exposes them.
Prompt:
Here is my ORM code (Prisma/Sequelize/Django):
<paste>
Find every place that issues a query inside a loop or a serializer. For each, show the SQL it generates and the rewrite using a single JOIN or a batched IN (...) query. Estimate the query count before and after for a page of 50 items.
Example: a Django serializer touching order.customer.name per row turns 1 + 50 queries into 2 with select_related. The model spells out the count so you can see the win.
6. Rewrite for high load
Prompt:
This query runs on a hot path (thousands of calls per minute). Rewrite it to minimize lock time and buffer reads. Options to consider: keyset pagination instead of OFFSET, EXISTS instead of IN for large subqueries, splitting into a fast path plus a background job. Show the trade-offs, not just the code.
Example: replacing OFFSET 100000 LIMIT 20 with WHERE id > :last_id ORDER BY id LIMIT 20 — the model should note that keyset pagination requires a stable sort key and breaks if rows are inserted out of order.
7. Cross-dialect translator
Prompt:
Translate this PostgreSQL query to MySQL 8 and to SQLite. Preserve semantics exactly. Flag anything that has no direct equivalent (e.g., DISTINCT ON, FILTER, window frame defaults) and give the idiomatic replacement per engine.
Example: SELECT DISTINCT ON (user_id) ... has no MySQL equivalent — the model rewrites it as a ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ...) subquery and explains the difference.
8. Migration safety check
Prompt:
Here is a migration adding a column and an index to a table with tens of millions of rows:
<paste>
Tell me which statements take an exclusive lock, which can be done CONCURRENTLY, and the exact order to run them so the app keeps working. Flag any step that rewrites the table.
Example: CREATE INDEX blocks writes; CREATE INDEX CONCURRENTLY does not but cannot run inside a transaction. The model should sequence the steps so deploys don't stall.
9. Deadlock and lock-wait explainer
Prompt:
Here is the output of pg_locks and pg_stat_activity during a hang:
<paste>
Identify the blocking chain, which transaction holds the lock, and the shortest safe fix (cancel vs. terminate). Then suggest the code change that prevents it — e.g., consistent lock ordering.
Example: two transactions updating the same two rows in opposite order deadlock; the fix is to always lock rows in ascending id order.
10. Data-quality audit
Prompt:
Write a single query that reports, per table: null counts on NOT NULL-able columns, duplicate keys, orphaned foreign keys, and rows with timestamps in the future. Output one row per check with a count and a sample id.
Example: the model produces a UNION ALL of COUNT(*) FILTER (WHERE ...) checks — a quick pre-migration sanity pass.
11. Test data generator
Prompt:
Generate INSERT statements for 1000 rows into this schema, with realistic distributions: 80% of orders in the last 30 days, a few customers with 50+ orders to exercise the hot path, and some NULLs in optional columns. Keep it deterministic (fixed seed).
Example: you get a generate_series-based insert you can rerun, instead of random noise that changes every run.
12. Query plan regression check
Prompt:
Compare these two EXPLAIN ANALYZE outputs (before/after an index change):
<before>
<after>
Did actual time drop at the bottleneck? Did any node get worse? Is the new plan stable under different parameter values, or does it flip to a seq scan for some inputs?
Example: the model spots that the new plan is faster for the test value but flips to a seq scan when the filter is less selective — a classic parameter-sensitivity trap.
13. The rubber-duck reviewer
Prompt:
Act as a senior DBA reviewing this query for a code review. Ask me three questions about the data distribution and access pattern before you suggest changes. Then give a verdict: ship, ship with caveats, or rewrite.
Example: the questions ("How many distinct tenants?", "Is this on the read replica?", "What's the p99 latency budget?") often surface the real constraint faster than any rewrite.
What actually moves the needle
Three habits separate prompts that help from prompts that waste time: always paste the plan and the DDL, ask for trade-offs, not just code, and treat the first answer as a hypothesis to verify with EXPLAIN. LLMs are excellent at pattern-matching query shapes and terrible at knowing your data distribution — so make them show their reasoning, then confirm it against reality.
If you want to go deeper into indexes, query design, and load testing, the SQL & Databases course walks through the same mechanics these prompts rely on. And if you're pairing these with AI-assisted coding, the Rust prompts guide shows the same "give context, demand trade-offs" approach for a very different language.