Prompting for SQL Optimisation

TL;DR: Good SQL optimization prompts are not "optimize this query", that's a request for a guess. Paste the real EXPLAIN output, the table and index definitions, your row counts, and your engine and version, because a model can only reason about a plan it can see. Postgres, MySQL and SQLite each describe plans differently, so one template won't fit all three.

Why "optimize this query" is a bad prompt

Paste a slow SELECT into a chat window and ask for help, and you'll get an answer. It will suggest an index, maybe rewrite a subquery as a join, maybe recommend LIMIT or a covering column. Some of that advice will even be right, by the kind of luck that comes from pattern-matching against millions of examples of slow queries that looked something like yours.

What you won't get is anything grounded in your database, because the query text alone doesn't contain your database. It doesn't say whether customer_id has an index, whether that index is on (customer_id) alone or (customer_id, status) together, whether the table has 800 rows or 80 million, or whether the planner is choosing a full scan because that's genuinely cheaper at your data's actual distribution. A model asked to fix a query it can't see reaches for the generically plausible fix, the same failure mode covered in why models make things up applied to a schema instead of a citation.

The fix is the same one that makes debugging prompts work: give the model the evidence, not just the symptom. For SQL, the evidence is a query plan. This page stays inside the database: if the slowness is actually in the application layer, caching, or an ORM issuing a query per row, see prompting for performance optimisation instead.

What do you need to paste into the prompt, besides the query?

Four things, in this order of importance:

1. The actual execution plan, captured with EXPLAIN ANALYZE or your dialect's equivalent, not a description of it. "It's doing a full table scan" is your summary of a plan; the model needs the plan.
2. The CREATE TABLE statement, including every existing index. Without it, the model can't tell whether an index it's about to recommend already exists under a different name, or whether the columns it wants to combine already share one.
3. Real row counts, at least approximately. A composite index and a full scan cost about the same on a 4,000-row table; the difference only shows up at scale, and the model has no way to know your scale unless you say it.
4. What "slow" means here, in milliseconds, and what fast enough would look like. "Slow" is not a number a model can act on; "142ms, needs to be under 20ms" is.

None of this needs to be exhaustive. A schema dump of forty tables when three of them appear in the query mostly spends context window without adding anything the model can use, and can bury the one index definition that mattered under thirty-seven it will never touch.

How does EXPLAIN differ across Postgres, MySQL, and SQLite?

Enough that a prompt template written for one will misread another's output rather than fail cleanly. Here's the shape of each, verified against each vendor's own current documentation:

Three things fall out of that table that matter for a prompt.

First, EXPLAIN ANALYZE genuinely runs the statement, in both Postgres and MySQL. Postgres's docs are explicit that "the statement is actually executed when the ANALYZE option is used", and for anything other than a SELECT, "other side effects of the statement will happen as usual." For an INSERT, UPDATE, DELETE, MERGE, or CREATE TABLE AS you don't want committed, Postgres's own fix is a transaction you throw away:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

MySQL's EXPLAIN ANALYZE also runs the statement, on SELECT, multi-table UPDATE/DELETE, and TABLE statements. SQLite's EXPLAIN QUERY PLAN is the odd one out: it never executes anything, which is why it carries no cost or timing numbers at all.

Second, MySQL speaks two vocabularies depending on which command you run. Plain EXPLAIN describes each table with a type column, ALL meaning, per MySQL's own reference, a full table scan "for each combination of rows from the previous tables", versus ref, range, or const for narrower paths. EXPLAIN ANALYZE is always rendered as a tree instead, built from nodes like Table scan on t3 and Filter: (t3.i > 8), nothing like the type/key/Extra columns at all. A prompt that says "check the type column" finds nothing in that tree, not because the query changed, but because it's reading the wrong shape.

Third, SQLite's SCAN/SEARCH distinction is the whole story, worth knowing before you paste one into a prompt built around cost numbers that will never appear. SQLite's docs put it plainly: SCAN marks a full-table scan, SEARCH means "only a subset of the table rows are visited." Add an index and the plan line changes from SCAN t1 to SEARCH t1 USING INDEX i1 (a=?), and to SEARCH t1 USING COVERING INDEX i2 (a=?) if that index covers every selected column. No cost estimate, just which of two words appears.

A worked example: turning a Postgres seq scan into an index scan

