{"id":3590,"date":"2022-10-14T07:15:14","date_gmt":"2022-10-14T07:15:14","guid":{"rendered":"http:\/\/www.randomnoun.com\/wp\/?p=3590"},"modified":"2023-12-27T02:19:32","modified_gmt":"2023-12-27T02:19:32","slug":"make-a-new-plan-stan","status":"publish","type":"post","link":"https:\/\/www.randomnoun.com\/wp\/2022\/10\/14\/make-a-new-plan-stan\/","title":{"rendered":"Make a new plan, Stan"},"content":{"rendered":"<p>I&#8217;ve produced some more software.<\/p>\n<p>This one <b>creates diagrams of query execution plans<\/b> for MySQL.<\/p>\n<p><!--more--><\/p>\n<pre style=\"display:none\"><style>\r\nTABLE.sqlTable { margin-bottom: 0px; }\r\nTABLE.sqlTable TD,\r\nTABLE.sqlTable TH {\r\n  white-space: nowrap; \r\n  padding: 3px; \r\n  font-family: Arial;\r\n  font-size: 12px;\r\n  border: solid 1px #e2e2e2;\r\n  text-align: left;\r\n}\r\n<\/style><\/pre>\n<p><script>\nfunction initSqlExplain2() {\n    iframe = document.getElementById('sqlIframe');\n    iframe.contentWindow.scrollTo(420, 0);\n    svgRoot = iframe.contentWindow.document;\n    html = svgRoot.getElementsByTagName('html')[0];\n    svg = svgRoot.getElementsByTagName('svg')[0];\n    \/\/svg = e.target.ownerDocument;\n    \/\/svgRoot = e.target; \/\/ svg.documentElement\n    \/\/var tooltip = svg.querySelector('g.tooltip');\n    \/\/var tooltipFo = tooltip.getElementsByTagName('foreignObject')[0];\n    \/\/var tooltipDiv = tooltipFo.getElementsByTagName('div')[0];\n    var tooltipDiv = document.getElementById('tooltip2');\n    var triggers = svg.querySelectorAll('[data-tooltip-html]');\n    for (var i = 0; i < triggers.length; i++) {\n        triggers[i].addEventListener('mousemove', showTooltip);\n        triggers[i].addEventListener('mouseout', hideTooltip);\n    }\n    function showTooltip(evt) {\n        var ctm = svg.getScreenCTM();\n        var x = (evt.clientX - ctm.e + 6) \/ ctm.a;\n        var y = (evt.clientY - ctm.f + 20) \/ ctm.d;\n        \/\/tooltip.setAttributeNS(null, 'transform', 'translate(' + x + ' ' + y + ')');\n        \/\/tooltip.setAttributeNS(null, 'visibility', 'visible');\n        var leftPos = iframe.offsetLeft - html.scrollLeft + x + 20;\n        var topPos = iframe.offsetTop - html.scrollTop + y + 15;\n        tooltipDiv.setAttribute('style', 'visibility: visible; left: ' + leftPos + 'px; top: ' + topPos + 'px;');\n        tooltipDiv.innerHTML = evt.target.getAttributeNS(null, 'data-tooltip-html');\n    }\n    function hideTooltip(evt) {\n        tooltipDiv.setAttribute('style', '');\n    }\n}\n<\/script><\/p>\n<pre style=\"display:none\"><style>\r\ndiv#tooltip2 {\r\n        position: absolute; visibility: hidden;\r\n        font-family: Arial;\r\n        font-size: 11px;\r\n        padding: 4px;\r\n        white-space: pre;\r\n        display: inline-block;\r\n        background: white;\r\n        border: 1px solid black;\r\n        box-shadow: 2px 2px 2px rgba(0, 0, 0, 0.2);\r\n        line-height: 1.2;\r\n}\r\ndiv#tooltip2 .keyIndexHeader,\r\ndiv#tooltip2 .costInfoHeader,\r\ndiv#tooltip2 .groupingOperationHeader,\r\ndiv#tooltip2 .orderingOperationHeader,\r\ndiv#tooltip2 .attachedConditionHeader,\r\ndiv#tooltip2 .nestedLoopHeader {\r\n        font-size: 11px;\r\n        font-weight: bold;\r\n}\r\n<\/style><\/pre>\n<div id=\"tooltip2\"><\/div>\n<p><iframe loading=\"lazy\" id=\"sqlIframe\" width=\"100%\" height=\"500px\" src=\"\/wpf\/sql\/sakila-7g-attribute.html\" onload=\"initSqlExplain2()\"><\/iframe><\/p>\n<p>^ There's tooltips on the things that have tooltips.<\/p>\n<h2>Explain that to me like I know what SQL is<\/h2>\n<p>You can get a query execution plan table from vanilla MySQL by entering <code>EXPLAIN<\/code> before the query, which looks a bit like this:<\/p>\n<p><b>Input:<\/b><\/p>\n<pre style=\"height: 200px; overflow-y: scroll; padding: 10px !important;\">EXPLAIN select A.*, B.sales \r\nfrom (\r\n    select sto.store_id, cit.city, cou.country\r\n    from store sto\r\n    left join address adr on sto.address_id = adr.address_id\r\n    join city cit on adr.city_id = cit.city_id\r\n    join country cou on cit.country_id = cou.country_id\r\n) A\r\njoin (\r\n    select cus.store_id, sum(pay.amount) sales\r\n    from customer cus\r\n    join payment pay on pay.customer_id = cus.customer_id\r\n    group by cus.store_id\r\n) B\r\non A.store_id = B.store_id\r\norder by A.store_id;\r\n<\/pre>\n<p><b>Output:<\/b><\/p>\n<div style=\"width:100%; overflow-x: scroll; margin-bottom: 20px;\">\n<table class=\"sqlTable\">\n<tbody>\n<tr>\n<th>id<\/th>\n<th>select_type<\/th>\n<th>table<\/th>\n<th>partitions<\/th>\n<th>type<\/th>\n<th>possible_keys<\/th>\n<th>key<\/th>\n<th>key_len<\/th>\n<th>ref<\/th>\n<th>rows<\/th>\n<th>filtered<\/th>\n<th>Extra<\/th>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>PRIMARY<\/td>\n<td>sto<\/td>\n<td><\/td>\n<td>index<\/td>\n<td>PRIMARY,idx_fk_address_id<\/td>\n<td>PRIMARY<\/td>\n<td>1<\/td>\n<td><\/td>\n<td>2<\/td>\n<td>100.00<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>PRIMARY<\/td>\n<td>adr<\/td>\n<td><\/td>\n<td>eq_ref<\/td>\n<td>PRIMARY,idx_fk_city_id<\/td>\n<td>PRIMARY<\/td>\n<td>2<\/td>\n<td>sakila.sto.address_id<\/td>\n<td>1<\/td>\n<td>100.00<\/td>\n<td>Using where<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>PRIMARY<\/td>\n<td>cit<\/td>\n<td><\/td>\n<td>eq_ref<\/td>\n<td>PRIMARY,idx_fk_country_id<\/td>\n<td>PRIMARY<\/td>\n<td>2<\/td>\n<td>sakila.adr.city_id<\/td>\n<td>1<\/td>\n<td>100.00<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>PRIMARY<\/td>\n<td>cou<\/td>\n<td><\/td>\n<td>eq_ref<\/td>\n<td>PRIMARY<\/td>\n<td>PRIMARY<\/td>\n<td>2<\/td>\n<td>sakila.cit.country_id<\/td>\n<td>1<\/td>\n<td>100.00<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>PRIMARY<\/td>\n<td><derived3><\/derived3><\/td>\n<td><\/td>\n<td>ref<\/td>\n<td><auto_key0><\/auto_key0><\/td>\n<td><auto_key0><\/auto_key0><\/td>\n<td>1<\/td>\n<td>sakila.sto.store_id<\/td>\n<td>160<\/td>\n<td>100.00<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>DERIVED<\/td>\n<td>cus<\/td>\n<td><\/td>\n<td>index<\/td>\n<td>PRIMARY,idx_fk_store_id<\/td>\n<td>idx_fk_store_id<\/td>\n<td>1<\/td>\n<td><\/td>\n<td>599<\/td>\n<td>100.00<\/td>\n<td>Using index<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>DERIVED<\/td>\n<td>pay<\/td>\n<td><\/td>\n<td>ref<\/td>\n<td>idx_fk_customer_id<\/td>\n<td>idx_fk_customer_id<\/td>\n<td>2<\/td>\n<td>sakila.cus.customer_id<\/td>\n<td>26<\/td>\n<td>100.00<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>And if you want a bit more detail, you can put <code>EXPLAIN FORMAT=JSON<\/code> in there, which gives it a bit more structure, and adds a heap of other useful information.<\/p>\n<p><b>Output:<\/b><\/p>\n<pre style=\"height: 400px; overflow-y: scroll; padding: 10px !important;\">{\r\n  \"query_block\": {\r\n    \"select_id\": 1,\r\n    \"cost_info\": {\r\n      \"query_cost\": \"120.39\"\r\n    },\r\n    \"ordering_operation\": {\r\n      \"using_filesort\": false,\r\n      \"nested_loop\": [\r\n        {\r\n          \"table\": {\r\n            \"table_name\": \"sto\",\r\n            \"access_type\": \"index\",\r\n            \"possible_keys\": [\r\n              \"PRIMARY\",\r\n              \"idx_fk_address_id\"\r\n            ],\r\n            \"key\": \"PRIMARY\",\r\n            \"used_key_parts\": [\r\n              \"store_id\"\r\n            ],\r\n            \"key_length\": \"1\",\r\n            \"rows_examined_per_scan\": 2,\r\n            \"rows_produced_per_join\": 2,\r\n            \"filtered\": \"100.00\",\r\n            \"cost_info\": {\r\n              \"read_cost\": \"1.00\",\r\n              \"eval_cost\": \"0.20\",\r\n              \"prefix_cost\": \"1.20\",\r\n              \"data_read_per_join\": \"32\"\r\n            },\r\n            \"used_columns\": [\r\n              \"store_id\",\r\n              \"address_id\"\r\n            ]\r\n          }\r\n        },\r\n        {\r\n          \"table\": {\r\n            \"table_name\": \"adr\",\r\n            \"access_type\": \"eq_ref\",\r\n            \"possible_keys\": [\r\n              \"PRIMARY\",\r\n              \"idx_fk_city_id\"\r\n            ],\r\n            \"key\": \"PRIMARY\",\r\n            \"used_key_parts\": [\r\n              \"address_id\"\r\n            ],\r\n            \"key_length\": \"2\",\r\n            \"ref\": [\r\n              \"sakila.sto.address_id\"\r\n            ],\r\n            \"rows_examined_per_scan\": 1,\r\n            \"rows_produced_per_join\": 2,\r\n            \"filtered\": \"100.00\",\r\n            \"cost_info\": {\r\n              \"read_cost\": \"2.00\",\r\n              \"eval_cost\": \"0.20\",\r\n              \"prefix_cost\": \"3.40\",\r\n              \"data_read_per_join\": \"1K\"\r\n            },\r\n            \"used_columns\": [\r\n              \"address_id\",\r\n              \"city_id\"\r\n            ],\r\n            \"attached_condition\": \"(`sakila`.`adr`.`city_id` is not null)\"\r\n          }\r\n        },\r\n        {\r\n          \"table\": {\r\n            \"table_name\": \"cit\",\r\n            \"access_type\": \"eq_ref\",\r\n            \"possible_keys\": [\r\n              \"PRIMARY\",\r\n              \"idx_fk_country_id\"\r\n            ],\r\n            \"key\": \"PRIMARY\",\r\n            \"used_key_parts\": [\r\n              \"city_id\"\r\n            ],\r\n            \"key_length\": \"2\",\r\n            \"ref\": [\r\n              \"sakila.adr.city_id\"\r\n            ],\r\n            \"rows_examined_per_scan\": 1,\r\n            \"rows_produced_per_join\": 2,\r\n            \"filtered\": \"100.00\",\r\n            \"cost_info\": {\r\n              \"read_cost\": \"2.00\",\r\n              \"eval_cost\": \"0.20\",\r\n              \"prefix_cost\": \"5.60\",\r\n              \"data_read_per_join\": \"432\"\r\n            },\r\n            \"used_columns\": [\r\n              \"city_id\",\r\n              \"city\",\r\n              \"country_id\"\r\n            ]\r\n          }\r\n        },\r\n        {\r\n          \"table\": {\r\n            \"table_name\": \"cou\",\r\n            \"access_type\": \"eq_ref\",\r\n            \"possible_keys\": [\r\n              \"PRIMARY\"\r\n            ],\r\n            \"key\": \"PRIMARY\",\r\n            \"used_key_parts\": [\r\n              \"country_id\"\r\n            ],\r\n            \"key_length\": \"2\",\r\n            \"ref\": [\r\n              \"sakila.cit.country_id\"\r\n            ],\r\n            \"rows_examined_per_scan\": 1,\r\n            \"rows_produced_per_join\": 2,\r\n            \"filtered\": \"100.00\",\r\n            \"cost_info\": {\r\n              \"read_cost\": \"2.00\",\r\n              \"eval_cost\": \"0.20\",\r\n              \"prefix_cost\": \"7.80\",\r\n              \"data_read_per_join\": \"416\"\r\n            },\r\n            \"used_columns\": [\r\n              \"country_id\",\r\n              \"country\"\r\n            ]\r\n          }\r\n        },\r\n        {\r\n          \"table\": {\r\n            \"table_name\": \"B\",\r\n            \"access_type\": \"ref\",\r\n            \"possible_keys\": [\r\n              \"<auto_key0>\"\r\n            ],\r\n            \"key\": \"<auto_key0>\",\r\n            \"used_key_parts\": [\r\n              \"store_id\"\r\n            ],\r\n            \"key_length\": \"1\",\r\n            \"ref\": [\r\n              \"sakila.sto.store_id\"\r\n            ],\r\n            \"rows_examined_per_scan\": 160,\r\n            \"rows_produced_per_join\": 321,\r\n            \"filtered\": \"100.00\",\r\n            \"cost_info\": {\r\n              \"read_cost\": \"80.42\",\r\n              \"eval_cost\": \"32.17\",\r\n              \"prefix_cost\": \"120.39\",\r\n              \"data_read_per_join\": \"7K\"\r\n            },\r\n            \"used_columns\": [\r\n              \"store_id\",\r\n              \"sales\"\r\n            ],\r\n            \"materialized_from_subquery\": {\r\n              \"using_temporary_table\": true,\r\n              \"dependent\": false,\r\n              \"cacheable\": true,\r\n              \"query_block\": {\r\n                \"select_id\": 3,\r\n                \"cost_info\": {\r\n                  \"query_cost\": \"17759.50\"\r\n                },\r\n                \"grouping_operation\": {\r\n                  \"using_filesort\": false,\r\n                  \"nested_loop\": [\r\n                    {\r\n                      \"table\": {\r\n                        \"table_name\": \"cus\",\r\n                        \"access_type\": \"index\",\r\n                        \"possible_keys\": [\r\n                          \"PRIMARY\",\r\n                          \"idx_fk_store_id\"\r\n                        ],\r\n                        \"key\": \"idx_fk_store_id\",\r\n                        \"used_key_parts\": [\r\n                          \"store_id\"\r\n                        ],\r\n                        \"key_length\": \"1\",\r\n                        \"rows_examined_per_scan\": 599,\r\n                        \"rows_produced_per_join\": 599,\r\n                        \"filtered\": \"100.00\",\r\n                        \"using_index\": true,\r\n                        \"cost_info\": {\r\n                          \"read_cost\": \"5.00\",\r\n                          \"eval_cost\": \"59.90\",\r\n                          \"prefix_cost\": \"64.90\",\r\n                          \"data_read_per_join\": \"341K\"\r\n                        },\r\n                        \"used_columns\": [\r\n                          \"customer_id\",\r\n                          \"store_id\"\r\n                        ]\r\n                      }\r\n                    },\r\n                    {\r\n                      \"table\": {\r\n                        \"table_name\": \"pay\",\r\n                        \"access_type\": \"ref\",\r\n                        \"possible_keys\": [\r\n                          \"idx_fk_customer_id\"\r\n                        ],\r\n                        \"key\": \"idx_fk_customer_id\",\r\n                        \"used_key_parts\": [\r\n                          \"customer_id\"\r\n                        ],\r\n                        \"key_length\": \"2\",\r\n                        \"ref\": [\r\n                          \"sakila.cus.customer_id\"\r\n                        ],\r\n                        \"rows_examined_per_scan\": 26,\r\n                        \"rows_produced_per_join\": 16085,\r\n                        \"filtered\": \"100.00\",\r\n                        \"cost_info\": {\r\n                          \"read_cost\": \"16086.00\",\r\n                          \"eval_cost\": \"1608.60\",\r\n                          \"prefix_cost\": \"17759.50\",\r\n                          \"data_read_per_join\": \"377K\"\r\n                        },\r\n                        \"used_columns\": [\r\n                          \"payment_id\",\r\n                          \"customer_id\",\r\n                          \"amount\"\r\n                        ]\r\n                      }\r\n                    }\r\n                  ]\r\n                }\r\n              }\r\n            }\r\n          }\r\n        }\r\n      ]\r\n    }\r\n  }\r\n}\r\n<\/auto_key0><\/auto_key0><\/pre>\n<p>Interpretting what that actually means is pretty difficult, so the makers of MySQL created a thingamajig inside <a href=\"https:\/\/www.mysql.com\/products\/workbench\/\">MySQL Workbench<\/a> to visualise that explanation, which creates diagrams like this:<\/p>\n<p><a href=\"https:\/\/www.randomnoun.com\/wp\/wp-content\/uploads\/2022\/10\/sakila-7g.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.randomnoun.com\/wp\/wp-content\/uploads\/2022\/10\/sakila-7g.png\" alt=\"sakila-7g\" width=\"1049\" height=\"679\" class=\"aligncenter size-full wp-image-3593\"><\/a><\/p>\n<p>Which is a bit easier to handle, as it shows you when your indexes are being used ( the green boxes ) and when they're not ( the red boxes ).<\/p>\n<h2>Well what's the problem then ?<\/h2>\n<p>This is all well and good, but unfortunately:<\/p>\n<ul>\n<li> MySQL Workbench is a desktop app, which makes it a little bit difficult to create these diagrams on demand inside your web application when it hits a particularly slow query.<\/li>\n<li> MySQL Workbench is <a href=\"https:\/\/www.gnu.org\/philosophy\/gpl-american-way.en.html\">GPL licensed<\/a>, so if you felt like reusing that code in another application that isn't a desktop app, you'll have to disclose the source code to that application, due to Eric Stallman getting a <a href=\"http:\/\/www.catb.org\/~esr\/writings\/cathedral-bazaar\/\">hissy fit with AT&amp;T<\/a> back in the 80s. A lot of people don't want to do that.<\/li>\n<li> It doesn't handle quite a few common SQL constructs<\/li>\n<\/ul>\n<p>So here's a thing I've dubbed the <b>SqlExplainerator<\/b>, which does the same sort of thing, but:<\/p>\n<ul>\n<li> it's a Java library, which you might find easier to invoke programmatically<\/li>\n<li> there's a CLI as well, which you might find easier to invoke programmatically from languages that aren't Java<\/li>\n<li> it generates SVG or HTML, some of which can be styled with CSS<\/li>\n<li> it's <a href=\"https:\/\/en.wikipedia.org\/wiki\/BSD_licenses#2-clause_license_(%22Simplified_BSD_License%22_or_%22FreeBSD_License%22)\">BSD licensed<\/a><\/li>\n<li> it handles a few more SQL constructs<\/li>\n<li> if you want to intentionally cripple it to look more like mysql workbench, there's also a 'workbench-compatible' mode which will prevent it from handling anything workbench doesn't handle.\n<ul>\n<li> and because there's a pretty good chance that at some stage in the future MySQL workbench will start supporting those constructs, I've separated the bit that does my own little extrapolation of JSON into the visual realm into a separate layout algorithm. That way I can 'fix' the code to mimic workbench again if and when they start supporting those constructs later on.<\/li>\n<\/ul>\n<\/li>\n<li> it has three flavours of tooltips. Four if you include not having tooltips at all.<\/li>\n<\/ul>\n<h2>So what sorts of things does this thing handle which workbench doesn't<\/h2>\n<ul>\n<li> HAVING clauses<\/li>\n<li> INSERT INTO queries<\/li>\n<li> CTEs that are used more than once<\/li>\n<li> window functions<\/li>\n<\/ul>\n<h2>Four flavours of tooltips ?<\/h2>\n<p>Oh yes. You've got<\/p>\n<ul>\n<li> SVG title elements, when you need to embed the SVG somewhere that doesn't allow javascript<\/li>\n<li> SVG + javascript, if you're able to include javascript inside the SVG, which then uses foreignObjects to format things a bit nicer<\/li>\n<li> SVG + attributes, which is similar to SVG + javascript but without the javascript. You can BYO your own javascript to draw the tooltips instead.<\/li>\n<li> no tooltips whatsoever.<\/li>\n<\/ul>\n<h2>Give me a rundown of the sort of things it can do:<\/h2>\n<p>The CLI gives you a rundown of the sorts of things it can do:<\/p>\n<pre style=\"height: 400px; overflow-y: scroll; padding: 10px !important;\">C:\\util\\java&gt; java -jar sql-explainerator-0.0.1-with-dependencies.jar --help\r\nusage: SqlExplaineratorCli [options]\r\n -h,--help                  This usage text\r\n -i,--infile &lt;infile&gt;       input file, or '-' for stdin; default = stdin\r\n -o,--outfile &lt;outfile&gt;     output file, or '-' for stdout; default = stdout\r\n -l,--layout &lt;layout&gt;       layout format (workbench or explainerator); default = explainerator\r\n -f,--format &lt;format&gt;       output format (svg or html); default = svg\r\n -t,--tooltip &lt;tooltip&gt;     tooltip type (none, title, attribute, javascript); default = title\r\n -j,--jdbc &lt;jdbc&gt;           JDBC connection string\r\n -u,--username &lt;username&gt;   JDBC username\r\n -p,--password &lt;password&gt;   JDBC password\r\n -d,--driver &lt;driver&gt;       JDBC driver class name; default = org.mariadb.jdbc.Driver\r\n -q,--sql &lt;sql&gt;             SQL to explain\r\n -c,--css &lt;css&gt;             alternate css file\r\n -s,--script &lt;script&gt;       alternate javascript file\r\n\r\nThis command will convert a MySQL JSON execution plan into an SVG diagram.\r\nThere are two layout methods: 'workbench' which will try to mimic the diagrams generated from MySQL\r\nWorkbench, or 'explainerator', which adds support for inserts, 'having' clauses, and window functions.\r\n\r\nThe execution plan can be supplied via stdin or --infile (Example 1), or can be retrieved from a\r\nMySQL server (Example 2).\r\n\r\nExample 1: To generate the query plan JSON, execute an 'EXPLAIN FORMAT=JSON' statement:\r\n\r\n  mysql --user=root --password=abc123 --silent --raw --skip-column-names \\\r\n    --execute \"EXPLAIN FORMAT=JSON SELECT 1 FROM DUAL\" sakila &gt; plan.json\r\n\r\nthen to generate the SVG diagram, supply this JSON as input to SqlExplaineratorCli:\r\n\r\n  SqlExplaineratorCli --infile plan.json --outfile plan.svg\r\nor\r\n  cat plan.json | SqlPlainToImageCli &gt; plan.svg\r\n\r\n\r\nExample 2: To generate the diagram from an SQL statement, you will need to also supply a JDBC\r\nconnection string and any credentials required to connect, e.g.:\r\n\r\n  SqlExplaineratorCli --jdbc jdbc:mysql:\/\/localhost\/sakila --username root --password abc123 \\\r\n    --sql \"SELECT 1 fROM DUAL\" --outfile plan.svg\r\n<\/pre>\n<p>There's an example of the type of diagram it produces at the top of this post.<\/p>\n<h2>Is there anything it can't do ?<\/h2>\n<p>Probably. There's <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/a246bad76b9271cb4333634e954040a970222e0a\/sql\/opt_explain_json.cc#L102\">quite a few things<\/a> that MySQL can generate in its plan JSON that it still doesn't process, but I haven't encountered those yet in any real-world queries.<\/p>\n<h2>Where it's at<\/h2>\n<p>It's up on github, and if you're using maven it's got the <code>artifactId:groupId<\/code> co-ordinates of <code>com.randomnoun.db:sql-explainerator<\/code><\/p>\n<p>Here's some links to it.<\/p>\n<p>The github link has a few more examples, including comparisons between this thing and Workbench.<\/p>\n<p><a href=\"https:\/\/github.com\/randomnoun\/sql-explainerator\"><\/p>\n<div class=\"github-button nobr\">\n <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.randomnoun.com\/wp\/wp-content\/uploads\/2020\/12\/github-42.png\" width=\"42\" height=\"42\" style=\"margin: 5px; float:left;\"><\/p>\n<div class=\"big\">sql-explainerator<\/div>\n<div class=\"small\">git@github.com:randomnoun\/sql-explainerator.git<\/div>\n<\/div>\n<p><\/a><\/p>\n<p><a href=\"https:\/\/randomnoun.github.io\/sql-explainerator\/\"><\/p>\n<div class=\"maven-button nobr\">\n <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.randomnoun.com\/wp\/wp-content\/uploads\/2012\/09\/maven-ball-42.png\" width=\"42\" height=\"42\" style=\"margin: 5px; float:left;\"><\/p>\n<div class=\"big\">sql-explainerator<\/div>\n<div class=\"small\">com.randomnoun.db:sql-explainerator<\/div>\n<\/div>\n<p><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve produced some more software. This one creates diagrams of query execution plans for MySQL.<\/p>\n","protected":false},"author":1,"featured_media":3751,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52],"tags":[74],"class_list":["post-3590","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","tag-java-sql"],"_links":{"self":[{"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/posts\/3590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/comments?post=3590"}],"version-history":[{"count":65,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/posts\/3590\/revisions"}],"predecessor-version":[{"id":4011,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/posts\/3590\/revisions\/4011"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/media\/3751"}],"wp:attachment":[{"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/media?parent=3590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/categories?post=3590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.randomnoun.com\/wp\/wp-json\/wp\/v2\/tags?post=3590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}