MySQL : UNION script help
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;