UNION and ORDER BY

[Ariel Scarpinelli]

Hi,

There is another question I've got.

Lets say I have the following query

(SELECT col1, col2, col3
FROM table
WHERE (col1 = 1) AND col2 BEETWEEN 10 AND 20)

UNION ALL

(SELECT col1, col2, col3
FROM table
WHERE (col1 = 2) AND col2 BEETWEEN 10 AND 20)

ORDER BY col2

There is a multicolumn key defined on table for col1 and col2

If you look at the explain of this query, the UNION RESULT table is using filesort.

Is there a way to optimize that? Because every subquery can be sorted by col2 using index, so I could use a merge sort kind algorithm to order it in a faster way. Does filesort work like that?. If you put ORDER BY inside each subquery Mysql optimizes it away, is there a way to keep them?

Thanks

Ariel