Optimize a Slow SQL Query With AI
Ask AI to "make this query faster" and it guesses — it never saw your execution plan and doesn't know your indexes or row counts. Optimize a slow SQL query with AI instead: bring the evidence, diagnose before you change, and prove the result before it ships.
Build the SQL Tuning PromptThe "make this faster" that optimizes in the dark
A query that ran fine for a year is suddenly taking four seconds, so you paste it into the AI and ask it to make it faster. Back comes a confident answer: add an index on created_at, simplify the join, rewrite the subquery. It reads like tuning. But the AI never saw your execution plan, so it's guessing where the time actually goes. It doesn't know which indexes already exist, so it may be recommending one you have. It doesn't know the table holds 200 million rows or 200, which decides whether its advice helps or hurts. And it can't tell you whether its rewrite still returns the same rows — only that it looks faster. The suggestions might be right. But "might be right, untested, on a query whose plan I can't see" is not optimization; it's a guess with SQL syntax.
The reason "optimize this query" produces generic advice is that the query text is the smallest part of the problem. Why a query is slow lives in things the text doesn't show: the execution plan the engine actually chose, the indexes that exist, the row counts, the parameter values, the statistics, whether a function wraps a filtered column and quietly defeats an index. Optimizing well means handing the AI that evidence and asking it for a diagnosis you can verify — not a fix you paste into production. This guide is how to optimize a slow SQL query with AI without guessing: what evidence to bring, how to get bottlenecks-before-fixes, how to keep rewrites result-preserving, and how to prove any change before it ships. NewPrompt's SQL Optimization Prompt builds exactly this kind of request — an evidence-based tuning contract that ties every recommendation to the query, plan, or schema you provide. The boundary is the whole point: NewPrompt doesn't connect to your database, run the query, read a plan, or create an index — it helps you build the prompt; you run it in your own AI, and you measure and apply the result.
Why "optimize this query" gets you generic advice
Ask a model to optimize a slow SQL query with nothing but the query text, and it does the only thing it can: it pattern-matches. It has seen a million "my query is slow" posts, so it returns the advice that fits the average one — add an index, don't SELECT *, avoid the subquery — whether or not any of it fits yours. The advice isn't wrong in general; it's unattached to your specific query, which is what makes it unreliable. Here is what goes missing when the evidence isn't there:
- It recommends indexes blind. Without your existing index list, the AI can suggest one you already have, or one that overlaps an existing index and just adds write cost for no gain.
- It guesses the bottleneck. Without the execution plan, it can't tell a full scan from a spilling sort from a nested loop over a huge table — so it names the usual suspect, which may not be yours.
- It ignores size and selectivity. The same query is a different problem at 10,000 rows and 200 million; without row counts, the AI can't tell which advice is worth the trouble.
- It misses sargability. A filter like WHERE YEAR(created_at) = 2026 or a leading-wildcard LIKE '%west%' quietly defeats an index — but whether that's your problem depends on the column and the plan, not a generic rule.
- It changes results while "optimizing." A rewrite that drops a DISTINCT, shifts a date boundary, or turns a LEFT JOIN into an INNER JOIN can look faster and return different rows — with nothing flagging it.
- It claims a speedup it never measured. "This will be faster" arrives with the same confidence whether it's a real win or a regression waiting to happen, because the model can't run either version.
Step 1: Bring the evidence, not just the query
The single biggest upgrade to a SQL optimization prompt is the context you attach to it. Alongside the query, give the AI the database engine and version (SQL Server, PostgreSQL, and MySQL differ in plan tooling and optimizer behavior); the relevant table schema and the columns involved; the existing indexes on those tables; approximate row counts, and the selectivity of the key filters if you know it; the common parameter values, because a query is often fast for most inputs and slow for a few; the execution plan — the actual plan, not the estimated one — if you have it; the current runtime, logical reads, and CPU if you've measured them; and the result the query is supposed to return, so a rewrite can be checked against it. The more of this the AI has, the less it has to invent.
This is the exact shape the SQL Optimization Prompt builds a request around — a tuning contract with slots for the query, platform, plan, indexes, row counts, and measured symptoms, plus non-goals that keep the AI honest: do not invent an execution plan, do not assume indexes exist, do not assume row counts. When a piece of evidence is missing, a good prompt doesn't paper over it — it states what can't be concluded without it and hands you the command to capture it. You describe what you have; the tool assembles the prompt, and you run it in your own AI on your own query. It structures the request; it doesn't touch your database.
Step 2: Ask for a diagnosis first — bottlenecks before fixes
The order of the request matters as much as its content. Tell the AI its first job is to diagnose, not to fix: identify the expensive operations, estimate each one's share of the cost, and distinguish the bottleneck from its symptom — a slow sort caused by a missing filter is a filter problem, not a sort problem. Only after the diagnosis should it propose changes, ordered by expected impact, each tied to the specific evidence that predicts it. A flat list of five tips is what "optimize this" gives you; a ranked list of changes, each attached to a named bottleneck, is what "diagnose, then recommend" gives you. The second is reviewable; the first is a lottery.
Reading the plan is the heart of the diagnosis, and it's a real skill — the Execution Plan Analysis resource is a worked example of doing it under discipline: work from the actual plan rather than the query text, find the one or two operators where the cost concentrates, and treat a large gap between estimated and actual row counts as the lead signal, because it usually means stale statistics and it invalidates the choices built on top of it. It's a template you adapt to your own query; if you haven't captured the plan yet, its first move is the command to capture it — not a guess in its place.
Step 3: Keep rewrites result-preserving and index candidates clause-justified
A faster query that returns different rows is not an optimization — it's a bug that happens to run quickly. So make result-equivalence a hard rule: any rewrite must return the same rows, and the AI has to call out every difference in NULL handling, duplicates, ordering, or date boundaries, or state explicitly that there is none. Watch the changes that silently alter results — dropping a DISTINCT or a GROUP BY, turning a LEFT JOIN into an INNER JOIN, moving a predicate across a join, changing an inclusive date range to an exclusive one. Each can be a legitimate optimization or a semantic change, and the only way to know is to have the AI name it and to verify it yourself against the original result.
Index suggestions need the same discipline, pointed the other way. An index recommendation is a candidate, not a "run this now" — and a good one names the exact predicate, join, or sort it serves (no index without a clause), justifies the column order for a composite (equality columns first, then ranges, then the ORDER BY), and states its write tax: every index adds maintenance cost to the writes on that table, and an index that speeds one slow report may not be worth the tax on every write. The Missing Index Analysis resource is a worked example of that trade-off — mapping each clause to the index that serves it and pricing the write cost most advice omits. It's a template you adapt; whether the trade-off is worth it on your workload is a call you make on your data, not one the prompt makes for you.
Step 4: Make it separate facts from assumptions — and name what it can't know
The most dangerous optimization advice is the confident kind built on an unstated guess. So require the AI to label its reasoning: what's a fact from the query, plan, or schema you provided; what's an assumption it's making about data volumes, distributions, or index state; and what's a hypothesis it can't confirm without evidence you haven't given. Any recommendation resting on an unverified assumption has to be flagged as conditional on it. This turns "add this index" into "if region is as selective as I'm assuming — which this query would confirm — add this index," which is a claim you can check instead of a directive you either trust or don't.
The corollary is that a missing execution plan or index list isn't something the AI should work around by guessing — it's something it should name. When the plan isn't provided, the right output isn't an invented plan; it's the exact command to capture one, with every plan-dependent conclusion marked as pending that evidence. "I can't determine the bottleneck without the actual plan; here's how to get it" is more useful than a confident wrong answer, because it tells you what to do next instead of sending you to optimize the wrong operator. An AI that says what it doesn't know is one you can actually work with on a hard query.
Step 5: Get a benchmark plan — and keep the change out of production until it's proven
Every candidate change needs a way to prove it, so ask for the benchmark plan alongside the recommendations: what to measure (runtime, logical reads, CPU, and the row counts flowing between operators), how to compare (the actual execution plan before and after), and on what data (production-shaped, not a tiny dev copy where everything is fast). Because a query is often slow only for certain inputs — from data skew, or from a cached or prepared plan compiled for an unrepresentative value, which is what parameter sniffing means — the benchmark has to cover the common parameter sets and the pathological ones, not just the one that was easy to test. A change that's faster on average and thirty times slower for your biggest customer is not a win, and the benchmark is how you learn that before your users do.
And nothing generated goes straight into production. Treat every rewrite and every index as candidate code: test it in staging or dev against representative data, get a DBA or a senior engineer to review anything that touches indexes or schema, and for any DDL — a new index, an altered column — have a backup, a rollback path, and monitoring in place before you run it, because index builds can lock and plans can regress. NewPrompt gives you the prompt and the structure to get reviewable optimization candidates; capturing the plan, running the benchmark, verifying the results still match, and deciding what's safe to ship all happen on your side, with your DBA, on your database. The AI can propose the change; whether it's correct, safe, and worth it is a call you own.
Common mistakes
The habits that turn AI SQL help into a fast way to ship a slow — or wrong — query:
- Optimizing without the plan. Without the actual execution plan, the AI is guessing where the time goes; capture the plan first, or treat every conclusion as provisional and labeled so.
- Letting a rewrite change the results. A query that returns different rows isn't faster, it's broken; make result-equivalence a rule and verify it against the original output yourself.
- Treating index suggestions as commands. An index is a candidate with a write cost; check what already exists, justify the column order, and price the tax on every insert before you create it.
- Believing an unmeasured speedup. "This will be faster" means nothing until you've measured runtime, reads, and the plan before and after on production-shaped data.
- Testing on a tiny dev database. The query is fast on 10,000 rows and slow on 200 million; a benchmark on unrepresentative data proves nothing about production.
- Running generated DDL in production unreviewed. NewPrompt doesn't connect to your database, run the query, benchmark it, or create the index — index and schema changes need staging, a DBA review, and a rollback plan before they go live.
A worked example: a slow order-listing query
Watch "optimize this query" return a blind index tip, then an evidence-first prompt turn the same slow query into a diagnosis you can verify — with the missing evidence named instead of invented.
"Optimize this SQL query" returns a blind index tip (no plan, no existing indexes, no row counts, and it misses the non-sargable YEAR() filter and the leading-wildcard LIKE); an evidence-first prompt names the likely bottlenecks, keeps rewrites result-preserving, prices each index candidate's write cost, and hands back a benchmark plan — candidates you capture the plan for, verify, and let a DBA approve before productionTHE SLOW QUERY (GET /orders, ~4s):
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE YEAR(o.created_at) = 2026
AND c.region LIKE '%west%'
ORDER BY o.created_at DESC;
THE WEAK ASK, AND WHAT IT GIVES BACK:
ask: "Optimize this SQL query."
answer: "Add an index on orders.created_at and use the join more
efficiently."
why it's a guess:
- no execution plan -- the bottleneck is unknown
- existing indexes unknown -- the suggested one may already exist
- row counts unknown -- can't tell if the advice is worth it
- YEAR(created_at) is non-sargable, so an index on created_at alone
may not even be used -- unmentioned
- c.region LIKE '%west%' has a leading wildcard -- also unmentioned
- no check that a rewrite returns the same rows; no way to measure
AN EVIDENCE-FIRST PROMPT:
Analyze this slow query and propose candidate optimizations.
Do not assume you can run it.
Engine: PostgreSQL 15.
Provided: query; schema for orders + customers; existing indexes;
approximate row counts; a common region value; current runtime.
Not provided: actual execution plan -- do not invent one; state the
command to capture it.
Output:
- the likely bottlenecks from the evidence given, and what stays
unknown without the plan
- result-preserving rewrite candidates (flag any change to NULLs,
duplicates, ordering, or date boundaries)
- index candidates: the clause each serves, its column order, and
its write cost
- a benchmark plan: what to measure, before/after, on which data
- a reviewer checklist
Rules:
- No speedup claim without a measurement.
- No result-semantics change without flagging it.
- No production DDL without staging + DBA review.
PART OF WHAT COMES BACK (candidates you verify):
missing evidence:
- actual plan needed to confirm the bottleneck
- existing index definitions needed before recommending new ones
observations:
- YEAR(o.created_at) = 2026 is non-sargable; a sargable range
(created_at >= '2026-01-01' AND created_at < '2027-01-01') can use an index
-- SAME result, but verify the boundary handling
- region LIKE '%west%' can't use a normal B-tree index; is an exact
region match possible instead?
- SELECT * inflates reads; select only the columns actually used
index candidate:
- orders (customer_id, created_at) IF the filters match the
workload -- confirm against the plan; costs writes on orders
verify:
- compare row count + sample rows, old vs new
- compare actual plan + runtime + logical reads on prod-shaped data
- test across common AND rare region values (parameter sniffing)
- DBA review before any index goes to production
NEXT: you capture the plan, confirm the rewrite returns identical rows,
benchmark before/after on representative data, and let a DBA sign off
on any index -- the AI proposed the candidates; you prove and apply them.Where this fits in NewPrompt
Tuning a slow query is a diagnosis job, and NewPrompt gives you the structure for the request, not a connection to your database. The SQL Optimization Prompt builds the evidence-based tuning contract — bottlenecks with their cost share, index recommendations that name their clause and their write tax, and non-goals that stop the AI inventing a plan it never saw. The Execution Plan Analysis resource is the worked example for reading the plan the engine actually chose, and the Missing Index Analysis resource is the one for pricing an index before you add it. Each builds a prompt or shows a pattern you run in your own AI, against your own query — the database work itself stays on your side.
This guide sits in the software-development cluster as the one about optimizing a slow SQL query specifically. Making AI explain unfamiliar code is about understanding what code does; this is about why a query is slow, which the code alone can't tell you. Debugging with AI without chasing the wrong cause is about finding a bug's root cause; a slow query has a performance root cause — the bottleneck — and this applies that same diagnose-before-you-fix discipline to it. And refactoring code without changing behavior is the general form of the result-equivalence rule at the heart of a safe SQL rewrite. The through-line: get the evidence, diagnose before you change, and prove it before it ships.
Optimizing a query you can't see the plan for is like tuning an engine by ear over the phone. The advice might be sound in general — check the timing, clean the injectors — but without the diagnostic readout you're guessing which cylinder is misfiring, and a confident wrong guess can leave the engine worse than you found it. The plan is the diagnostic readout, the benchmark is the dyno, and the AI is a sharp mechanic who can reason well from the numbers — once you hand them over. What it can't do is put the car on the lift: it doesn't run your query, read your plan, or measure the result. So it proposes; you capture the plan, run the benchmark, and — with your DBA — decide what actually gets bolted on. A fast query you can't trust is worse than a slow one you can.