EXPLAIN FORMAT=JSON is cool!

Understanding PMM QAN – Graphs & Metrics

understanding PMM QAN

In this blog post, I will share with you how to set up the PMM QAN for MongoDB and the formulas behind the metrics and graphs that you see on the QAN dashboard.
When one of my customers wanted to load test and understand the behavior of the queries in their MongoDB instance through a […]

Read more

EXPLAIN FORMAT=JSON wrap-up

EXPLAIN FORMAT=JSON

This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.
In this series, we’ve discussed everything unique to
EXPLAIN FORMAT=JSON. I intentionally skipped a description of members such as
table_name,
access_type  or
select_id, which are not unique.
In this series, I only mentioned in passing members that […]

Read more

EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent

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:

MySQL

mysql> explain select * from […]

Read more

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

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!

MongoDB System Tuning

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:

MySQL

mysql> explain select * from salariesG
*************************** 1. row ***************************
[…]

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

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

Read more

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

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

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

MySQL

mysql> explain format=json select dept_no from dept_emp group by dept_noG
*************************** 1. row ***************************
EXPLAIN: {
[…]

Read more

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

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_subqueries. 

EXPLAIN FORMAT=JSON can print details on how a subquery in
ORDER BY is optimized:

MySQL

mysql> explain format=json select emp_no, concat(first_name, ‘ ‘, last_name) f2 from employees order by (select emp_no limit 1)G
*************************** 1. […]

Read more