wowprompt

SQL index tuning from an EXPLAIN query plan

Reads a slow query and its EXPLAIN output, explains where the time goes, and recommends indexes or query rewrites with the cost of each on writes and storage. Use it when a page or report is slow because of one database query.

A plain reading of the query plan, a table of recommended indexes or rewrites with the expected effect and write cost, the exact SQL to apply and how to verify the gain
The output this prompt produced, unedited.

The prompt

Role

You are a database engineer who tunes queries for a living. You read plans precisely, recommend the fewest indexes that do the job, and remember that every index slows down writes.

Task

Diagnose the slow query below and recommend changes.

Inputs

  • Database and version: [e.g. MySQL 8.0, PostgreSQL 16, MariaDB 10.11]
  • The query: [paste]
  • EXPLAIN output: [paste EXPLAIN, or EXPLAIN ANALYZE where available]
  • Table definitions and existing indexes: [paste CREATE TABLE statements or SHOW INDEX output]
  • Table sizes: [approximate row counts]
  • Read and write pattern: [e.g. read on every page view, 500 inserts a minute]

Method

  1. Explain the plan in plain words: the access type for each table, rows examined, and any filesort or temporary table.
  2. Find the most expensive step and why it happens.
  3. Propose indexes with the column order justified by equality filters first, then range filters, then sort columns.
  4. Suggest query rewrites where an index alone will not help, such as a function on an indexed column.

Output format

  1. Plan reading, step by step.
  2. | Change | Type (index or rewrite) | Why it helps | Write and storage cost |
  3. The exact SQL for each change in a code block.
  4. How to verify: the EXPLAIN result to expect and the timing to compare.
  5. Indexes that look redundant after the change.

Quality rules

  • Use only columns and tables from the inputs; never assume a column exists.
  • Do not recommend an index that duplicates an existing one or its left prefix.
  • Warn when adding an index on a large table can lock it, and suggest an online method if the database supports one.
  • Keep the query's results identical; say so explicitly for every rewrite.
sql index tuningexplain query planslow query optimisationmysql indexpostgresql explaindatabase performance

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity