EmergencyEMERGENCY? Get 24/7 Help Now!

Tag Archives: explain

EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent

Once again it’s time for another EXPLAIN FORMAT=JSON is cool! post. This post will discuss how EXPLAIN FORMAT=JSON allows the nested_loop command to make the JOIN operation hierarchy transparent. The regular EXPLAIN  command lists each …

Read More
 
EXPLAIN FORMAT=JSON: cost_info knows why optimizer prefers one index to another

Time for another entry in the EXPLAIN FORMAT=JSON is cool! series of blog posts. This time we’ll discuss how using EXPLAIN FORMAT=JSON allows you to see that cost_info  knows why the optimizer prefers one index to …

Read More
 
EXPLAIN FORMAT=JSON: buffer_result is not hidden!

Time 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 …

Read More
 
EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications

Ready for another post in the EXPLAIN FORMAT=JSON is Cool series! Great! This post will discuss how to see all the information that is contained in optimized queries with UNION using the union_result and query_specifications commands.   When …

Read More
 
EXPLAIN FORMAT=JSON has details for subqueries in HAVING, nested selects and subqueries that update values

Over several previous blog posts, we’ve already discussed what information the  EXPLAIN FORMAT=JSON output provides for some subqueries. You can review those discussions here, here and here. EXPLAIN FORMAT=JSON shows many details that you can’t get with other commands. Let’s …

Read More
 
ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing

We’ve already discussed using the ORDER BY clause with subqueries. You can also, however, use the  ORDER BY clause with sorting results of one of the columns. Actually, this is most common way to use this clause. Sometimes such queries …

Read More
 
grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY

In the previous EXPLAIN FORMAT=JSON is Cool! series blog post, we discussed the   group_by_subqueries  member (which is child of grouping_operation). Let’s now focus on the  grouping_operation  and other details of  GROUP BY  processing. grouping_operation simply shows the details of what happens …

Read More
 
EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY

Another post in the EXPLAIN FORMAT=JSON is Cool! series! In this post, we’ll discuss how the EXPLAIN FORMAT=JSON provides optimization details for  ORDER BY and   GROUP BY operations in conjunction with  order_by_subqueries and   group_by_subqueriesEXPLAIN FORMAT=JSON can print details on how a subquery in ORDER BY is optimized:

The …

Read More
 
EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

The previous post in the EXPLAIN FORMAT=JSON is Cool! series showed an example of the query select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null), where the subquery was materialized into a temporary table and then joined with the outer query. This is known as a semi-join optimization. …

Read More
 
EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery

EXPLAIN FORMAT=JSON The regular EXPLAIN command already provides some information about subquery optimization. For example, you can find out if the subquery is dependent or not, and (since version 5.6) if it was materialized:

However, …

Read More
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.