ordering_operation: EXPLAIN FORMAT=JSON Knows Everything About ORDER BY Processing
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.
Join the DZone community and get the full member experience.
Join For FreeFind out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate
We’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.
Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate
Published at DZone with permission of Peter Zaitsev , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}