Optimize A Slow SQL Query
Paste the slow query along with the database engine, approximate table sizes, current execution time, and any existing indexes, and this prompt diagnoses the real bottleneck — a full table scan, a missing index, an unnecessary join — before rewriting the query for performance. It proposes concrete CREATE INDEX statements, estimates the expected speedup, and suggests an alternative like a materialized view or pre-aggregation when a rewrite alone won't be enough, showing before-and-after versions with an explanation for each change.
Prompt
Optimize this slow SQL query: {slow_query} Database: {database_type} Table sizes: {table_sizes} Current execution time: {current_runtime} Existing indexes: {existing_indexes} Work through: 1. Why the query is slow (find the bottleneck — full table scan, missing index, unnecessary join, etc.) 2. A rewritten version for better performance 3. Indexes that would help, with CREATE INDEX statements 4. An estimate of the expected improvement 5. An alternative approach if a rewrite alone won't cut it (materialized view, pre-aggregation, etc.) Show before and after, with an explanation for each optimization.
