MySQL Forums
Forum List  »  Partitioning

Re: speed up the data retriving
Posted by: Rick James
Date: May 05, 2011 12:50AM

What is the output of this? I suspect it is at least 3 million:
SELECT COUNT(*) FROM bwfdata.tbl_data
where timestamp>='2011-04-01'
and timestamp<='2011-04-30';
(That would explain why the EXPLAIN says ALL instead of RANGE.)

The data is 1,486,396,736 bytes. That will take some time to read.

Hash partitioning does not guarantee which partition will contain what.

What will you do with the data when you get it? If you are doing counts and summations, consider letting SQL do the work.

You have a possible bug:
`TimeStamp` datetime NOT NULL,
where timestamp>='2011-04-01' and timestamp<='2011-04-30';
That will exclude all of Apr. 30th except for midnight. Suggest doing
where timestamp>='2011-04-01'
and timestamp < DATE_ADD('2011-04-01', INTERVAL 1 MONTH);

Options: ReplyQuote

Written By
April 28, 2011 04:41AM
April 29, 2011 08:37AM
May 02, 2011 11:46PM
Re: speed up the data retriving
May 05, 2011 12:50AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.