https://hackmysql.com/learn/data-and-access-patterns/

1<!doctype html><html lang=en><head><meta charset=utf-8><meta http-equiv=X-UA-Compatible content="IE=edge"><meta name=viewport content="width=device-width,initial-scale=1"><title>Data and Access Patterns</title><meta property="og:title" content="Data and Access Patterns"><meta name=description content="Context
2This is why you need to learn about MySQL data storage and access patterns:
3
4Even when you master indexes and indexing, you will encounter queries that are simple and properly indexed but still slow.
5That’s when you begin to optimize around the query, starting with the data that it accesses.
6To understand why, let’s think about rocks.
7Imagine that your job is to move rocks, and you have three piles of different sized rocks.
8The first pile contains pebbles: very light, no larger than your thumbnail.
9The second pile contains cobbles: heavy but light enough to pick up, no larger than your head.
10The third pile contains boulders: too large and heavy to pick up; you need leverage or a machine to move them.
11Your job is to move one pile from the bottom of a hill to the top.
12Which pile do you choose?"><meta property="og:description" content="Context
13This is why you need to learn about MySQL data storage and access patterns:
14
15Even when you master indexes and indexing, you will encounter queries that are simple and properly indexed but still slow.
16That’s when you begin to optimize around the query, starting with the data that it accesses.
17To understand why, let’s think about rocks.
18Imagine that your job is to move rocks, and you have three piles of different sized rocks.
19The first pile contains pebbles: very light, no larger than your thumbnail.
20The second pile contains cobbles: heavy but light enough to pick up, no larger than your head.
21The third pile contains boulders: too large and heavy to pick up; you need leverage or a machine to move them.
22Your job is to move one pile from the bottom of a hill to the top.
23Which pile do you choose?"><meta name=author content="Daniel Nichter"><link rel=apple-touch-icon href=/img/apple-touch-icon.png><link rel=icon href=/img/hackmysql-favicon.svg?v2><link rel=manifest href=/site.webmanifest><meta property="og:url" content="https://hackmysql.com/learn/data-and-access-patterns/"><meta property="og:type" content="website"><meta property="og:site_name" content="Hack MySQL"><meta name=generator content="Hugo 0.161.1"><link rel=canonical href=https://hackmysql.com/learn/data-and-access-patterns/><link rel=alternate href=https://hackmysql.com/index.xml type=application/rss+xml title="Hack MySQL"><link rel=stylesheet href=https://hackmysql.com/hackmysql.css></head><body><nav id=menu><a href=/ aria-label="Hack MySQL home" style=color:hotpink>hack mysql>&nbsp;</a>&nbsp;<button onclick='location.href="/about/"'>2005&ndash;2025;</button></nav><div class="page path"><div id=bird class=path><div class=scroller><div class=pathwrap><p><a href=/path>Learn MySQL Performance</a></p><div class=path><div class=pi><div class="ps t"></div><div class=pl><a href=/learn/query-response-time/>1. Query Response Time</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/indexes-and-indexing/>2. Indexes and Indexing</a></div></div><div class=pi><div class="ps x"></div><div class=pl><a href=/learn/data-and-access-patterns/>3. Data and Access Patterns</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/sharding/>4. Sharding</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/server-metrics-and-innodb/>5. Server Metrics and InnoDB</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/replication/>6. Replication</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/transactions-and-data-locks/>7. Transactions and Data Locks</a></div></div><div class=pi><div class=ps></div><div class=pl><a href=/learn/common-challenges/>8. Common Challenges</a></div></div><div class=pi><div class="ps b"></div><div class=pl><a href=/learn/cloud-performance/>9. Cloud Performance</a></div></div></div></div><div style=margin-top:3em><img width=120 height=106 src=/img/emp-bird-2.png alt="Efficient MySQL Performance bird"><p><b><i>Efficient MySQL Performance</i></b><br>20 years of MySQL performance, written for software engineers</p><div class=book-buttons>

