Bug #67023 EXPLAIN FORMAT=json output not documented.
Submitted: 30 Sep 2012 11:58 Modified: 31 Aug 2015 13:43
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.6.7 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[30 Sep 2012 11:58] Peter Laursen
Description:
No matter if I specify a LIMIT or not the JSON string always returns:

 

How to repeat:
EXPLAIN FORMAT=json SELECT * FROM t LIMIT 1000;
-- use another LIMIT or don't use LIMIT - does not change anything.
-- the table has one row BTW

returns: 

{                                                                                                                                                                
  "query_block": {                                                                                                                                               
    "select_id": 1,                                                                                                                                              
    "table": {                                                                                                                                                   
      "table_name": "t",                                                                                                                                         
      "access_type": "ALL",                                                                                                                                      
      "rows": 1,                                                                                                                                                 
      "filtered": 100                                                                                                                                            
    }                                                                                                                                                            
  }                                                                                                                                                              
}   

Suggested fix:
The page http://dev.mysql.com/doc/refman/5.6/en/explain.html does not provide any detailed inforamtion about how to understand the JSON output.

And this page http://dev.mysql.com/doc/refman/5.6/en/explain-output.html does not reflect the new JSON output format. It is simply not described at all.

' "filtered": 100 ' 
.. in the JSON output looks like a bug to me.  Maybe it is not, but then I should able to understand from documentation why it is not!
[30 Sep 2012 12:02] Peter Laursen
Correcting 'version'.  I use 5.6.7 (but wrote 5.6.6 in the first place). -- Peter
[1 Oct 2012 11:06] Peter Laursen
OK .. the output of 'filtered' is OK.  It is displayed as a percentage. So please ignore this.

Changing category to 'docs'.  There should be some comparative/side-by-side explanation of how the traditional (array) output is displayed as a Json-formatted string.
[2 Oct 2012 4:35] Sveta Smirnova
Thank you for the report.

Verified as described. For now use this blog post: http://glebshchepa.blogspot.com/2012/04/optimizer-new-explain-formatjson.html
[29 Oct 2012 17:08] Stefan Hinz
Since JSON format output is for developers, this should be covered in the Internals documentation. -Stefan
[29 Oct 2012 17:18] Peter Laursen
WHAT? Internals???

Any application developer may use the json option.
[1 Nov 2012 17:36] Sveta Smirnova
Stefan,

I disagree with you and agree with Peter: any MySQL user can use advantages of JSON output format.

Probably does not matter in which user manual this explanation would be placed, but "general use" version should contain a link to this explanation at least.
[1 Nov 2012 18:13] Peter Laursen
My point was rather: Isn't it a real BUMMER that it is revealed that such thing as an "Internals documentation" exists at all? I think Oracle never told anybody that before!

All the interfaces that MySQL has (SQL, XML, Json, UDFs, whatever) should be documented publicly and properly.  That is how simple it is. If there is need for an "Internals documentation" there also is for an "Externals documentation". 

So just make it "Documentation", please, rather than "Internals documentation"

(and don't make this bug report private.  Because I have the full thread in my mailbox and I will blog it publicly if you do).
[1 Nov 2012 18:34] Sveta Smirnova
Peter,

internal documentation is not internal to Oracle, but exists since MySQL AB times and explains internal server behavior. It is available at http://dev.mysql.com/doc/internals/en/index.html
[30 Nov 2014 9:40] Daniël van Eeden
I think a JSON schema (http://json-schema.org/) should be shipped with the server. This makes it easy to validate the output.
[30 Nov 2014 10:08] Daniël van Eeden
Example JSON output from 5.7.5-m15 "create table t1 (id int); EXPLAIN FORMAT=JSON SELECT * FROM t1"

Attachment: data.json (application/json, text), 466 bytes.

[30 Nov 2014 10:08] Daniël van Eeden
Example JSON schema

Attachment: schema.json (application/json, text), 1.39 KiB.

[30 Nov 2014 10:09] Daniël van Eeden
Python script to validate the data against the schema

Attachment: val.py (application/x-python, text), 259 bytes.

[12 Aug 2015 8:48] Jon Stephens
I'll try to handle this one.
[12 Aug 2015 9:13] Jon Stephens
Hi Daniël,

Please file a separate feature request bug regarding your suggestion about the JSON schema. *This* bug will be closed once the original docs issue (EXPLAIN FORMAT=JSON described/explained) has been taken care of.

Thanks!

jon.
[20 Aug 2015 18:57] Jon Stephens
I've added some info about JSON equivalents for EXPLAIN output column names; this should be online shortly. I'll add examples and some other things when/as time permits.