MySQL Forums
Forum List  »  Partitioning

Partitioning with timestamp column
Posted by: dileep ch
Date: August 01, 2012 10:25PM

Hi,

I partitioned timestamp column using range. Using syntax
alter table sessions_cts1 PARTITION BY RANGE ( UNIX_TIMESTAMP(cts) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-03-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-03-16 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-04-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-04-16 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-05-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-05-16 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-06-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-06-16 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-07-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-07-16 00:00:00') ),
PARTITION p10 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-08-01 00:00:00') ),
PARTITION p11 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-08-16 00:00:00') ),
PARTITION p12 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-09-01 00:00:00') ),
PARTITION p13 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-09-16 00:00:00') ),
PARTITION p14 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-01 00:00:00') ),
PARTITION p15 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-16 00:00:00') ),
PARTITION p16 VALUES LESS THAN (MAXVALUE)
);

The problem is when i do

select count(*) from sessions_cts1 where cts between '2012-05-01 00:00:00' and '2012-05-10 23:59:59';

The explain of the commnad says its accessing all the 17 partitions. Why its accessing all partitions and is there any other way to partition timestamp column.

Please help,

Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning with timestamp column
9364
August 01, 2012 10:25PM
3313
August 02, 2012 09:26PM
3054
August 02, 2012 11:44PM
3397
August 03, 2012 07:30PM


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.