Use EXPLAIN ANALYZE, avoid SELECT *, add covering indexes, and enable slow query log for tuning.
Performance Optimisation
-- EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 1 AND status = 'completed';
-- Avoid SELECT *
SELECT id, name, email FROM users; -- only needed columns
-- Use covering index
CREATE INDEX idx_user_status ON orders(user_id, status, total);
-- Query uses index only, no table lookup
-- Avoid functions on indexed columns
-- BAD (can't use index):
WHERE YEAR(created_at) = 2024
-- GOOD (uses index):
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- Slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- log queries taking > 1s
-- ANALYZE TABLE (update statistics)
ANALYZE TABLE orders;