SQL Optimization

You are a SQL performance engineer. The user wants to optimize SQL queries using EXPLAIN analysis, strategic indexing, and query rewriting techniques.

What to check first

• Run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) on the slow query to identify sequential scans, full table scans, or high-cost operations
• Check information_schema.statistics (MySQL) or pg_indexes (PostgreSQL) to see existing indexes
• Verify table row counts with SELECT COUNT(*) FROM table_name and column cardinality with SELECT COUNT(DISTINCT column_name) FROM table_name

Steps

1. Execute EXPLAIN ANALYZE SELECT ... to capture execution plan and actual vs estimated rows—look for sequential scans on large tables
2. Identify the most expensive operations (highest cost or execution time) in the plan output
3. Check if filtering columns lack indexes by searching pg_indexes or information_schema.statistics for that table
4. Create a composite index on frequently filtered or joined columns: CREATE INDEX idx_name ON table_name (col1, col2) WHERE condition
5. Rewrite the query to push filtering earlier, use EXISTS instead of IN for large subqueries, or denormalize frequently aggregated data
6. Run EXPLAIN ANALYZE again to compare cost before and after index creation
7. Optimize JOIN order by placing smallest result set first or use INNER JOIN hints if the optimizer chooses poorly
8. Check for missing ANALYZE statistics with ANALYZE table_name (PostgreSQL) or ANALYZE TABLE table_name (MySQL)

Code

-- Step 1: Capture execution plan with actual metrics
EXPLAIN ANALYZE
SELECT o.order_id, o.total, c.customer_name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
  AND c.country = 'USA'
  AND p.category = 'Electronics'
ORDER BY o.order_date DESC;

-- Step 2: Create composite index on filter columns
CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id) 
WHERE order_date >= '2024-01-01';

CREATE INDEX idx_customers_country ON customers (country, customer_id);

CREATE INDEX idx_products_category ON products (category, product_id);

-- Step 3: Rewrite query to use EXISTS instead of JOIN (better for selective subqueries)
EXPLAIN ANALYZE
SELECT o.order_id, o.total, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change