<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by D.Tadpole on Medium]]></title>
        <description><![CDATA[Stories by D.Tadpole on Medium]]></description>
        <link>https://medium.com/@dtadpole?source=rss-22d443a5434b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*boAbwtWHxz5viO4CADis0A.jpeg</url>
            <title>Stories by D.Tadpole on Medium</title>
            <link>https://medium.com/@dtadpole?source=rss-22d443a5434b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 05:22:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dtadpole/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Optimizing MySQL Query Plan for Table with Large JSON Attributes]]></title>
            <link>https://medium.com/@dtadpole/optimizing-mysql-query-plan-for-table-with-large-json-attributes-cdb586e60be1?source=rss-22d443a5434b------2</link>
            <guid isPermaLink="false">https://medium.com/p/cdb586e60be1</guid>
            <dc:creator><![CDATA[D.Tadpole]]></dc:creator>
            <pubDate>Sat, 09 Apr 2022 10:49:26 GMT</pubDate>
            <atom:updated>2022-04-10T20:20:47.330Z</atom:updated>
            <content:encoded><![CDATA[<p>MySQL introduced JSON support starting from version 5.7.</p><p>JSON is a powerful data type that simplifies MySQL schema maintenance and significantly reduces schema changes in many production use cases. JSON provides <em>Weak Type</em> support for applications using MySQL.</p><p>When deployed for a critical use case, we found that MySQL query plan is <strong>not optimal</strong> when a table contains large JSON attributes, and when a query requires table scan of non-JSON columns.</p><p>The specific <em>where</em> clause for the query does not contain the JSON column, and should not require either reading or decoding of the JSON column to satisfy the query predicate. Furthermore, large JSON objects are stored as BLOBs at overflow storage space when a table row size is larger than 8KB (<a href="https://dev.mysql.com/blog-archive/how-large-can-json-documents-be/">link</a>). However, the observed query time is significantly longer than expected with high Database CPU usage. To understand such high CPU usage and longer-than-expected query time, following tests were performed to uncover what is happening inside the MySQL query engine.</p><p>Below is a simple MySQL table with JSON attribute:</p><pre>CREATE TABLE IF NOT EXISTS events (<br>  id int AUTO_INCREMENT PRIMARY KEY,<br>  event_name VARCHAR(255),<br>  event_type VARCHAR(255),<br>  event_props JSON<br>);</pre><p>Next, create the following <em>generate_sql.sh</em> script to generate the mock data with SQL batch insert statements.</p><pre>#!/bin/bash</pre><pre>if [ &quot;x$1&quot; = &quot;x&quot; ]; then<br>  echo &quot;USAGE: $0 &lt;rows&gt;&quot;<br>  exit 1<br>fi</pre><pre>ROWS=$1</pre><pre>echo &quot;INSERT INTO events (event_name, event_type, event_props) &quot;<br>echo &quot;VALUES &quot;<br>RECORD_NUM=1<br>while [ $RECORD_NUM -le $ROWS ]; do<br>  echo &quot;(&quot;<br>  echo &quot;  &#39;event-name-$RANDOM&#39;, &quot;<br>  echo &quot;  &#39;event-type-$RECORD_NUM&#39;, &quot;<br>  echo &quot;  &#39;{ \\&quot;<br>  i=1<br>  while [ $i -lt $RECORD_NUM ]; do<br>    echo &quot;        \&quot;name-$i\&quot;: \&quot;browser-$i\&quot;, \&quot;os-$i\&quot;: \&quot;operating-system-$RANDOM\&quot;, \&quot;resolution-$RANDOM\&quot;: { \&quot;x$i\&quot;: $RANDOM, \&quot;y$i\&quot;: $RANDOM  }, \\&quot;<br>    i=$(($i+1))<br>  done<br>  echo &quot;        \&quot;name-$i\&quot;: \&quot;browser-$i\&quot;, \&quot;os-$i\&quot;: \&quot;operating-system-$RANDOM\&quot;, \&quot;resolution-$RANDOM\&quot;: { \&quot;x$i\&quot;: $RANDOM, \&quot;y$i\&quot;: $RANDOM }   \\&quot;<br>  echo &quot;  }&#39; &quot;<br>  RECORD_NUM=$(($RECORD_NUM + 1))<br>  if [ $RECORD_NUM -eq $ROWS ]; then<br>    echo &#39;);&#39;<br>  else<br>    echo &#39;),&#39;<br>  fi<br>done</pre><p>Run the <em>generate_sql.sh</em> script. It generates a batch insert SQL with varied JSON attribute sizes.</p><pre>$ ./generate_sql.sh 3</pre><pre>INSERT INTO events (event_name, event_type, event_props)<br>VALUES<br>(<br>  &#39;event-name-3847&#39;, <br>  &#39;event-type-1&#39;,<br>  &#39;{ \<br>    &quot;name-1&quot;: &quot;browser-1&quot;, &quot;os-1&quot;: &quot;operating-system-19298&quot;, &quot;resolution-16116&quot;: { &quot;x1&quot;: 6731, &quot;y1&quot;: 29276 }  \<br>   }&#39;<br>),<br>(<br>  &#39;event-name-13804&#39;,<br>  &#39;event-type-2&#39;,<br>  &#39;{ \<br>    &quot;name-1&quot;: &quot;browser-1&quot;, &quot;os-1&quot;: &quot;operating-system-10236&quot;, &quot;resolution-16556&quot;: { &quot;x1&quot;: 7432, &quot;y1&quot;: 31911 }, \<br>    &quot;name-2&quot;: &quot;browser-2&quot;, &quot;os-2&quot;: &quot;operating-system-20742&quot;, &quot;resolution-6657&quot;: { &quot;x2&quot;: 27002, &quot;y2&quot;: 19783 }  \<br>   }&#39;<br>),<br>(<br>  &#39;event-name-12068&#39;,<br>  &#39;event-type-3&#39;,<br>  &#39;{ \<br>    &quot;name-1&quot;: &quot;browser-1&quot;, &quot;os-1&quot;: &quot;operating-system-4666&quot;, &quot;resolution-21609&quot;: { &quot;x1&quot;: 17458, &quot;y1&quot;: 26593 }, \<br>    &quot;name-2&quot;: &quot;browser-2&quot;, &quot;os-2&quot;: &quot;operating-system-27043&quot;, &quot;resolution-19747&quot;: { &quot;x2&quot;: 24575, &quot;y2&quot;: 143 }, \<br>    &quot;name-3&quot;: &quot;browser-3&quot;, &quot;os-3&quot;: &quot;operating-system-17536&quot;, &quot;resolution-21091&quot;: { &quot;x3&quot;: 3591, &quot;y3&quot;: 32762 }  \<br>   }&#39;<br>);</pre><p>Next, generate a batch load SQL with 1000 records, then run batch load for 50 times. Total is 50,000 records.</p><pre>$ ./generate_sql.sh 1000 &gt; /tmp/events.sql</pre><pre>$ i=0<br>$ while [ $i -lt 50 ]; do<br>    echo $i<br>    mysql &lt; /tmp/events.sql<br>    i=$(($i+1))<br>  done</pre><pre>$ mysql -e &#39;select count(*) from events&quot;<br>50000</pre><p>Now, run a query and select <em>id</em>, <em>event_name</em>, and <em>event_type</em> columns with <em>where</em> clause on <em>event_name</em>:</p><pre>$ time mysql -e &#39;select id, event_name, event_type from events where event_name like &quot;event-name-1283%&quot;&#39; | wc -l<br>51<br>real 0m0.040s<br>user 0m0.007s<br>sys  0m0.003s</pre><p>Next, run a query and select <em>all</em> columns with same <em>where</em> clause on <em>event_name</em>:</p><pre>$ time mysql -e &#39;select * from events where event_name like &quot;event-name-1283%&quot;&#39; | wc -l<br>51<br>real 1m30.113s<br>user 0m0.051s<br>sys  0m0.018s</pre><p>Note both of the above queries require full table scan on <em>event_name</em>, because the <em>event_name</em> column in the <em>where</em> clause is not indexed.</p><p>Comparing these two queries: the first query took less than 1 second, while the 2nd query took 1 minute 30 seconds, which is significantly slower than the first query.</p><p>Theoretically, the 2nd query can be executed by running query on <em>event_name</em> column to select the specific row IDs that match the predicate. After retrieving the 50 row IDs, the 2nd query can then decode the 50 JSON objects and return the result set.</p><p>However, the computation required to decode 50 JSON objects cannot explain the significant performance difference between these two queries, nor the high database CPU utilization during the 1 minute 30 seconds query time. A likely explanation is the 2nd query may have decoded <strong>all 50,000 JSON objects</strong> in the entire table. i.e. the MySQL query plan for the 2nd query is <strong>not optimal</strong>.</p><p>To verify the above theory, a re-composed query is designed to explicitly override the default query plan:</p><pre>$ time mysql -e &#39;select * from events where id in (select id from events where event_name like &quot;event-name-1283%&quot;)&#39; | wc -l<br>51<br>real 0m0.243s<br>user 0m0.051s<br>sys  0m0.014s</pre><p>Note the output of the 3rd query includes all columns, same as the output of the 2nd query. In the meantime, the 3rd query performance is significantly better than the 2nd query.</p><p>By re-composing the SQL statement, we have overridden the default MySQL query plan and <strong>improved query performance more than 200x</strong>.</p><p>The above examples are tested on AWS RDS, with following environment:</p><pre>AWS RDS<br>MySQL: 8.0.28<br>Instance class: db.t3.micro<br>vCPU: 2<br>RAM: 1 GB</pre><pre>All balances are available (CPU, EBS IO, etc)</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cdb586e60be1" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>