Efficient MySQL Performance Best Practices and Techniques (Daniel Nichter)（Z-Library）

# Efficient MySQL Performance: Best Practices and Techniques — Reading Guide ## 【One-Line Pitch】 A practical, mid-level guide for software engineers who need to make MySQL fast without becoming database administrators — covering query analysis, indexing strategy, and the metrics that actually matter for performance troubleshooting. ## 【Book Arc】 - **Opening (~0%–9%)**: Establishes the book's core philosophy — query response time is the "North Star" of MySQL performance — and sets up the reader's toolkit: query metrics, reporting, and analysis workflows that turn raw data into actionable insight. - **Early (~9%–28%)**: Dives deep into query response time as a measurable quantity, explaining query load, concurrency implications, lock time gotchas (Performance Schema vs. slow query log), and the critical distinction between direct and indirect query optimization. - **Early-to-Middle (~28%–38%)**: Introduces the "query optimization journey" framework, then pivots to why scaling up hardware is a trap — workload (queries + data + access patterns) is what actually determines whether faster hardware helps at all. - **Middle (~38%–47%)**: Delivers a visual, example-driven tour of MySQL indexes: leftmost prefix requirements, secondary index lookups, the "hidden" primary key appended to secondary indexes, and how GROUP BY and ORDER BY can leverage index ordering to avoid filesorts. - **Late (~47% onward)**: Extends into access patterns, sharding, and scale-out strategies — offloading reads, enqueueing writes, partitioning data, and the honest assessment of when you shouldn't use MySQL at all. ## 【Key Takeaways】 - **Query response time is the primary metric** (Early): All other performance indicators are secondary to how long queries actually take. Load — total query time divided by clock time — reveals concurrency: load above 1.0 means multiple instances of a query run simultaneously, and load above 10 is likely a slow query worth investigating. - **Lock time sources differ critically** (Early): Performance Schema lock time excludes row lock waits (only table and metadata locks), making it nearly useless for write-performance analysis. The slow query log includes all lock waits — use it when diagnosing write bottlenecks. - **Query profiles and reports serve different purposes** (Early): A query profile shows which queries are slowest at a glance; a query report is an organized information dump for deep analysis. Stability matters — unstable queries are harder to analyze because their execution varies, often due to factors outside MySQL. - **Direct optimization comes first** (Early): Changes to queries and indexes solve most performance problems and should be your first move. Indirect optimization (schema, hardware, configuration) is secondary — don't reach for it until direct methods are exhausted. - **Scaling up is a trap, not a strategy** (Early–Middle): Faster hardware doesn't change how the application uses MySQL — if the workload causes table scans, more memory won't help. Cloud scaling is expensive (RDS costs double per instance size) and unsustainable; optimization is the durable answer. - **Leftmost prefix is the index law** (Middle): To use an index, a query must use a leftmost prefix of it. Indexes (a, b) and (b, a) are different indexes with different leftmost prefixes — order matters fundamentally. - **The hidden primary key enables ORDER BY optimizations** (Middle): Every secondary index has the primary key appended at its rightmost position. This allows ORDER BY on the primary key to avoid filesorts when the secondary index's leftmost prefix conditions are met — a subtle but powerful optimization for real tables. - **Indexes implicitly group data** (Middle): GROUP BY on a leftmost prefix column can use the index's inherent ordering to identify groups without sorting — MySQL knows each new value starts a new group, eliminating expensive grouping operations. ## 【Reading Tips】 - **Skim the visual index examples** (Middle, ~38%–47%): The book uses a chemical elements table (elem) with figures showing index structures. These visuals are the clearest way to internalize leftmost prefixes and secondary index lookups — don't skip the figures even if the text feels repetitive. - **Deep-read the query metrics chapters** (Early, ~9%–28%): Query load, lock time sources, and the profile-vs-report distinction are foundational. Understanding these concepts makes every later chapter (indexing, sharding, access patterns) more actionable. - **Treat the "Practice" sections as checkpoints**: Each chapter ends with a practice exercise (e.g., "Identify Slow Queries," "Describe an Access Pattern"). Actually doing these — not just reading them — is where the book's value materializes. - **Watch for the gotchas**: The book deliberately highlights traps like Performance Schema lock time excluding row locks, and why you shouldn't use STRAIGHT_JOIN. These are the details that separate engineers who merely read about MySQL from those who can diagnose it. - **Skip the hardware discussion if you're cloud-native**: The scaling-up critique is useful context, but if you're already on managed cloud MySQL, focus on the query optimization and indexing chapters where the actionable content lives. ## 【Coverage Limits】 This guide covers the book's opening through the indexing chapters (~47% of the book). The excerpts do not cover the later chapters on access patterns, sharding strategies, replication, transactions, row locking, or cloud-specific performance considerations in detail — those sections are noted in the table of contents but not analyzed here. ##