MySQL Forums
Forum List  »  Optimizer & Parser

Re: BETWEEN a DATE and a hard place
Posted by: Gerald Bayles
Date: April 21, 2014 08:36AM

Hi Rick and thank you for your help.

The number of records ranges from two million to the biggest number we can efficiently store on an AWS X1-Large. The numbers are not cast in stone.

We are using partitions to keep a number of days of storage for our customers. We want to offer them the highest number of days possible.

Yes, dev_tz_date is INDEXed. It is a DATE type and, in the snippet shown, would uniquely identify a particular day within the last 100 days (going past 100 would, obviously, wrap around randomly.

I wanted to use a RANGE PARTITION but find the syntax too cumbersome:
PARTITION BY RANGE ( DAYOFYEAR(dev_tz_date) % 100 ) (
PARTITION p0 VALUES LESS THAN (0),
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
. . .
PARTITION p3 VALUES LESS THAN 100
);
Is there another way to specify RANGE partitions?


Since we are restricting searches to an arbitrary but contiguous one week block, I am not averse to using many 'OR's but prefer the simplicity of BETWEEN.


Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
2037
April 16, 2014 02:07PM
1072
April 18, 2014 05:19PM
Re: BETWEEN a DATE and a hard place
1151
April 21, 2014 08:36AM
1108
April 24, 2014 08:20PM
1054
April 26, 2014 05:46AM


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.