How to improve MYSQL performance on UNION ALL?

Question

I am optimizing a query which involves a UNION ALL of two queries. Both Queries have been more than optimized and run at less than one second separately. However, when I perform the union of both, it takes around 30 seconds to calculate everything.

I won't bother you with the specific query, since they are optimized as they get, So let's call them Optimized_Query_1 and Optimized_Query_2

Number of rows from Optimized_Query_1 is roughly 100K

Number of rows from Optimized_Query_2 is roughly 200K

SELECT * FROM (
   Optimized_Query_1
UNION ALL
   Optimized_Query_2
) U 
ORDER BY START_TIME ASC

I do require for teh results to be in order, but I find that with or without the ORDER BY at the end the query takes as much time so shouldn't make no difference.

Apparently the UNION ALL creates a temporary table in memory, from where the final results are then given, is there any way to work around this?

Thanks

Answer 1

You can't optimize UNION ALL. It simply stacks the two results sets on top of each other. Compared to UNION where an extra step is required to remove duplicates, UNION ALL is a straight stacking of the two result sets. The ORDER BY is likely taking additional time.

You can try creating a VIEW out of this query.