MySQL : UNION script help I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small.

We run the followign script

SELECT * FROM (
SELECT dataString,dataInsertDateTime FROM tblMainData 
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1



The script runs quite slow
I want to mix the data from both tables
I want it ordered by mainDateTime


Thank you
Three(3) Suggestions
SUGGESTION #1
You have the following clause in the SQL
mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
If mainDateTime hold s a date and a time you should change the clause to this
mainDateTime BETWEEN '2012-06-20 00:00:00' AND '2012-07-07 23:59:59'
SUGGESTION #2
The whole WHERE clause gives away what index both tables need:
ALTER TABLE tblMainData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);
ALTER TABLE tblSecondData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);

That way, each SELECT will perform better index scans
SUGGESTION #3
If you want everything ordered by mainDateTIme, you should include it in the SELECT list:
SELECT * FROM (
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblMainData
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1
ordder by mainDateTime;