MySQL Bugs: #67023: EXPLAIN FORMAT=json output not documented.

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

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!

Correcting 'version'.  I use 5.6.7 (but wrote 5.6.6 in the first place). -- Peter

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.

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

Since JSON format output is for developers, this should be covered in the Internals documentation. -Stefan

WHAT? Internals???

Any application developer may use the json option.

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.

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

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

I think a JSON schema (http://json-schema.org/) should be shipped with the server. This makes it easy to validate the output.

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.

Example JSON schema

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

Python script to validate the data against the schema

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

I'll try to handle this one.

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.

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.