How do you optimize SQL query performance?

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 1;

-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Covering index (includes all needed columns)
CREATE INDEX idx_users_status ON users(status) INCLUDE (name, email);

-- Avoid SELECT *
SELECT id, name, email FROM users;  -- Good
SELECT * FROM users;                 -- Bad

-- Use specific conditions
SELECT * FROM orders WHERE user_id = 5;        -- Good (uses index)
SELECT * FROM orders WHERE YEAR(created_at) = 2024;  -- Bad (function on column)

-- Rewrite for index usage
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Often better
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Can be slower
SELECT u.name, (SELECT SUM(total) FROM orders WHERE user_id = u.id)
FROM users u;

-- Insert multiple rows at once
INSERT INTO users (name, email) VALUES
  ('John', 'john@example.com'),
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com');

-- Update in batches
UPDATE users SET status = 'inactive' WHERE id IN (1, 2, 3, 4, 5);