Database.Middle.How would you optimize a slow SQL query?

To optimize a slow SQL query, follow a structured approach. Here’s a step-by-step guide with techniques, tools, and tips:

---

🔍 1. Analyze the Query

✅ Use EXPLAIN (or EXPLAIN ANALYZE)

• In PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@example.com';

In MySQL:

EXPLAIN SELECT * FROM users WHERE email = 'a@example.com';

This reveals how the query is executed:

• Full table scan?
• Index used?
• Join type?
• Rows examined?

---

🔧 2. Index Optimization

✅ Add indexes on columns used in:

• WHERE, JOIN, ORDER BY, GROUP BY

CREATE INDEX idx_users_email ON users(email);

❗ Avoid over-indexing: indexes slow down inserts/updates.

✅ Use composite indexes for multiple columns

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

🛠️ 3. Query Rewrite

✅ Avoid SELECT *

Only select necessary columns:

SELECT id, email FROM users WHERE status = 'active';

✅ Use EXISTS instead of IN (for subqueries)

-- Bad
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = true);

-- Better
SELECT * FROM orders WHERE EXISTS (
    SELECT 1 FROM users WHERE users.id = orders.user_id AND active = true
);

🤝 4. Optimize Joins

✅ Use appropriate join types:

• INNER JOIN is faster than OUTER JOIN if you don’t need unmatched rows.

✅ Index foreign keys and join columns:

CREATE INDEX idx_orders_user_id ON orders(user_id);

📊 5. Reduce Rows Early

✅ Use LIMIT for pagination:

SELECT * FROM products WHERE category = 'tech' LIMIT 50;

✅ Add filters as early as possible:

SELECT * FROM logs WHERE event_time > NOW() - INTERVAL '1 day';

🧠 6. Materialize or Cache Results

• Use materialized views for expensive queries (PostgreSQL):

CREATE MATERIALIZED VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';

Use temporary tables for intermediate steps.

🔁 7. Check for Table Bloat and Stats

✅ PostgreSQL

• Run ANALYZE or VACUUM ANALYZE to update stats.
• Use pg_stat_user_tables to find large or bloated tables.

✅ MySQL

• Use ANALYZE TABLE to update optimizer statistics.

🧪 8. Test and Compare

Use query performance benchmarks:

• Run test queries with/without index
• Monitor rows, cost, and execution time via EXPLAIN ANALYZE

---

✅ Example: Optimization in Action

❌ Bad:

SELECT * FROM orders WHERE user_id = 5 ORDER BY created_at;

• No index on user_id
• Sort is slow

✅ Improved:

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

SELECT id, created_at, total FROM orders
WHERE user_id = 5
ORDER BY created_at;