You've been staring at the same slow query for an hour. The EXPLAIN output looks like a foreign language. The index you created last week isn't being used. And your production database is starting to sweat under the load. We've all been there.
Optimizing databases is both an art and a science. The science part — understanding B-tree internals, lock contention, query planner decisions — is well-documented. But the art? That's knowing what to ask, where to look, and how to frame the problem. And that's where AI can help.
This isn't a list of generic 'write me a query' prompts. This is a collection of 14 battle-tested prompts I've refined over months of real-world database work. Each one targets a specific pain point: from analyzing query plans to designing schemas that won't fall apart at scale. They're designed to work with any modern LLM (GPT-4, Claude, Gemini, or open-source models), and they'll save you hours of documentation digging.
Let's dive in.
1. The Query Plan Translator
When: You've run EXPLAIN ANALYZE and have no idea what it's telling you.
Prompt:
You are a PostgreSQL performance expert. Analyze the following EXPLAIN ANALYZE output for a query that [describe the query and its purpose]. Explain in plain English:
1. What is the actual execution flow?
2. Where are the bottlenecks?
3. Which nodes are the most expensive (by actual time and rows)?
4. Why might the planner have chosen this plan?
5. What specific indexes or query rewrites would improve it?
EXPLAIN ANALYZE output:
[PASTE HERE]
Why it works: LLMs are trained on vast amounts of query plan examples. They can detect common patterns like sequential scans on large tables, nested loop joins that should be hash joins, or misestimated row counts.
Example: I once pasted a plan with a Seq Scan on orders filtering on customer_id — the LLM immediately pointed out the missing index and even suggested a partial index for the WHERE status = 'active' clause.
2. The N+1 Query Hunter
When: Your API responses are slow, and you suspect too many database round-trips.
Prompt:
You are a database optimization expert. Here is a code snippet that interacts with a database (ORM or raw SQL). Identify N+1 query problems — where one query is executed for each item in a loop. For each occurrence:
- Explain why it's an N+1 (show the query count formula)
- Rewrite the code to use a JOIN, batch query, or eager loading
- Show the optimized code and the expected reduction in queries
Code:
[PASTE YOUR CODE]
Why it works: N+1 is a classic, and LLMs are excellent at spotting loops that trigger queries. It's like having a senior engineer review your code.
Example: A Django ORM snippet that fetches authors in a loop — the prompt suggested select_related and reduced 100 queries to 1.
3. The Index Whisperer
When: You have a slow query and want to know exactly which index to create.
Prompt:
You are a database indexing expert. For the following query, recommend the optimal indexes. Consider:
- The WHERE clause columns (equality, range, and ORDER BY)
- The SELECT columns — would covering indexes help?
- The table's approximate size and write frequency
Query:
[PASTE QUERY]
Table schema:
[PASTE CREATE TABLE STATEMENT]
Provide the exact CREATE INDEX statements and justify each one.
Why it works: The LLM can reason about composite index column order (equality first, then range) and covering index benefits.
Example: For a query filtering on status and sorting by created_at, it recommended (status, created_at DESC) — perfect for the ORDER BY.
4. The Schema Architect
When: Designing a new table or a whole database from scratch.
Prompt:
You are a database schema design expert. Design a normalized (3NF) schema for [describe your domain, entities, and relationships]. For each table:
- Define columns with appropriate data types (PostgreSQL/MySQL)
- Specify primary keys, foreign keys, and unique constraints
- Discuss trade-offs between normalization and performance
- Suggest indexes for the most common queries
Also provide a sample INSERT and a complex SELECT with JOINs.
Why it works: LLMs have memorized best practices for schema design from countless tutorials and real-world examples.
Example: For an e-commerce app, it produced a clean orders, order_items, products schema with proper foreign keys and partial indexes for active orders.
5. The Missing Index Detector
When: You have a slow query and want to find the missing index automatically.
Prompt:
You are a PostgreSQL query tuner. Given the following query and the table schema, identify:
1. Which columns in the WHERE, JOIN, and ORDER BY clauses are not indexed?
2. What type of index (B-tree, Hash, GIN, BRIN) would be best for each?
3. Write the exact CREATE INDEX statements.
4. Show the EXPLAIN ANALYZE output you would expect before and after the index.
Query:
[PASTE QUERY]
Schema:
[PASTE SCHEMA]
Why it works: It forces the LLM to think like a query planner — which columns are actually being used for filtering and sorting.
Example: It caught a missing index on user_id in a posts table, turning a 2-second scan into a 5ms index scan.
6. The Cost-Benefit Analyzer
When: Deciding whether to add an index or rewrite a query.
Prompt:
You are a database consultant. We have a table [table_name] with [row count] rows and [write frequency] writes per second. The following query is slow:
[PASTE QUERY]
Evaluate two options:
1. Adding an index on [suggest columns]
2. Rewriting the query to [suggest alternative]
For each option, estimate:
- Impact on read performance (expected speedup)
- Impact on write performance (overhead)
- Disk space usage
- Implementation complexity
Recommend the best option with justification.
Why it works: This prompt forces the LLM to consider trade-offs, not just blindly add indexes.
Example: For a high-write table, it recommended a partial index over a full index because the write overhead was too high.
7. The Slow Query Autopsy
When: You have a specific slow query and want a step-by-step optimization.
Prompt:
You are a database performance detective. Here's a slow query:
[PASTE QUERY]
Provide a step-by-step diagnosis:
1. Explain what the query is doing logically.
2. Identify potential performance killers (e.g., full table scans, functions in WHERE, lack of indexes).
3. For each issue, show a specific fix (index, rewrite, denormalization).
4. Show the optimized query and why it's faster.
Use a concrete example with mock data to illustrate the difference.
Why it works: It mimics a systematic debugging approach, and LLMs are good at following step-by-step reasoning.
Example: A query with WHERE YEAR(created_at) = 2024 was rewritten to WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' to allow index usage.
8. The Migration Safe-Cracker
When: Writing a complex database migration (schema change, data backfill).
Prompt:
You are a database migration expert. I need to write a migration that:
- [Describe the change: add column, change type, backfill data, etc.]
- The table has [row count] rows and is in production.
Provide:
1. A step-by-step migration plan (using ALTER TABLE, UPDATE, etc.)
2. Lock implications and how to minimize downtime
3. A rollback strategy
4. The exact SQL for both forward and rollback migrations
5. How to test the migration safely
Why it works: Migrations are risky, and LLMs can generate safe, well-structured SQL with proper transaction handling.
Example: For adding a NOT NULL column to a large table, it suggested adding with a default, then dropping the default — avoiding table rewrite.
9. The Query Rewrite Wizard
When: You have a working but slow query and want to rewrite it for performance.
Prompt:
You are a SQL performance expert. Rewrite the following query to be more efficient. Use techniques like:
- Replacing subqueries with JOINs or CTEs
- Using window functions instead of self-joins
- Avoiding functions in WHERE clauses
- Using EXISTS instead of IN where appropriate
Explain each rewrite and why it improves performance.
Original query:
[PASTE QUERY]
Why it works: LLMs know all the classic SQL antipatterns and can suggest idiomatic alternatives.
Example: It converted a correlated subquery to a window function, cutting execution time by 80%.
10. The NoSQL Schema Designer (MongoDB)
When: Designing a MongoDB schema (documents, collections) that balances flexibility and performance.
Prompt:
You are a MongoDB schema design expert. Design a schema for [describe your application]. Consider:
- Document structure (embedded vs. referenced)
- Use of arrays and subdocuments
- Indexing strategy (single-field, compound, multi-key)
- Aggregation pipeline use cases
Provide example documents, index definitions, and a sample aggregation query.
Why it works: MongoDB schema design is different from SQL, and LLMs have absorbed the official guidance on embedding vs. referencing.
Example: For a blog platform, it recommended embedding comments in posts for small comment counts, but referencing for large ones.
11. The Sharding Strategist (MongoDB)
When: Scaling MongoDB horizontally — choosing a shard key.
Prompt:
You are a MongoDB scaling expert. I'm about to shard a collection with [describe the data and access patterns]. Recommend a shard key:
1. Explain the characteristics of a good shard key (high cardinality, low frequency, monotonically increasing vs. random)
2. Propose 3 candidate shard keys and evaluate each
3. Recommend the best one and explain how it distributes writes and reads
Also discuss the impact on chunk splitting and balancing.
Why it works: Shard key selection is tricky, and LLMs can reason through the trade-offs with concrete examples.
Example: It recommended a hashed shard key on user_id for a social app to evenly distribute writes.
12. The Query Optimizer (MongoDB)
When: A MongoDB aggregation pipeline is slow.
Prompt:
You are a MongoDB performance expert. Here's an aggregation pipeline:
[PASTE PIPELINE]
Identify performance bottlenecks:
1. Stages that scan the entire collection (e.g., $match after $unwind)
2. Missing indexes that would help $match and $sort
3. Expensive operations like $lookup without indexes
Provide an optimized pipeline with the same results and explain why it's faster.
Why it works: LLMs understand MongoDB's aggregation pipeline and can reorder stages for optimal performance.
Example: It moved $match before $unwind, reducing the number of documents processed by 90%.
13. The MySQL Lock Analyzer
When: You're experiencing deadlocks or lock contention in MySQL.
Prompt:
You are a MySQL InnoDB expert. Here is a deadlock report from SHOW ENGINE INNODB STATUS:
[PASTE REPORT]
Explain:
1. What transactions are involved and what locks they hold
2. The root cause of the deadlock
3. How to fix it (e.g., change transaction isolation, reorder operations, use SELECT ... FOR UPDATE properly)
Provide a concrete example of the fix.
Why it works: Deadlock reports are cryptic, but LLMs can parse them and suggest standard solutions.
Example: It identified two transactions locking rows in different orders and suggested a consistent ordering to prevent deadlocks.
14. The Performance Tester
When: You want to benchmark a query before and after optimization.
Prompt:
You are a database benchmarking expert. Write a script (in Python or SQL) to benchmark the following query before and after an index change. Use EXPLAIN ANALYZE and measure actual execution time. Provide:
1. The benchmark script
2. How to interpret the results
3. A sample output table comparing execution times
Query:
[PASTE QUERY]
Indexes:
[PASTE INDEXES]
Why it works: It gives you a repeatable process to validate improvements.
Example: It generated a Python script using psycopg2 to run the query 100 times and report average times.
Final Thoughts
These prompts aren't magic — they're tools that amplify your own expertise. Always verify the AI's suggestions against your actual data and environment. But used wisely, they can turn you into a database performance ninja.
Start with the prompt that matches your current headache. Paste your real query or schema, not a hypothetical one. And remember: the best optimization is the one you test and measure.
If you have a favorite database prompt that's not on this list, share it in the comments — I'm always looking to expand my toolbox.
Comments