Menu

Things I learned while investigating slow MySQL queries in production

Reddit r/Database·u/KingBig9811·5 days ago
#vcDmjMpz
#queries#things#slow#approach#mysql
Reading 0:00
15s threshold

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…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.