I've been spending some time investigating slow MySQL queries in production recently. Thought I'd put together some notes on the approach I followed and a few things I found useful along the way.
My general approach is to get as much as possible out of the existing database before considering additional replicas, sharding or other infrastructure changes.
A few things I usually look at:
Identifying expensive queries: Query frequency, average latency and rows read. A moderately slow query executed hundreds of times can be more expensive than an occasional slow one.
EXPLAIN ANALYZE: Understanding actual execution plans, table scans, index lookups, rows and loops.
Joins: Looking at nested-loop joins and how the number of rows entering a join affects subsequent lookups.
Indexes: Choosing indexes based on actual query patterns rather than adding them blindly.
Application code: Tracing expensive queries back to their source and checking for things like N+1 queries.
Verification: Comparing database metrics…