Bug #41123 UNION ... ORDER BY doesn't use indices of underlying tables
Submitted: 29 Nov 2008 13:59 Modified: 29 Nov 2008 19:35
Reporter: Gleb Shchepa Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Tags: unioin

[29 Nov 2008 13:59] Gleb Shchepa
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;
[29 Nov 2008 19:35] Gleb Shchepa
Sorry, it's normal behavior.