2

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

4
  • 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? Jul 7, 2012 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? Jul 8, 2012 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, 2012 at 12:25
  • Then use union all instead of union. Jul 8, 2012 at 13:42

2 Answers 2

3

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;
5
  • 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). Jul 8, 2012 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, 2012 at 12:27
  • @ypercube you are right. A good covering index would be better. Jul 8, 2012 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, 2012 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, 2012 at 12:39
1

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.

4
  • @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, 2012 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.
    – NoChance
    Jul 8, 2012 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, 2012 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/…
    – NoChance
    Jul 14, 2012 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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