Say you have this table and this query:

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  status       text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

SELECT id, customer_id, created_at
FROM orders
WHERE customer_id = 48213
  AND status = 'refunded'
ORDER BY created_at DESC
LIMIT 20;

With no index beyond the primary key, the plan looks like this. (This is an illustrative plan shaped like real PostgreSQL 18 EXPLAIN (ANALYZE, BUFFERS) output, not a capture from a live run against this exact table, and the numbers are for illustration only.)

                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=18420.55..18420.60 rows=20 width=24) (actual time=142.318..142.322 rows=20 loops=1)
   Buffers: shared hit=9812
   ->  Sort  (cost=18420.55..18463.21 rows=17064 width=24) (actual time=142.316..142.318 rows=20 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 27kB
         Buffers: shared hit=9812
         ->  Seq Scan on orders  (cost=0.00..17987.00 rows=17064 width=24) (actual time=0.031..138.902 rows=17201 loops=1)
               Filter: ((customer_id = 48213) AND (status = 'refunded'))
               Rows Removed by Filter: 482799
               Buffers: shared hit=9812
 Planning Time: 0.412 ms
 Execution Time: 142.401 ms
(11 rows)

Seq Scan means the planner reads every row and throws away the ones that don't match, 482,799 of them here, before it can even sort the 20 it needs. That's the line worth pasting into a prompt with the schema: "Seq Scan, filtered out nearly half a million rows to find twenty." Given that plan plus the CREATE TABLE statement, a model can reason about column order, because Postgres's own multicolumn-index docs are specific: "the index is most efficient when there are constraints on the leading (leftmost) columns." customer_id and status are equality constraints here, created_at is the sort column, which suggests one composite index:

CREATE INDEX orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

Re-running the same EXPLAIN (ANALYZE, BUFFERS) afterward (again, an illustrative plan, not a captured benchmark) shows a different shape:

                                                              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..8.86 rows=20 width=24) (actual time=0.028..0.061 rows=20 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using orders_customer_status_created_idx on orders  (cost=0.42..712.18 rows=1687 width=24) (actual time=0.026..0.057 rows=20 loops=1)
         Index Cond: ((customer_id = 48213) AND (status = 'refunded'))
         Buffers: shared hit=5
 Planning Time: 0.198 ms
 Execution Time: 0.089 ms
(6 rows)

That's a defensible claim: the rewrite turned a sequential scan filtering hundreds of thousands of rows into an index scan reading only what it needs. It is not a benchmark. I did not run this against a live database; your own timings depend on data distribution, cache state, and hardware, and the millisecond figures above exist to show plan shape, not a speedup to expect.

A worked example: reading MySQL's type and Extra columns

The same table, MySQL-flavoured, with an index only on customer_id:

CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_customer (customer_id)
) ENGINE=InnoDB;

EXPLAIN SELECT id, customer_id, created_at
FROM orders
WHERE customer_id = 48213 AND status = 'refunded'
ORDER BY created_at DESC
LIMIT 20;

Illustrative traditional-format output (partitions and filtered columns omitted here for width):

+----+-------------+--------+------+----------------+--------------+---------+-------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys  | key          | key_len | ref   | rows  | Extra                       |
+----+-------------+--------+------+----------------+--------------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | orders | ref  | idx_customer   | idx_customer | 4       | const | 17064 | Using where; Using filesort |
+----+-------------+--------+------+----------------+--------------+---------+-------+-------+-----------------------------+

type: ref already means MySQL is using the index rather than a full scan: MySQL's reference describes ALL as a full table scan done "for each combination of rows from the previous tables", which it says is normally bad and usually worse when the table isn't the first one read, so ref here is not the problem. The problem is Using filesort in the Extra column, which MySQL's own docs define as needing "an extra pass to find out how to retrieve the rows in sorted order." The index stops at customer_id, so it can locate matching rows but can't hand them back pre-sorted by created_at, and MySQL sorts them separately every time.

That single word, filesort, is the fact worth pasting into a prompt. MySQL's own optimization docs describe the fix directly: when a leading index column is held constant by an equality condition, "all rows accessed through the index are in [the next column's] order," so a composite index in the right column order can eliminate the sort entirely:

ALTER TABLE orders
  ADD INDEX idx_customer_status_created (customer_id, status, created_at);

