| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.6.27, 5.7.9 | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
[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 >
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