MySQL : UNION script help

Question

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

Answer 1

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;

Answer 2

Your justification for why you separate the tables is not convincing to me. I suggest you consider table partitioning rather than table splitting into 2 tables. You did not provide enough information, so here is a guess.

The reason it is running slow is probably because you don't have the correct indexes defined. You should at least have an index defined on each table for mainDateTime column. Sorting can be provided by adding ORDER BY to the result set. Other reasons for bad performance may be memory, heavy table access by other users, row size, table table fragmantaion, locks, etc.