EXPLAIN FORMAT=JSON: buffer_result is not hidden!

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  EXPLAIN FORMAT=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: EXPLAIN FORMAT=JSON  does not hide important details for query optimizations.

Share this post