Writing SQL is easy. Writing SQL that runs fast under real production load? That's where the pain begins. You've likely stared at a query that works fine on your laptop but crawls in production, or spent hours crafting a complex JOIN only to realize the optimizer hates it. The good news: modern AI assistants can act as a senior database engineer, helping you refactor, debug, and optimize queries in seconds. But only if you know the right prompts.
This guide is a practical cheat sheet of 15 tested prompts for PostgreSQL, MySQL, and MongoDB. Each prompt is designed to solve a specific problem—from writing complex joins to designing sharding strategies. You'll get the exact prompt text, a real-world example, and the expected output. No fluff, just tools you can copy-paste into your AI assistant today.
1. The All-Purpose Query Optimizer
Prompt:
You are a senior SQL performance expert. Analyze the following query for performance issues. Identify any missing indexes, inefficient joins, or full table scans. Suggest a rewritten version with EXPLAIN output if possible. Also, recommend the exact index creation statements (include the right column order) for PostgreSQL/MySQL.
Query: [PASTE YOUR QUERY]
Example:
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid' AND o.created_at > NOW() - INTERVAL '30 days';
The AI will likely point out that o.status and o.created_at are not indexed, and o.user_id should be indexed with status as a composite index. It might suggest:
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
2. The JOIN Architect
Prompt:
Design a query that joins [Table A] and [Table B] on [key] while preserving all rows from [Table A] that have no match in [Table B]. Include a row count comparison between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN. Explain the business case for each.
Example: You have customers and orders. You need a list of all customers, including those with no orders, plus their total spend if any. The prompt will produce a LEFT JOIN with COALESCE(SUM(o.amount), 0), and explain that INNER JOIN would exclude non-buyers, while FULL OUTER JOIN is overkill.
3. The Index Strategist
Prompt:
Given this table schema: [paste CREATE TABLE]. My workload is 70% reads, 20% writes, 10% range queries on date. Recommend the best set of indexes (B-tree, BRIN, or GIN) for PostgreSQL, and for MySQL (BTREE, HASH). For each index, explain the trade-off on write performance.
Example: For a logs table with timestamp, level, and message, the AI might suggest a BRIN index on timestamp for large tables, a B-tree on level, and explain that BRIN is small but slower for point lookups.
4. The EXPLAIN Decoder
Prompt:
Here is the EXPLAIN output from my query. Identify the bottleneck and suggest specific changes. Also, explain what each node means in plain English.
EXPLAIN: [PASTE]
Example: You paste an EXPLAIN that shows a Seq Scan on a large table. The AI explains that the planner expected to scan 1 million rows, and recommends adding a WHERE clause on an indexed column, or using a partial index.
5. The CTE Simplifier
Prompt:
Rewrite this complex query using Common Table Expressions (CTEs) to improve readability and maintainability. Also, show me how to convert it to a recursive CTE if it involves hierarchical data.
Query: [PASTE]
Example: A query with multiple nested subqueries becomes a chain of CTEs, making it easier to debug and modify.
6. The Window Function Wizard
Prompt:
Using window functions, write a query to calculate a moving average of [metric] over a [time period] per [category]. Explain the ROWS vs RANGE frame, and show how to get the same result with a self-join for comparison.
Example: For a sales table, you want a 7-day moving average per product. The prompt will generate a query with AVG(amount) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and explain the difference.
7. The Anti-Pattern Detective
Prompt:
Review this query for common anti-patterns: SELECT *, N+1 queries, lack of LIMIT, and implicit type conversions. Provide a corrected version and explain each fix.
Query: [PASTE]
Example: The AI spots SELECT * and suggests listing columns, and flags a correlated subquery that could be rewritten as a JOIN.
8. The MongoDB Aggregation Pipeline Builder
Prompt:
For MongoDB, build an aggregation pipeline that [describe task: e.g., groups orders by customer, calculates total, filters by date]. Use $match, $group, $sort, and $limit. Provide an example with a sample collection and explain the order of stages.
Example: You need the top 5 customers by total order amount in the last month. The pipeline: $match on date, $group by customer, $sort by total, $limit 5. The AI also explains that $match early reduces the documents processed.
9. The NoSQL Data Modeling Advisor
Prompt:
I'm using MongoDB for a [describe app]. Should I embed or reference? Design a schema for [entities] with examples, and explain the trade-offs for read/write performance.
Example: For a blog with posts and comments, the AI suggests embedding comments if they are small and bounded, but referencing if they can grow unbounded. It provides sample documents.
10. The Sharding Strategist
Prompt:
For a MongoDB collection that will grow to [size], recommend a shard key. Explain the distribution and query patterns, and show how to configure the shard key with `sh.shardCollection()`. For PostgreSQL, discuss partitioning vs sharding.
Example: The AI recommends hashed shard key on user_id for even distribution, and warns about high-cardinality keys.
11. The Query Rewriter for Performance
Prompt:
Rewrite this slow query to run faster. Use techniques like replacing OR with UNION ALL, using EXISTS instead of IN, or adding a covering index. Show the EXPLAIN plan for both the original and the rewritten query.
Query: [PASTE]
Example: A query with WHERE a = 1 OR b = 2 becomes UNION ALL with two separate queries, each using an index.
12. The Database Schema Refactorer
Prompt:
Analyze this schema for normalization issues (1NF, 2NF, 3NF). Suggest a redesign that reduces redundancy while keeping query performance in mind. Provide DDL for the new schema.
Example: A table with repeated phone numbers is split into a separate phones table, with foreign keys.
13. The SQL Injection Safety Checker
Prompt:
Review this SQL code for injection vulnerabilities. Show how to parameterize it for PostgreSQL (using $1) and MySQL (using ?). Also, explain the risk of string concatenation.
Code: [PASTE]
Example: The AI converts SELECT * FROM users WHERE id = ' + $id to SELECT * FROM users WHERE id = $1 with a parameter binding.
14. The Error Message Decoder
Prompt:
I'm getting this database error: [paste error]. Explain the cause and give me a step-by-step fix. Include the exact SQL statement to resolve it.
Example: Error: "deadlock detected" — the AI explains that two transactions are waiting on each other's locks, and suggests setting a lock timeout or reordering operations.
15. The Query Generator from Natural Language
Prompt:
Generate a SQL query for PostgreSQL that [describe task in plain English]. Assume this table structure: [paste schema]. Also, generate a MongoDB aggregation equivalent.
Example: "Find all customers who have placed more than 5 orders in the last month, and show their total spend." The AI produces both SQL and a MongoDB aggregation pipeline, with explanations.
Putting It All Together: A Real-World Case
Let's say you run an e-commerce platform. A report query that aggregates orders per region is taking 12 seconds. Using prompt #1, the AI identifies a missing index on region and order_date. You add a composite index, and the query drops to 1.2 seconds—a 10x improvement. Then, using prompt #6, you rewrite the report to use window functions for running totals, reducing the code size by half. Finally, prompt #10 helps you plan for scaling: you partition the orders table by month, keeping the data manageable.
These prompts aren't magic—they're a way to leverage AI's knowledge of database internals. The key is to provide clear context: schema, query, and your goal. The more specific you are, the better the output.
The Bottom Line
Optimizing SQL is a skill that separates junior from senior developers. With the right prompts, you can tap into years of database expertise instantly. Start with the ones that match your current pain point, and you'll see immediate gains. And remember: always test the AI's suggestions on a staging environment first. No tool replaces a good DBA—but this comes close.
Now go paste your slowest query into your AI assistant and watch it transform.
Comments