Bug #79215 EXPLAIN FORMAT=JSON not properly documented
Submitted: 10 Nov 2015 21:47 Modified: 11 Nov 2015 0:58
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.27, 5.7.9 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[10 Nov 2015 21:47] Sveta Smirnova
Description:
EXPLAIN FORMAT=JSON can print additional value if compare to regular EXPLAIN, such as query cost, but these features are not documented anywhere

How to repeat:
mysql> EXPLAIN FORMAT=JSON SELECT user, host FROM mysql.user\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.13"
    },
    "table": {
      "table_name": "user",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "Host",
        "User"
      ],
      "key_length": "276",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "2.13",
        "eval_cost": "1.00",
        "prefix_cost": "3.13",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "Host",
        "User"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

See "cost_info" and "used_columns" part. Now try to find same information in regular EXPLAIN:

mysql> EXPLAIN  SELECT user, host FROM mysql.user\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 276
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Suggested fix:
Document all additions which EXPLAIN FORMAT=JSON provides
[11 Nov 2015 0:58] Miguel Solorzano
Thank you for the bug report.

C:\dbs>net start mysqld57
The MySQLD57 service is starting.
The MySQLD57 service was started successfully.

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > EXPLAIN FORMAT=JSON SELECT user, host FROM mysql.user\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.46"
    },
    "table": {
      "table_name": "user",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "Host",
        "User"
      ],
      "key_length": "276",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "2.06",
        "eval_cost": "0.40",
        "prefix_cost": "2.46",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "Host",
        "User"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

mysql 5.7 >