Over a million developers have joined DZone.

EXPLAIN FORMAT=JSON: buffer_result Is Not Hidden!

Today we’re going to look at how you can view the buffer result using JSON instead of the regular EXPLAIN command.

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

EXPLAIN FORMAT=JSONTime for another entry in the EXPLAIN FORMAT=JSON is cool! series. Today we’re going to look at how you can view the buffer result using JSON instead of the regular EXPLAIN command.

Regular EXPLAIN does not identify if SQL_BUFFER_RESULT was used at all. To demonstrate, let’s run this query:


Now, let’s compare it to this query:


Notice there is no difference, except the expected "Using temporary" value in the "Extra" row of the second query. The field  "Using temporary"  is expected here because SQL_BUFFER_RESULT  directly instructs the MySQL server to put a result set into a temporary table to free locks. But what if the query uses the temporary table by itself? For example, for a grouping operation? In this case, the  EXPLAIN result for the original query and the query that contains the  SQL_BUFFER_RESULT  clause will be 100% identical.

Compare:


With:


There is no difference! We not able to tell if we used a temporary table to resolve the query, or simply put the result set into the buffer. The EXPLAINFORMAT=JSON command can help in this case as well. Its output is clear, and shows all the details of the query optimization:



Firstly, we can see how the grouping_operation was optimized:

"grouping_operation": { "using_temporary_table": true, "using_filesort": true,

And it does indeed use the temporary table.

Now we can follow the details for SQL_BUFFER_RESULT:


With this output, we can be absolutely certain that the temporary table was created for both the SQL_BUFFER_RESULT and the grouping operation. This is especially helpful for support engineers who need the EXPLAIN output to help their customers to tune queries, but are afraid to ask for the same query twice — once with the SQL_BUFFER_RESULT clause and once without.

Conclusion


EXPLAINFORMAT=JSON does not hide important details for query optimizations.

Tags:

explain, EXPLAIN FORMAT=JSON is Cool!, MySQL Query Tuning

Categories:

 EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
result ,mysql ,buffer ,regular ,explain ,json

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}