Optimize SQL queries and database schemas for performance. Use when you need to: (1) analyze slow queries, (2) add or optimize indexes, (3) review database schemas, (4) interpret EXPLAIN plans, (5) fix N+1 query problems, or (6) design efficient data models.
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
Key indicators:
-- For frequent WHERE clauses
CREATE INDEX idx_users_email ON users(email);
-- For composite queries
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Bad
SELECT * FROM users WHERE id = 1;
-- Good
SELECT id, name, email FROM users WHERE id = 1;
-- Instead of querying in a loop, use JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3);
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
Consider the specific engine (PostgreSQL, MySQL, SQLite) when optimizing: