SQL Query Optimizer with Execution Plan Analysis

What this prompt does

This prompt takes your slow query and turns it into a structured performance investigation. It is not a generic "make this faster" request — the template explicitly asks the AI to identify why the query is slow (full table scans, subquery re-execution, missing indexes), rewrite it, and generate ready-to-run CREATE INDEX statements. The execution time target you set becomes a concrete benchmark the AI reasons against.

What makes this effective is the context it demands upfront: table sizes, current execution time, and existing indexes. Without those, AI SQL advice is guesswork. With them, the AI can distinguish between "this needs an index" and "this index already exists but is not being used" — two very different problems with very different fixes.

The ORM equivalent step is particularly useful on Laravel, Django, or ActiveRecord codebases where raw SQL is eventually going to be wrapped in a query builder call — you get both the tuned SQL and the idiomatic application-layer version in one pass.

When to use it

• A dashboard query on a large orders or analytics table starts timing out in production under real load.
• You have a correlated subquery in a WHERE clause that runs once per row and suspect it is the bottleneck but are not sure how to rewrite it as a join.
• You are doing a code review and need a second opinion on whether an ORM-generated query will hold up at scale.
• A scheduled job processing thousands of rows is creeping past its cron window.
• You inherited a legacy query with no comments and no original author to ask.
• You need CREATE INDEX DDL ready to hand off to a DBA or drop into a migration file.

Example output

For a MySQL query joining orders (2M rows) and order_items (8M rows) with a correlated subquery filtering by status:

SLOW REASON: Correlated subquery in WHERE executes 2M times — full scan on
order_items per row.

REWRITTEN:
SELECT o.id, o.total, item_counts.cnt
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS cnt
  FROM order_items
  WHERE status = 'shipped'
  GROUP BY order_id
) item_counts ON item_counts.order_id = o.id
WHERE o.created_at >= '2025-01-01';

INDEXES:
CREATE INDEX idx_order_items_status_order ON order_items (status, order_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

ESTIMATED IMPROVEMENT: ~40x reduction — from ~8s to ~200ms based on provided
table sizes and cardinality.

LARAVEL ELOQUENT EQUIVALENT:
$itemCounts = DB::table('order_items')
    ->select('order_id', DB::raw('COUNT(*) AS cnt'))
    ->where('status', 'shipped')
    ->groupBy('order_id');

Order::joinSub($itemCounts, 'item_counts', function ($join) {
        $join->on('item_counts.order_id', '=', 'orders.id');
    })
    ->where('orders.created_at', '>=', '2025-01-01')
    ->select('orders.id', 'orders.total', 'item_counts.cnt')
    ->get();

Pro tips

• Be precise with [table_sizes] — "large" is useless; "orders: 2.1M rows, order_items: 8.4M rows" lets the AI reason about index selectivity correctly.
• Paste the output of EXPLAIN or EXPLAIN ANALYZE alongside your slow query if you have it — the AI will interpret the actual execution plan and tie its recommendations directly to observed steps rather than inferred ones.
• If your [existing_indexes] list is long, highlight which ones are composite and in what column order — the AI needs that to determine whether an existing index is actually usable for your filter conditions.
• Use [target_time] to constrain the advice: "under 100ms" pushes the AI toward covering index solutions; "under 500ms" may accept a simpler rewrite. Setting an unrealistic target for the data volume will surface that tradeoff explicitly, which is useful on its own.
• For [orm], specify the exact framework and version (e.g., "Laravel 11 Eloquent" or "Django 4.2 ORM") — the generated query builder code will match the current API rather than deprecated syntax.