Description:
EXPLAIN FORMAT=JSON does not show "Using filesort" for result of UNION.
How to repeat:
create table t1 (a int);
insert into t1 values (1),(2),(3);
create table t2 (a int);
insert into t2 values (1),(2),(3);
## Let's try a UNION
explain select a from t1 union select a from t2;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)
## The same UNION with ORDER BY:
explain select a from t1 union select a from t2 order by a;
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
3 rows in set (0.01 sec)
## Now, let's try the same with format=json
## Both queries will produce the same JSON output:
explain format=json select a from t1 union select a from t2;
explain format=json select a from t1 union select a from t2 order by a;
| {
"query_block": {
"union_result": {
"using_temporary_table": true,
"table_name": "<union1,2>",
"access_type": "ALL",
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 3,
"filtered": 100
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 3,
"filtered": 100
}
}
}
]
}
}
} |
Suggested fix:
Make EXPLAIN FORMAT=JSON show "Using filesort" when appropriate
Description: EXPLAIN FORMAT=JSON does not show "Using filesort" for result of UNION. How to repeat: create table t1 (a int); insert into t1 values (1),(2),(3); create table t2 (a int); insert into t2 values (1),(2),(3); ## Let's try a UNION explain select a from t1 union select a from t2; +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+ 3 rows in set (0.00 sec) ## The same UNION with ORDER BY: explain select a from t1 union select a from t2 order by a; +----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort | +----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+ 3 rows in set (0.01 sec) ## Now, let's try the same with format=json ## Both queries will produce the same JSON output: explain format=json select a from t1 union select a from t2; explain format=json select a from t1 union select a from t2 order by a; | { "query_block": { "union_result": { "using_temporary_table": true, "table_name": "<union1,2>", "access_type": "ALL", "query_specifications": [ { "dependent": false, "cacheable": true, "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } }, { "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "table": { "table_name": "t2", "access_type": "ALL", "rows": 3, "filtered": 100 } } } ] } } } | Suggested fix: Make EXPLAIN FORMAT=JSON show "Using filesort" when appropriate