Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

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.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}