Over a million developers have joined DZone.

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. Read on to learn more.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

EXPLAIN FORMAT=JSONWe’ve already discussed using the ORDER BY clause with subqueries. However, you can also 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 job is needed for ORDER BY or for optimizing another part of the query.

For example, if we take a pretty simple query (select distinct last_name from employees order by last_name asc) and run EXPLAIN on it, we can see that both the temporary table and filesort were used. However, we can’t identify if these were applied to DISTINCT, or to ORDER BY, or to any other part of the query.

mysql> explain select distinct last_name from employees order by last_name ascG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299379
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select distinct `employees`.`employees`.`last_name` AS `last_name` from `employees`.`employees` order by `employees`.`employees`.`last_name`

EXPLAIN FORMAT=JSON tells us exactly what happened:

mysql> explain format=json select distinct last_name from employees order by last_name ascG
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "360183.80"
    },
    "ordering_operation": {
      "using_filesort": false,
      "duplicates_removal": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "299379.00"
        },
        "table": {
          "table_name": "employees",
          "access_type": "ALL",
          "rows_examined_per_scan": 299379,
          "rows_produced_per_join": 299379,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "929.00",
            "eval_cost": "59875.80",
            "prefix_cost": "60804.80",
            "data_read_per_join": "13M"
          },
          "used_columns": [
            "emp_no",
            "last_name"
          ]
        }
      }
    }
  }
}
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select distinct `employees`.`employees`.`last_name` AS `last_name` from `employees`.`employees` order by `employees`.`employees`.`last_name`

In the output above, you see can see that ordering_operation does not use filesort:

    "ordering_operation": {
      "using_filesort": false,

But, DISTINCT does:

      "duplicates_removal": {
        "using_temporary_table": true,
        "using_filesort": true,

If we remove the DISTINCT clause, we will find that ORDER BY started using filesort, but does not need to create a temporary table:

mysql> explain format=json select last_name from employees order by last_name ascG
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "360183.80"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "299379.00"
      },
<rest of the output skipped>

This means that in the case of the first query, a sorting operation proceeded in parallel with the duplicate keys removal.

Conclusion: EXPLAIN FORMAT=JSON provides details about ORDER BY optimization which cannot be seen with a regular EXPLAIN operation.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
mysql ,performance ,databases ,profiling

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}