Description:
It should be nice to use indices of underlying tables to order union results.
Currently this is not implemented and the server always does a filesort:
mysql> create table t1 (i INT PRIMARY KEY, s VARCHAR(10));
mysql> create table t2 like t1;
...
mysql> explain select * from t1 union select * from t2 order by i;
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
| 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
3 rows in set (0.00 sec)
How to repeat:
create table t1 (i INT PRIMARY KEY, s VARCHAR(10));
create table t2 like t1;
insert into t1 values (1, 'a'), (3, 'b'), (5, 'c');
insert into t2 values (2, 'd'), (4, 'e'), (6, 'f');
# see "Key" and "Extra" columns:
explain select * from t1 union select * from t2 order by i;