Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 70 10 Older Entries

Displaying posts with tag: explain (reset)

EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications
+0 Vote Up -0Vote Down

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
+0 Vote Up -0Vote Down

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

  [Read more...]
ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing
+1 Vote Up -0Vote Down

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 …

  [Read more...]
grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY
+1 Vote Up -0Vote Down

In the previous EXPLAIN FORMAT=JSON is Cool! series blog post, we discussed the  

group_by_subqueries

  member (which is child of

  [Read more...]
EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY
+1 Vote Up -0Vote Down

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   …

  [Read more...]
EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness
+0 Vote Up -0Vote Down

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 …
  [Read more...]
EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery
+0 Vote Up -0Vote Down

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:

mysql> explain select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null)G
*************************** 1. row …
  [Read more...]
used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used
+1 Vote Up -0Vote Down

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 …

  [Read more...]
used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes
+2 Vote Up -0Vote Down

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

  [Read more...]
rows_examined_per_scan, rows_produced_per_join: EXPLAIN FORMAT=JSON answers on question “What number of filtered rows mean?”
+1 Vote Up -0Vote Down

At the end of my talk “Troubleshooting MySQL Performance” at the LinuxPiter conference, a user asked me a question: “What does the EXPLAIN ‘filtered’ field mean, and how do I use it?” I explained that this is the percentage of rows …

  [Read more...]
Showing entries 1 to 10 of 70 10 Older Entries

Planet MySQL © 1995, 2016, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.