Make a new plan, Stan

knoxg | October 14, 2022 | Programming | 1 Comment

I’ve produced some more software.

This one creates diagrams of query execution plans for MySQL.

^ There's tooltips on the things that have tooltips.

Explain that to me like I know what SQL is

You can get a query execution plan table from vanilla MySQL by entering EXPLAIN before the query, which looks a bit like this:

Input:

EXPLAIN select A.*, B.sales 
from (
    select sto.store_id, cit.city, cou.country
    from store sto
    left join address adr on sto.address_id = adr.address_id
    join city cit on adr.city_id = cit.city_id
    join country cou on cit.country_id = cou.country_id
) A
join (
    select cus.store_id, sum(pay.amount) sales
    from customer cus
    join payment pay on pay.customer_id = cus.customer_id
    group by cus.store_id
) B
on A.store_id = B.store_id
order by A.store_id;

Output:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	sto	index	PRIMARY,idx_fk_address_id	PRIMARY	1		2	100.00
1	PRIMARY	adr	eq_ref	PRIMARY,idx_fk_city_id	PRIMARY	2	sakila.sto.address_id	1	100.00	Using where
1	PRIMARY	cit	eq_ref	PRIMARY,idx_fk_country_id	PRIMARY	2	sakila.adr.city_id	1	100.00
1	PRIMARY	cou	eq_ref	PRIMARY	PRIMARY	2	sakila.cit.country_id	1	100.00
1	PRIMARY		ref			1	sakila.sto.store_id	160	100.00
3	DERIVED	cus	index	PRIMARY,idx_fk_store_id	idx_fk_store_id	1		599	100.00	Using index
3	DERIVED	pay	ref	idx_fk_customer_id	idx_fk_customer_id	2	sakila.cus.customer_id	26	100.00

And if you want a bit more detail, you can put EXPLAIN FORMAT=JSON in there, which gives it a bit more structure, and adds a heap of other useful information.

Output:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "120.39"
    },
    "ordering_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "sto",
            "access_type": "index",
            "possible_keys": [
              "PRIMARY",
              "idx_fk_address_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "store_id"
            ],
            "key_length": "1",
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "0.20",
              "prefix_cost": "1.20",
              "data_read_per_join": "32"
            },
            "used_columns": [
              "store_id",
              "address_id"
            ]
          }
        },
        {
          "table": {
            "table_name": "adr",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "idx_fk_city_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "address_id"
            ],
            "key_length": "2",
            "ref": [
              "sakila.sto.address_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.00",
              "eval_cost": "0.20",
              "prefix_cost": "3.40",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "address_id",
              "city_id"
            ],
            "attached_condition": "(`sakila`.`adr`.`city_id` is not null)"
          }
        },
        {
          "table": {
            "table_name": "cit",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "idx_fk_country_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "city_id"
            ],
            "key_length": "2",
            "ref": [
              "sakila.adr.city_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.00",
              "eval_cost": "0.20",
              "prefix_cost": "5.60",
              "data_read_per_join": "432"
            },
            "used_columns": [
              "city_id",
              "city",
              "country_id"
            ]
          }
        },
        {
          "table": {
            "table_name": "cou",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "country_id"
            ],
            "key_length": "2",
            "ref": [
              "sakila.cit.country_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.00",
              "eval_cost": "0.20",
              "prefix_cost": "7.80",
              "data_read_per_join": "416"
            },
            "used_columns": [
              "country_id",
              "country"
            ]
          }
        },
        {
          "table": {
            "table_name": "B",
            "access_type": "ref",
            "possible_keys": [
              ""
            ],
            "key": "",
            "used_key_parts": [
              "store_id"
            ],
            "key_length": "1",
            "ref": [
              "sakila.sto.store_id"
            ],
            "rows_examined_per_scan": 160,
            "rows_produced_per_join": 321,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "80.42",
              "eval_cost": "32.17",
              "prefix_cost": "120.39",
              "data_read_per_join": "7K"
            },
            "used_columns": [
              "store_id",
              "sales"
            ],
            "materialized_from_subquery": {
              "using_temporary_table": true,
              "dependent": false,
              "cacheable": true,
              "query_block": {
                "select_id": 3,
                "cost_info": {
                  "query_cost": "17759.50"
                },
                "grouping_operation": {
                  "using_filesort": false,
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "cus",
                        "access_type": "index",
                        "possible_keys": [
                          "PRIMARY",
                          "idx_fk_store_id"
                        ],
                        "key": "idx_fk_store_id",
                        "used_key_parts": [
                          "store_id"
                        ],
                        "key_length": "1",
                        "rows_examined_per_scan": 599,
                        "rows_produced_per_join": 599,
                        "filtered": "100.00",
                        "using_index": true,
                        "cost_info": {
                          "read_cost": "5.00",
                          "eval_cost": "59.90",
                          "prefix_cost": "64.90",
                          "data_read_per_join": "341K"
                        },
                        "used_columns": [
                          "customer_id",
                          "store_id"
                        ]
                      }
                    },
                    {
                      "table": {
                        "table_name": "pay",
                        "access_type": "ref",
                        "possible_keys": [
                          "idx_fk_customer_id"
                        ],
                        "key": "idx_fk_customer_id",
                        "used_key_parts": [
                          "customer_id"
                        ],
                        "key_length": "2",
                        "ref": [
                          "sakila.cus.customer_id"
                        ],
                        "rows_examined_per_scan": 26,
                        "rows_produced_per_join": 16085,
                        "filtered": "100.00",
                        "cost_info": {
                          "read_cost": "16086.00",
                          "eval_cost": "1608.60",
                          "prefix_cost": "17759.50",
                          "data_read_per_join": "377K"
                        },
                        "used_columns": [
                          "payment_id",
                          "customer_id",
                          "amount"
                        ]
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      ]
    }
  }
}

