MySQL Query Optimization

Use EXPLAIN

EXPLAIN SELECT * FROM orders
WHERE user_id = 5 AND status = 'pending';
-- Check: type (should be ref/range), rows (lower = better), Extra

Indexing Strategy

-- Composite index: put equality columns first
CREATE INDEX idx_status_user ON orders(status, user_id);

-- Covering index: include all selected columns
CREATE INDEX idx_cover ON products(category_id, price, name);

Avoid Common Pitfalls

-- Bad: functions on indexed columns disable index
WHERE YEAR(created_at) = 2024

-- Good: use range instead
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- Bad: implicit cast
WHERE id = '5'   -- id is INT

-- Good
WHERE id = 5