EXPLAIN FORMAT=JSON differences from MySQL

EXPLAIN FORMAT=JSON output in MySQL and MariaDB.

MariaDB's EXPLAIN JSON output is different from MySQL's. Here's a list of differences. (Currently they come in no particular order).

Attached conditions are prettier

MySQL prints conditions with too many quotes and braces. Also, subqueries are printed in full (despite that you also get a plan for this subquery). You see something like this:

      "attached_condition": "((`test`.`t1`.`a` < (/* select#2 */ select min(`test`.`t10`.`b`) from `test`.`t10`)) or (`test`.`t1`.`a` > (/* select#3 */ select max(`test`.`t10`.`b`) from `test`.`t10`)))",

      "attached_condition": "((`test`.`t20`.`col1` > `test`.`t20`.`col2`) or (`test`.`t20`.`col3` = 4))"

in MariaDB, the same conditions are printed like this:

      "attached_condition": "((t1.a < (subquery#2)) or (t1.a > (subquery#3)))"

      "attached_condition": "((t20.col1 > t20.col2) or (t20.col3 = 4))"

JSON pretty-printer is smarter

MySQL's JSON pretty-printer is pretty dumb:

      "possible_keys": [
        "a"
      ],
      "key": "a",
      "used_key_parts": [
        "a"
      ],

MariaDB's JSON pretty-printer is a bit smarter:

      "possible_keys": ["a"],
      "key": "a",
      "key_length": "5",
      "used_key_parts": ["a"],

Index Merge shows used_key_parts

For multi-part keys, tabular EXPLAIN shows key_length column and leaves the user to do column-size arithmetic to figure out how many key parts are used.

MySQL's EXPLAIN=JSON may show used_key_parts member which shows which key parts are used. For range access, key_length is also provided:

      "access_type": "range",
      "possible_keys": [
        "col1"
      ],
      "key": "col1",
      "used_key_parts": [
        "col1",
        "col2"
      ],
      "key_length": "10",

But if you are using index_merge, you will still have to decode key_length:

<</code>> "table": { "table_name": "t22", "access_type": "index_merge", "possible_keys": [ "col1", "col3" ], "key": "sort_union(col1,col3)", "key_length": "10,5", "rows": 2398, <</code>>

In MariaDB, you get used_key_parts for all parts of index_merge:

      "table_name": "t22",
      "access_type": "index_merge",
      "possible_keys": ["col1", "col3"],
      "key_length": "10,5",
      "index_merge": {
        "sort_union": {
          "range": {
            "key": "col1",
            "used_key_parts": ["col1", "col2"]
          },
          "range": {
            "key": "col3",
            "used_key_parts": ["col3"]
          }
        }