EmergencyEMERGENCY? Get 24/7 Help Now!

Introduction to Troubleshooting Performance – Troubleshooting Slow Queries webinar: Q & A

 | May 20, 2016 |  Posted In: EXPLAIN, MySQL, MySQL Webinars

Troubleshooting Slow Queries

In this blog, I will provide answers to the Q & A for the Troubleshooting Slow Queries webinar. First, I want to thank you for attending the April 28 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with […]

Read More

EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent

 | February 29, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

EXPLAIN FORMAT=JSON

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 table that participates in a  JOIN  operation on a single row. This works perfectly for simple queries:

You can see […]

Read More

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

 | February 22, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

Percona Server 5.7 performance improvements

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 another. Tables often have more than one index. Any of these indexes can be used to resolve query. The optimizer has […]

Read More

EXPLAIN FORMAT=JSON: buffer_result is not hidden!

 | February 9, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

EXPLAIN FORMAT=JSON

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 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 […]

Read More

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

 | January 29, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

EXPLAIN FORMAT=JSON

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 optimizing complicated queries with UNION, it is easy to get lost in the regular EXPLAIN  output trying to identify which part […]

Read More

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

 | January 25, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

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 now finish this topic and discuss the output for the rest of the subquery types. First, let’s look at the subquery […]

Read More

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

 | January 9, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

EXPLAIN FORMAT=JSON

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 require using temporary tables or filesort, and a regular EXPLAIN  clause provides this information. But it doesn’t show if this […]

Read More

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

 | January 4, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

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 when the  GROUP BY clause is run:

In the listing above, you can see which table was accessed by the  GROUP BY operation, the access […]

Read More

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

 | December 29, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

EXPLAIN FORMAT=JSON

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 above code shows member ordering_operation of query_block  (which includes the  order_by_subqueries array) with information on how the subquery in ORDER BY  was optimized. This is […]

Read More

EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

 | December 28, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, MySQL, query tuning

optimizer_switch

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. But what happens if we turn off this optimization? EXPLAIN FORMAT=JSON can help us with this investigation too. First lets look at the […]

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.