Interpretting what that actually means is pretty difficult, so the makers of MySQL created a thingamajig inside MySQL Workbench to visualise that explanation, which creates diagrams like this:

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 ).

Well what's the problem then ?

This is all well and good, but unfortunately:

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.
MySQL Workbench is GPL licensed, 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 hissy fit with AT&T back in the 80s. A lot of people don't want to do that.
It doesn't handle quite a few common SQL constructs

So here's a thing I've dubbed the SqlExplainerator, which does the same sort of thing, but:

it's a Java library, which you might find easier to invoke programmatically
there's a CLI as well, which you might find easier to invoke programmatically from languages that aren't Java
it generates SVG or HTML, some of which can be styled with CSS
it's BSD licensed
it handles a few more SQL constructs
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.
- 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.
it has three flavours of tooltips. Four if you include not having tooltips at all.

So what sorts of things does this thing handle which workbench doesn't

HAVING clauses
INSERT INTO queries
CTEs that are used more than once
window functions

Four flavours of tooltips ?

Oh yes. You've got

SVG title elements, when you need to embed the SVG somewhere that doesn't allow javascript
SVG + javascript, if you're able to include javascript inside the SVG, which then uses foreignObjects to format things a bit nicer
SVG + attributes, which is similar to SVG + javascript but without the javascript. You can BYO your own javascript to draw the tooltips instead.
no tooltips whatsoever.

Give me a rundown of the sort of things it can do:

The CLI gives you a rundown of the sorts of things it can do:

C:\util\java> java -jar sql-explainerator-0.0.1-with-dependencies.jar --help
usage: SqlExplaineratorCli [options]
 -h,--help                  This usage text
 -i,--infile <infile>       input file, or '-' for stdin; default = stdin
 -o,--outfile <outfile>     output file, or '-' for stdout; default = stdout
 -l,--layout <layout>       layout format (workbench or explainerator); default = explainerator
 -f,--format <format>       output format (svg or html); default = svg
 -t,--tooltip <tooltip>     tooltip type (none, title, attribute, javascript); default = title
 -j,--jdbc <jdbc>           JDBC connection string
 -u,--username <username>   JDBC username
 -p,--password <password>   JDBC password
 -d,--driver <driver>       JDBC driver class name; default = org.mariadb.jdbc.Driver
 -q,--sql <sql>             SQL to explain
 -c,--css <css>             alternate css file
 -s,--script <script>       alternate javascript file

This command will convert a MySQL JSON execution plan into an SVG diagram.
There are two layout methods: 'workbench' which will try to mimic the diagrams generated from MySQL
Workbench, or 'explainerator', which adds support for inserts, 'having' clauses, and window functions.

The execution plan can be supplied via stdin or --infile (Example 1), or can be retrieved from a
MySQL server (Example 2).

Example 1: To generate the query plan JSON, execute an 'EXPLAIN FORMAT=JSON' statement:

  mysql --user=root --password=abc123 --silent --raw --skip-column-names \
    --execute "EXPLAIN FORMAT=JSON SELECT 1 FROM DUAL" sakila > plan.json

then to generate the SVG diagram, supply this JSON as input to SqlExplaineratorCli:

  SqlExplaineratorCli --infile plan.json --outfile plan.svg
or
  cat plan.json | SqlPlainToImageCli > plan.svg


Example 2: To generate the diagram from an SQL statement, you will need to also supply a JDBC
connection string and any credentials required to connect, e.g.:

  SqlExplaineratorCli --jdbc jdbc:mysql://localhost/sakila --username root --password abc123 \
    --sql "SELECT 1 fROM DUAL" --outfile plan.svg

There's an example of the type of diagram it produces at the top of this post.

Is there anything it can't do ?

Probably. There's quite a few things 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.