Re-running the same EXPLAIN afterward, Extra drops Using filesort, possible_keys now lists both indexes, and key picks the new composite one; rows falls from an estimate of the whole customer_id slice to roughly the 20 the LIMIT needs. Same claim as the Postgres example: a defensible change in plan shape, not a timed benchmark.

Why doesn't the "leftmost column" rule tell the whole story anymore?

Because at least one vendor's optimizer got smarter than the rule of thumb most training data still describes. The leftmost-prefix rule itself is real and current. PostgreSQL's documentation on multicolumn B-tree indexes still says it plainly, for the case where the leading columns aren't constrained: "Constraints on columns to the right of these columns are checked in the index, so they'll always save visits to the table proper, but they do not necessarily reduce the portion of the index that has to be scanned."

But the current PostgreSQL documentation, and only the current documentation, also describes a "skip scan" optimization: a B-tree scan that can apply a constraint on a later column, generating one internally, even when an earlier column "lacks a conventional equality constraint". That sentence does not appear in the PostgreSQL 17 documentation for the identical page. MySQL documents its own version too, an Extra value called Using index for skip scan. Neither is guaranteed to fire for a given query, and an AI answer trained on older material, or on a mix of versions, may confidently tell you a query "can't use the index" for a reason that stopped being universally true.

What's a reusable prompt template for SQL optimization?

This one, filled in per query. It forces the four inputs from earlier and asks for one recommendation rather than a wish list:

You are optimizing a slow SQL query. Here is everything you need.

DATABASE: [engine and version, e.g. "PostgreSQL 18" or "MySQL 8.4"]

TABLE DEFINITIONS (including every existing index):
[paste CREATE TABLE statements here]

APPROXIMATE ROW COUNTS:
[table_name: row_count, ...]

THE QUERY:
[paste the exact SQL, unmodified]

THE PLAN (from EXPLAIN ANALYZE or this dialect's equivalent, full output):
[paste the complete plan, not a summary of it]

WHAT "SLOW" MEANS HERE:
Currently: [X ms observed]. Target: [under Y ms].

Task:
1. Name the single most expensive step in the plan and explain why it's expensive.
2. Propose ONE index or query change, not a ranked list of five.
3. State what you cannot verify without running it yourself (fresh statistics,
   real data skew, lock contention under concurrent writes).
4. If you propose a new index, give the exact CREATE INDEX statement in this
   database's own syntax.

Constraint 2 is doing real work: an unconstrained prompt tends to return several plausible-sounding options and leave you to guess which one the model actually believes in, which is the same shapeless-output problem covered in prompting for tables and structured data. One recommendation, with its reasoning attached, is something you can actually test and either keep or discard.

If you run this same shape of prompt often enough that retyping the four inputs gets old, reusable prompt variables turn the bracketed placeholders above into a saved template you fill in rather than rebuild by hand each time.

What can't AI see, and where do you still need judgment?

Four things, and none of them are visible from a query and a schema alone.

Real data skew. A composite index helps enormously when status = 'refunded' matches 200 rows out of 500,000, and does almost nothing when it matches 300,000 of them, because at that point a full scan can be cheaper than jumping around an index. The model can't know which world you're in unless your row counts and, ideally, your actual value distribution are in the prompt.

Concurrent write load. Every index added makes every future INSERT and UPDATE on that table slightly slower, since the index needs maintaining too. A recommendation correct for read performance in isolation can be wrong for a table under heavy concurrent writes, and a single EXPLAIN output can't show write volume.

Whether the index it names already exists. Reasoning only from the schema you pasted, an incomplete one, it will confidently recommend idx_customer_status_created without knowing you already have idx_status_customer covering nearly the same case: the schema-shaped version of the same confident invention covered elsewhere on this site. Paste the whole CREATE TABLE, indexes included, every time.

Whether the rewrite it proposes returns the same rows. Turning a correlated subquery into a JOIN, or an OR into a UNION, can change which rows come back under NULLs, duplicates, or edge-case joins, not just how fast they arrive. Read a proposed rewrite the way you'd read a genuinely useful code review: confirm what changed and why, before you run it anywhere real.

None of that is a reason to skip the prompt. It's a reason to run the model's one recommendation against a real EXPLAIN afterward, on a copy of the data if you have one, before it goes near production.