Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Do you want ORDER BY in your query? Are there rows that are identical (one in tblMainData and the other in tblSecondData) and you want them to appear once? –  ypercube Jul 7 '12 at 18:23
    
Since the data is in separate tables, one would assume that you have no need for returning DISTINCT values. Have you tried/considered UNION ALL? –  Aaron Brown Jul 8 '12 at 10:48
    
The data will be different both the tables. What I need end of the data is true to order by mainDateTime. –  newbie14 Jul 8 '12 at 12:25
    
Then use union all instead of union. –  Aaron Brown Jul 8 '12 at 13:42

2 Answers 2

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;
share|improve this answer
    
And if the datastring are not some huge TEXT or BLOB, indexes that include it: (deviceSerial, mainDateTime, datastring) would help to avoid hitting the tables (besides the index scans). –  ypercube Jul 8 '12 at 9:46
    
@ypercube the dataString will most to be 600 characters long. I would like to know when you said avoid hitting the table means by using index it wont go the tables is it that how index work is it? –  newbie14 Jul 8 '12 at 12:27
    
@ypercube you are right. A good covering index would be better. –  RolandoMySQLDBA Jul 8 '12 at 12:30
    
@rolando I would like to confirm so what is best indexining I should use in this vase but I got also other query which will select based on dataInsertDateTime. So should I composite all under one index or separately? –  newbie14 Jul 8 '12 at 12:32
    
@rolando your 3rd suggestion workd but I notice I must have the field ,mainDateTime in both the queries else it does not work what is the reason for it ya? –  newbie14 Jul 8 '12 at 12:39

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.

share|improve this answer
    
@kareem so if table partitioning how should I partition based on what key? I am planning to partition these tables only daily basis as these data will grow into millions. –  newbie14 Jul 8 '12 at 12:26
    
The partitioning key selection depends on how the table is frequently queried. I can't say for sure, but you may consider partitioning on the mainDateTime's date value. –  Emmad Kareem Jul 8 '12 at 18:43
    
@kareem is there any good guide and sample on date partitioning I dont find a good example for a newbie like me. –  newbie14 Jul 14 '12 at 3:11
    
The topic is usually discussed under database performance. See for example: dev.mysql.com/doc/refman/5.1/en/partitioning.html or everythingmysql.ning.com/profiles/blogs/… –  Emmad Kareem Jul 14 '12 at 9:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.