Query Response Time
Context
This is why you need to learn MySQL query response time:
This book explores that idea from various angles with a single intent: to help you achieve remarkable MySQL performance. Efficient MySQL performance means focusing on the best practices and techniques that directly affect MySQL performance—no superfluous details or deep internals required by DBAs and experts. I presume that you’re a busy professional who is using MySQL, not managing it, and that you need the most results for the least effort. That’s not laziness, that’s efficiency. To that end, this book is direct and to the point. And by the end, you will be able to achieve remarkable MySQL performance.
MySQL performance is a complex and multi-faceted subject, but you do not need to become an expert to achieve remarkable performance. Focus dramatically narrows the scope of MySQL complexity and allows me to show you what is essential. MySQL performance begins with query response time.
Query response time is how long it takes MySQL to execute a query. Synonymous terms are: response time, query time, execution time, and (inaccurately) query latency. Timing starts when MySQL receives the query and ends when it has sent the result set to the client. Query response time comprises many stages (steps during query execution) and waits (lock waits, I/O waits, and so on), but a complete and detailed breakdown is neither possible nor necessary. As with many systems, basic troubleshooting and analysis reveal the majority of problems.
Copyright 2021 Daniel Nichter
No reproduction of this excerpt without permission
Key Points
- Performance is query response time
- Query response time is the North Star of MySQL performance because it’s the only metric users experience
- Query response time is one of several query metrics: metrics about queries, not the server
- Query metrics originate from the slow query log or the Performance Schema
- The Performance Schema is the best source of query metrics
- Query metrics are grouped and aggregated by digest: normalized SQL statements
- A query profile shows queries sorted descending by total time; the first is the “slowest”†
- A query report shows all available information for one query; it’s used for query analysis
- The goal of query analysis is understanding query execution, not solving slow response time
- Query analysis requires query metrics, metadata (EXPLAIN plans, table structures, and so on), and knowledge of the application
- Improving query response time (query optimization) has two parts: direct query optimization, then indirect query optimization
- Direct query optimization is changes to queries and indexes
- Indirect query optimization is changes to data and access patterns
- The best way to make MySQL faster is to improve response time
† Some tools can sort queries by other metrics, but total time is the virtually universal default.
Pitfalls
- Not understanding that, first and foremost, performance is query response time
- Not having or reviewing query metrics (slow queries)
- Waiting until there’s a problem to review slow queries
- Using averages instead of the P95 or a higher percentile
- Not sorting queries by total time to find the slowest†
- Focusing only on server metrics to gauge performance
Hack MySQL Articles
Copyright 2025 Daniel Nichter