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 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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:
1 2 | "ordering_operation": { "using_filesort": false, |
But DISTINCT does:
1 2 3 | "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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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.
