Claude Code prompt to optimize database queries
Query optimization done by reading code is guessing. Claude Code can run your workload, capture what the database actually executes, and attach EXPLAIN output to every change, one commit per fix. You end up with a reviewable history where each commit message carries its own before and after plan.
Last updated
Find and fix the slowest database queries in this app. Start with evidence, not guesses: enable query logging or the ORM's debug output, run the existing test suite or a local traffic script to generate load, and collect the queries actually executed. Rank them by count times cost and show me the top offenders with their EXPLAIN or EXPLAIN ANALYZE output before changing anything. Typical fixes I expect: N+1 loops collapsed into joins or batched IN queries, missing indexes added through a proper migration file, SELECT star trimmed to needed columns, and pagination converted from OFFSET to keyset where tables are large. One fix per commit with the before and after EXPLAIN in the commit message. Re-run the same workload after each fix and confirm the query count dropped or the plan changed; never claim an improvement without showing the plan.
Same task in other tools
Questions about this prompt
My local dataset is tiny, so plans will not match production. Is this still useful?
Query counts and N+1 shapes are valid at any size; plan choices are not. Ask the agent to write a seed script inflating the hot tables to realistic row counts before profiling, or run the workload against a restored production snapshot.
Does this work with my ORM?
Name it in the prompt. The workflow of log, rank, EXPLAIN, fix, re-run is ORM-independent; only the logging switch and the eager-loading syntax for the N+1 fixes differ.
An index got added but the planner still ignores it. Why?
Stale statistics or a column-order mismatch are the usual causes. Have Claude Code run ANALYZE on the table, re-check the plan, and confirm the WHERE clause matches the index's leading columns before trying anything more exotic.