23<a class=book-button data-dst=oreilly href=https://oreil.ly/efficient-mysql-performance>O'Reilly&nbsp;&Rang;</a><br><a class=book-button data-dst=amazon href=https://www.amazon.com/Efficient-MySQL-Performance-Practices-Techniques/dp/1098105095/>Amazon &nbsp;&Rang;</a><br><a class=book-button data-dst=goodreads href=https://www.goodreads.com/book/show/59963702-efficient-mysql-performance>Goodreads&nbsp;&Rang;</a></div></div></div></div><main><article><h1 class=post-title>Data and Access Patterns</h1><h2 id=context>Context</h2><p>This is why you need to learn about MySQL data storage and access patterns:</p><div class=intro>Even when you master indexes and indexing, you will encounter queries that are simple and properly indexed but still slow.
24That’s when you begin to optimize <i>around</i> the query, starting with the data that it accesses.
25To understand why, let’s think about rocks.<p>Imagine that your job is to move rocks, and you have three piles of different sized rocks.
26The first pile contains pebbles: very light, no larger than your thumbnail.
27The second pile contains cobbles: heavy but light enough to pick up, no larger than your head.
28The third pile contains boulders: too large and heavy to pick up; you need leverage or a machine to move them.
29Your job is to move one pile from the bottom of a hill to the top.
30Which pile do you choose?</p><p>I presume that you choose the pebbles because they’re light and easy to move.
31But there’s a critical detail that might change your decision: weight.
32The pile of pebbles weighs two metric tons (the weight of a full-size SUV).
33The pile of cobbles weighs one metric ton (the weight of a very small car).
34And there’s only one boulder that weighs half a metric ton (the weight of ten adult humans).
35Now which pile do you choose?</p><p>On the one hand, the pebbles are still a lot easier to move.
36You can shovel them into a wheelbarrow and roll it up the hill.
37There’s just a lot of them (pebbles, not wheelbarrows).
38The boulder is a fraction of the weight, but its singular size makes it unwieldy.
39Special equipment is need to move it up the hill, but it’s a one-time task.
40Tough decision.</p><p>Data is analogous to a pile of rocks, and executing queries is analogous to moving the rocks uphill.
41When data size is small, direct query optimization is usually sufficient because the data is trivial to handle—like walking (or running) up a hill with a handful of pebbles.
42But as data size increases, indirect query optimization becomes increasingly important—like lugging a heavy cobble up a hill and stopping midway to ask, &ldquo;Can we do something about these rocks?&rdquo;</p><p>Chapter 1 provided a &ldquo;proof&rdquo; that data size affects performance: <code>TRUNCATE TABLE</code> dramatically increases performance—but <strong>do not</strong> use this &ldquo;optimization&rdquo;.
43It&rsquo;s just a joke, but it also proves a point that is not frequently followed through to its logical consequence: <em>less data is more performance</em>.
44You can improve performance by reducing data because less data requires less system resources (CPU, memory, storage, and so on).</p><p>But isn&rsquo;t <em>more</em> data the reality and reason that drives engineers to learn about performance optimization?
45Yes, and sharding addresses MySQL at scale, but first it’s imperative to learn to reduce and optimize data when it’s relatively small and problems are tractable.
46Ignoring data size until it’s crushing the application is the most stressful time to learn.</p><hr><p><em>Access patterns</em> describe how an application uses MySQL to access data.
47Changing access patterns has a powerful affect on MySQL performance, but it usually requires a greater level of effort than other optimizations.
48That’s why it’s the last leg of the journey: first optimize queries, indexes, and data—then optimize access patterns.
49Let’s think again about rocks.</p><p>Suppose you have a truck, which is analogous to MySQL.
50If used efficiently, the truck makes moving any pile of rocks uphill easy.
51But if used inefficiently, the truck provides little value, and it might even make the job take longer than necessary.
52For example, you could use the truck to haul the cobbles <em>one by one</em> up the hill.
53That’s easy for you (and the truck), but it’s terribly inefficient and time-consuming.
54A truck is only as useful as the person who uses it.
55Likewise, MySQL is only as useful as the application that uses it.</p><p>Sometimes, an engineer puzzles over why MySQL isn&rsquo;t running faster.
56For example, when MySQL is executing 5,000 QPS and the engineer wonders why it’s not executing 9,000 QPS instead.
57Or when MySQL is using 50% CPU and the engineer wonders why it’s not using 90% CPU instead.
58The engineer is unlikely to find an answer because they’re focused on the effect (MySQL) rather than the cause: the application.
59Metrics like QPS and CPU usage say very little—almost nothing—about MySQL; they only reflect how the application uses MySQL.</p><p>An application can outgrow the capacity of a <em>single</em> MySQL instance, but again: that says more about the application than MySQL because there are innumerable large, high-performance applications using a single MySQL instance.
60Without a doubt, MySQL is fast enough for the application.

