Over a million developers have joined DZone.

EXPLAIN FORMAT=JSON: buffer_result Is Not Hidden!

DZone's Guide to

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 ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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.



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.


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


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


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

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

result ,mysql ,buffer ,regular ,explain ,json

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}