Buy Percona ServicesBuy Now!

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!, JSON, MySQL

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!, JSON, MySQL

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

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

 | December 22, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL

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, you can’t find details on exactly how this subquery was materialized. To find out more, use EXPLAIN FORMAT=JSON:

We can see […]

Read More

used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used

 | December 17, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL

In the previous post for this ongoing “EXPLAIN FORMAT=JSON is Cool!” series, we discussed covered indexes and how the  used_columns  array can help to choose them wisely. There is one more type of multiple-column indexes: composite indexes. Composite indexes are just indexes on multiple columns. Covered indexes are a subgroup of the larger set “composite indexes.” In this post we’ll […]

Read More

used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes

 | December 14, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL, MySQL 101

In the “MySQL Query tuning 101” video, Alexander Rubin provides an excellent example of when to use a covered index. On slide 25, he takes the query select name from City where CountryCode = ’USA’ and District = ’Alaska’ and population > 10000 and adds the index cov1(CountryCode, District, population, name) on table City. With Alex’s query tuning experience, making the right index decision is simple – but what about us mere mortals? If a query is more […]

Read More