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

MongoDB Atlas is a database as a service that makes it easy to deploy, manage, and scale MongoDB. So you can focus on innovation, not operations. Brought to you in partnership with MongoDB.

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

MongoDB Atlas is the best way to run MongoDB on AWS — highly secure by default, highly available, and fully elastic. Get started free. Brought to you in partnership with MongoDB.

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}