Description:
As you can see from EXPLAIN below we do not have any information about filesort
being done as well as about temporary table created for its resolution.
mysql> explain (select * from a) union (select * from b) order by c;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 129 | |
| 2 | UNION | b | ALL | NULL | NULL | NULL | NULL | 129 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
How to repeat:
CREATE TABLE a (
i int(11) default NULL,
c char(1) default NULL,
KEY i (i)
) ;
--
-- Dumping data for table `a`
--
INSERT INTO a VALUES (1,'a'),(1,'a'),(2,'a'),(3,'a'),(101,'a'),(101,'a'),(102,'a'),(103,'a'),(101,'a'),(101,'a'),(102,'a'),(103,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(101,'a'),(101,'a'),(102,'a'),(103,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(101,'a'),(101,'a'),(102,'a'),(103,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(401,'a'),(401,'a'),(402,'a'),(403,'a'),(101,'a'),(101,'a'),(102,'a'),(103,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(401,'a'),(401,'a'),(402,'a'),(403,'a'),(201,'a'),(201,'a'),(202,'a'),(203,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(401,'a'),(401,'a'),(402,'a'),(403,'a'),(301,'a'),(301,'a'),(302,'a'),(303,'a'),(401,'a'),(401,'a'),(402,'a'),(403,'a'),(401,'a'),(401,'a'),(402,'a'),(403,'a'),(501,'a'),(501,'a'),(502,'a'),(503,'a');
--
-- Table structure for table `b`
--
CREATE TABLE b (
i int(11) default NULL,
c char(1) default NULL,
KEY i (i)
) ;
--
-- Dumping data for table `b`
--
INSERT INTO b VALUES (1,'b'),(2,'b'),(3,'b'),(4,'b'),(101,'b'),(102,'b'),(103,'b'),(104,'b'),(101,'b'),(102,'b'),(103,'b'),(104,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(101,'b'),(102,'b'),(103,'b'),(104,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(101,'b'),(102,'b'),(103,'b'),(104,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(401,'b'),(402,'b'),(403,'b'),(404,'b'),(101,'b'),(102,'b'),(103,'b'),(104,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(401,'b'),(402,'b'),(403,'b'),(404,'b'),(201,'b'),(202,'b'),(203,'b'),(204,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(401,'b'),(402,'b'),(403,'b'),(404,'b'),(301,'b'),(302,'b'),(303,'b'),(304,'b'),(401,'b'),(402,'b'),(403,'b'),(404,'b'),(401,'b'),(402,'b'),(403,'b'),(404,'b'),(501,'b'),(502,'b'),(503,'b'),(504,'b');
explain (select * from a) union (select * from b) order by c;
