Let’s be honest: SQL query optimization is both an art and a science. You can spend hours staring at a slow query, guessing why it’s crawling, or you can ask an AI to help you pinpoint the exact bottleneck. The difference? The right prompt. In this article, I’ll share a practical collection of prompts that cover indexing, execution plans, and profiling for PostgreSQL and MySQL. Each prompt comes with a real-world example and a walkthrough of the expected output, so you can start using them immediately.
These prompts are not magic bullets. They work best when you provide context: your schema, the query, and the database engine. But with the right framing, an AI can act like a senior DBA sitting next to you, pointing out missing indexes, suggesting query rewrites, or explaining why the planner chose a sequential scan.
I’ve organized the prompts into three levels: Basic (for everyday tuning), Advanced (for deeper analysis), and Expert (for complex scenarios). Let’s dive in.
Basic Prompts: Everyday Query Tuning
1. The “Explain This Query” Prompt
Task: Get a human-readable explanation of what the query planner is doing, without needing to interpret EXPLAIN output yourself.
Prompt:
I have a PostgreSQL query that is slow. Here is the EXPLAIN ANALYZE output:
[Paste your output]
Explain what the database is doing step by step. Point out any red flags like sequential scans, high estimated vs actual row counts, or missing indexes. Suggest one concrete fix.
Example:
Say you run EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123; and paste the output. The AI will likely respond:
The planner is doing a sequential scan on
orders(cost=0.00..1.50, rows=1). Since there’s no index oncustomer_id, the database reads the entire table. I recommend creating an index:CREATE INDEX idx_orders_customer_id ON orders(customer_id);This should reduce the cost to a bitmap index scan or an index scan.
Why it works: The AI interprets the planner’s output, saving you time and helping you learn to read plans yourself.
2. The “Missing Index” Prompt
Task: Get index recommendations based on a specific query pattern.
Prompt:
My MySQL query is slow. Here’s the query and the table schema:
Query: SELECT * FROM products WHERE category = 'electronics' AND price > 100 ORDER BY created_at DESC;
Schema: [Paste your CREATE TABLE statement]
What index(es) would you recommend? Explain the order of columns in a composite index and why.
Example:
The AI might suggest a composite index: CREATE INDEX idx_category_price_created ON products(category, price, created_at); and explain that the leftmost prefix rule applies, so category should be first because it’s used in equality, then price for range, and created_at for sorting.
Why it works: It teaches you the logic behind index design, not just the answer.
3. The “Slow Query Rewrite” Prompt
Task: Rewrite a slow query for better performance while preserving the result.
Prompt:
I have a query that runs in 30 seconds. Can you rewrite it to be more efficient? Here’s the query and the schema:
Query: SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2025-01-01' AND o.total > 100;
Schema: [Paste schema]
Please provide the rewritten query and explain the changes. Also note if any indexes would help.
Example:
The AI might suggest using EXISTS instead of JOIN if you don’t need duplicates, or rewriting the WHERE clause to use a covering index. It would explain the trade-offs and give you a revised query.
Why it works: It gives you an alternative to test, and you learn different ways to express the same logic.
Advanced Prompts: Deep Dive into Execution Plans
4. The “Compare Two Plans” Prompt
Task: Compare two execution plans for the same query to decide which is better.
Prompt:
I have two execution plans for the same query, one with index A and one with index B. Here they are:
Plan 1: [Paste output]
Plan 2: [Paste output]
Which one is better and why? Consider cost, actual time, and the number of rows. What does this tell you about the indexes?
Example:
The AI will compare the estimated costs, look at actual vs. estimated rows, and explain which plan uses resources more efficiently. It might recommend keeping one index and dropping the other.
Why it works: It helps you make informed decisions about index maintenance.
5. The “Index Usage” Prompt
Task: Determine if a particular index is being used by the query planner.
Prompt:
I created an index on the `email` column of my `users` table, but my query `SELECT * FROM users WHERE email = 'test@example.com'` still seems slow. Here’s the EXPLAIN ANALYZE output:
[Paste output]
Is the index being used? If not, why? What can I do to force it or make it usable?
Example:
The AI might explain that the index isn’t used because of a data type mismatch (e.g., comparing varchar to a number) or because the planner expects the index scan to be more expensive than a sequential scan due to low selectivity. It would suggest casting or using LIKE with proper collation.
Why it works: It resolves the mystery of “why is my index not working?”
6. The “Full Table Scan” Prompt
Task: Get advice on eliminating sequential scans.
Prompt:
My query does a full table scan on a table with 10 million rows. Here’s the query and the schema:
Query: SELECT * FROM events WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31' && event_type = 'click';
Schema: [Paste schema]
How can I avoid the sequential scan? Should I use a composite index, partition the table, or something else? Please provide SQL.
Example:
The AI might suggest creating a composite index on (event_date, event_type), or if the table is huge, partitioning by event_date. It would give you the SQL to create the index or partition.
Why it works: It addresses a common performance issue with a concrete solution.
Expert Prompts: Profiling and Complex Scenarios
7. The “Query Profiling” Prompt
Task: Profile a query to see where time is spent across multiple executions.
Prompt:
I want to profile a query in MySQL. I’ve enabled profiling with `SET profiling = 1;` and run the query. Then I ran `SHOW PROFILES;` and `SHOW PROFILE FOR QUERY 1;`. Here is the output:
[Paste output]
What are the main bottlenecks? Which stages take the most time? How can I reduce the time spent in each?
Example:
The AI will analyze the profile, pointing out that “Sending data” takes 80% of the time, indicating network or result set issues, or that “Creating sort index” is slow, suggesting an index for sorting. It would provide specific recommendations.
Why it works: Profiling reveals granular timing information that can be hard to interpret without experience.
8. The “PostgreSQL pg_stat_statements” Prompt
Task: Analyze cumulative statistics to find the most resource-heavy queries.
Prompt:
I use PostgreSQL and have pg_stat_statements enabled. Here are the top 5 queries by total execution time from the view:
[Paste output]
What are the common patterns? Which queries should I optimize first? What indexes or changes would benefit them?
Example:
The AI might identify that queries are missing indexes on join columns or that some queries are doing redundant calculations. It would suggest creating specific indexes or rewriting the queries.
Why it works: It helps you prioritize optimization efforts based on actual usage.
9. The “Index Bloat” Prompt
Task: Detect and fix index bloat in PostgreSQL.
Prompt:
I suspect my PostgreSQL indexes are bloated. Here’s the output of `SELECT * FROM pgstatindex('my_index');`:
[Paste output]
What does this tell me? Should I rebuild the index? If so, what’s the safest way without locking the table for too long?
Example:
The AI will interpret the stats, noting high dead tuple percentage, and recommend REINDEX INDEX CONCURRENTLY my_index; to rebuild without blocking writes. It would also explain how to monitor bloat over time.
Why it works: Index bloat is a common but overlooked issue; the prompt guides you through diagnosis and remedy.
10. The “MySQL Performance Schema” Prompt
Task: Use MySQL’s Performance Schema to find slow queries and wait events.
Prompt:
I have MySQL with Performance Schema enabled. I ran this query to get the top 5 slow queries by total latency:
[Paste your query and output]
What are the main wait events? How can I reduce latency? Are there any configuration variables I should adjust?
Example:
The AI might point out that wait/io/table/sql/handler is dominant, suggesting that queries are doing too many disk reads, and recommend increasing innodb_buffer_pool_size or adding indexes.
Why it works: Performance Schema provides deep visibility, and the prompt helps you interpret it.
Real-World Case Study: From 20 Seconds to 50ms
Let’s put these prompts into practice. A client had a MySQL query that took 20 seconds to run on an e-commerce platform. The query was:
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31' AND status = 'shipped' ORDER BY order_date DESC;
The orders table had 5 million rows. Using the “Missing Index” prompt, we asked the AI for advice. It suggested a composite index on (status, order_date) because the status equality filter should be first, and order_date for the range and sorting.
We created the index:
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
Then we ran EXPLAIN ANALYZE again and used the “Explain This Query” prompt. The AI confirmed that the planner now used an index range scan, and the query time dropped to 50ms—a 400x improvement.
This shows that a simple index recommendation can have a dramatic impact. The prompts helped us diagnose the issue quickly and implement the fix confidently.
Conclusion
SQL optimization is a continuous learning process, but with the right prompts, you can shortcut the guesswork. Start with the basic prompts to understand your query plans, then move to advanced ones for deeper analysis, and finally use expert prompts for profiling and complex scenarios. The key is to provide the AI with complete context: your schema, the query, and the execution plan. The more accurate the input, the more valuable the output.
Now it’s your turn. Pick a slow query from your own database, run one of these prompts, and see what insights you get. You might be surprised at how quickly you can find and fix performance bottlenecks.
Comments