61The real question is: does the application use MySQL efficiently?
62After many years with MySQL, hundreds of different applications, and thousands of different MySQL instances, I assure you: MySQL performance is limited by the application, not the other way around.</p><div class=src>Excerpt from <i><a href=https://oreil.ly/efficient-mysql-performance>Efficient MySQL Performance</a></i>, Chapter 3 and Chapter 4<br>Copyright 2021 Daniel Nichter<br>No reproduction of this excerpt without permission</div></div><h2 id=key-points>Key Points</h2><ul><li>Less data yields better performance</li><li>Less QPS is better because it’s a liability, not an asset</li><li>Indexes are necessary for maximum MySQL performance, but there are cases when indexes may not help</li><li>The <em>Principle of Least Data</em> means: store and access only needed data</li><li>Ensure that queries access as few rows as possible</li><li>Do not store more data than needed: data is valuable to you, but it’s dead weight to MySQL</li><li>Deleting or archiving data is important and improves performance</li><li>MySQL does nothing except execute application queries</li><li>No database can utilize 100% of hardware capacity</li><li>Data access patterns describe how an application uses a database</li><li>You must change the application to change its data access patterns</li><li>Scale up hardware to improve performance as the <em>last</em> solution</li></ul><h2 id=pitfalls>Pitfalls</h2><ul><li>Scaling up hardware before optimizing queries</li><li>Storing data without need or reason</li><li>Not using efficient column types or data representations</li><li>Not normalizing (and rarely: normalizing too much)</li><li>Selecting columns that the app doesn&rsquo;t use</li><li>Not limiting results when app only uses limited result</li><li>Sorting too much (instead of sorting in app if possible)</li><li>Not archiving old or unused data</li><li>Archiving rows too fast, in batches too large, or both</li><li>Thinking MySQL is slow instead of the application workload</li><li>Access patterns that don&rsquo;t work well on a transactional OLTP RDBMS</li></ul><h2 id=hack-mysql-articles>Hack MySQL Articles</h2><ul><li><a href=https://hackmysql.com/deferred-join-deep-dive/>Deferred Join: A Deep Dive</a></li><li><a href=https://hackmysql.com/mysql-iops-for-reads-and-surprsies/>MySQL IOPS for Reads and Surprsies</a></li><li><a href=https://hackmysql.com/book-4/>Access Patterns for MySQL</a></li><li><a href=https://hackmysql.com/book-3/>Performance Is Less</a></li></ul><div class=print-copyright>Source: https://hackmysql.com/learn/data-and-access-patterns/<br>Copyright 2025 Daniel Nichter</div></article></main><nav id=toc class=path><div class=scroller><em><a href=#>Data and Access Patterns</a></em><hr><nav id=TableOfContents><ul><li><a href=#context>Context</a></li><li><a href=#key-points>Key Points</a></li><li><a href=#pitfalls>Pitfalls</a></li><li><a href=#hack-mysql-articles>Hack MySQL Articles</a></li></ul></nav></div></nav></div><footer><p>/*&nbsp;All articles written, edited, and copyright 2005&ndash;2025 <a href=https://github.com/daniel-nichter style=text-decoration:underline>Daniel Nichter</a>&nbsp;*/</p></footer>

62<script src=/hackmysql.js></script>

62<script src=https://cdn.usefathom.com/script.js data-site=WJIYGVMB data-canonical=false defer></script>

62<script>window.addEventListener("load",e=>{document.querySelectorAll(".book-button").forEach(e=>{e.addEventListener("click",t=>{fathom.trackEvent("book "+e.getAttribute("data-dst")+" "+window.location.pathname)})})})</script>

62</body></html>