Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
Description
EXPLAIN output does not include "Using filesort" when using ORDER BY NULL, as we group by columns form two different tables. So, the temporary table is created and it has to be sorted by these columns to identify groups.
explain
|
-> SELECT production1_.CODE AS col_0_0,
|
-> order0_.STATUS AS col_1_0,
|
-> count(DISTINCT order0_.ID_ORDER) AS col_2_0
|
-> FROM IWAYS_ORDER order0_
|
-> INNER JOIN CONF_PARTNER production1_ ON order0_.ID_PARTNER=production1_.ID_PARTNER
|
-> GROUP BY production1_.CODE,
|
-> order0_.`STATUS`
|
-> ORDER BY NULL\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: production1_
|
type: index
|
possible_keys: PRIMARY
|
key: CODE
|
key_len: 48
|
ref: NULL
|
rows: 21
|
Extra: Using index; Using temporary
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: order0_
|
type: ref
|
possible_keys: IX_ID_PARTNER_ORDER_STATUS_DUE_DATE,IX_ID_PARTNER_INITIALIZED_DATE,IX_ID_PARTNER_STATUS_ORDER_UPDATE_DATE
|
key: IX_ID_PARTNER_ORDER_STATUS_DUE_DATE
|
key_len: 9
|
ref: iways_core.production1_.ID_PARTNER
|
rows: 11166
|
Extra: Using index
|
2 rows in set (0.00 sec)
|
However, when we enable 'profiling' then it is visible that sorting is done and most of the time is spent on sorting, actually I discovered this issue when I was trying to eliminate implicit sorting but this step cannot be skipped as explained earlier.:
 |
SHOW PROFILE FOR QUERY 14;
|
+--------------------------------+----------+
|
| Status | Duration |
|
+--------------------------------+----------+
|
| starting | 0.000138 |
|
| checking permissions | 0.000027 |
|
| checking permissions | 0.000020 |
|
| Opening tables | 0.000028 |
|
| After opening tables | 0.000019 |
|
| exit open_tables() | 0.000021 |
|
| System lock | 0.000021 |
|
| Table lock | 0.000020 |
|
| After table lock | 0.000019 |
|
| mysql_lock_tables(): unlocking | 0.000019 |
|
| exit mysqld_lock_tables() | 0.000021 |
|
| init | 0.000039 |
|
| optimizing | 0.000034 |
|
| statistics | 0.000045 |
|
| preparing | 0.000047 |
|
| executing | 0.000020 |
|
| Copying to tmp table | 0.000057 |
|
| Copying to tmp table | 0.074034 |
|
| converting HEAP to Aria | 0.230379 |
|
| Copying to tmp table on disk | 1.126279 |
|
| innobase_commit_low():trx_comm | 0.000070 |
|
| Copying to tmp table on disk | 0.000058 |
|
| Sorting result | 3.110680 |
|
| Sending data | 0.444269 |
|
| end | 0.000654 |
|
| removing tmp table | 0.000026 |
|
| end | 0.000025 |
|
| removing tmp table | 0.000222 |
|
| end | 0.000023 |
|
| query end | 0.000031 |
|
| innobase_commit_low():trx_comm | 0.000019 |
|
| query end | 0.000019 |
|
| closing tables | 0.000029 |
|
| freeing items | 0.000026 |
|
| removing tmp table | 0.000022 |
|
| freeing items | 0.000040 |
|
| logging slow query | 0.000024 |
|
| cleaning up | 0.000039 |
|
+--------------------------------+----------+
|
38 rows in set (0.00 sec)
|
EXPLAIN output when not using ORDER BY NULL
explain
|
-> SELECT production1_.CODE AS col_0_0,
|
-> order0_.STATUS AS col_1_0,
|
-> count(DISTINCT order0_.ID_ORDER) AS col_2_0
|
-> FROM IWAYS_ORDER order0_
|
-> INNER JOIN CONF_PARTNER production1_ ON order0_.ID_PARTNER=production1_.ID_PARTNER
|
-> GROUP BY production1_.CODE,
|
-> order0_.`STATUS`
|
-> \G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: production1_
|
type: index
|
possible_keys: PRIMARY
|
key: CODE
|
key_len: 48
|
ref: NULL
|
rows: 21
|
Extra: Using index; Using temporary; Using filesort
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: order0_
|
type: ref
|
possible_keys: IX_ID_PARTNER_ORDER_STATUS_DUE_DATE,IX_ID_PARTNER_INITIALIZED_DATE,IX_ID_PARTNER_STATUS_ORDER_UPDATE_DATE
|
key: IX_ID_PARTNER_ORDER_STATUS_DUE_DATE
|
key_len: 9
|
ref: iways_core.production1_.ID_PARTNER
|
rows: 11166
|
Extra: Using index
|
2 rows in set (0.00 sec)
|
 |