SQL Query Optimization Techniques AI Prompt

Optimize this slow SQL query.

Query: {{query}}
Database: {{database}}
Table sizes: {{table_sizes}}
Current runtime: {{runtime}}

1. Diagnosis first:
   Run EXPLAIN ANALYZE to understand the query plan before making changes.
   Identify: Seq Scans on large tables, high estimated vs actual row counts, sort operations.

2. Optimization techniques:

   Filter early (predicate pushdown):
   -- Bad: filter after join
   SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
   WHERE o.created_at > '2024-01-01';
   -- Good: same query but the optimizer should handle this; verify in EXPLAIN

   Avoid functions on indexed columns in WHERE:
   WHERE YEAR(created_at) = 2024          -- prevents index use
   WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'  -- uses index

   Use EXISTS instead of IN for subqueries:
   -- Slow for large subqueries:
   WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'premium')
   -- Often faster:
   WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = orders.customer_id AND c.tier = 'premium')

   Avoid SELECT *:
   - Retrieves unnecessary columns, increases I/O and memory
   - Select only the columns you need

   Pagination: use keyset instead of OFFSET:
   -- Slow: OFFSET scans and discards N rows
   SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
   -- Fast: continue from last seen id
   SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 20;

3. Aggregate optimization:
   - Move aggregation before joins where possible
   - Pre-aggregate in a CTE, then join to the aggregated result

4. Rewriting correlated subqueries:
   -- Correlated: executes once per row (slow)
   SELECT c.*, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
   FROM customers c;
   -- Better: pre-aggregate and join
   SELECT c.*, COALESCE(o.order_count, 0) AS order_count
   FROM customers c
   LEFT JOIN (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY 1) o
     ON c.id = o.customer_id;

Return: optimized query with explanation for each change, expected improvement, and index recommendations.