Description:
database scripts
CREATE TABLE part_date3
( c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
c2 varchar(30) default NULL,
c3 timestamp NOT NULL,
PRIMARY KEY (c1,c3),
KEY partindex(c3))
partition by range (UNIX_TIMESTAMP(c3))
(PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2010-02-01 00:00:00')) ,
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2010-03-01 00:00:00')) ,
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2010-04-01 00:00:00')) ,
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2010-05-01 00:00:00')) ,
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2010-06-01 00:00:00')) ,
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2010-07-01 00:00:00')) ,
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2010-08-01 00:00:00')) ,
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2010-09-01 00:00:00')) ,
PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2010-10-01 00:00:00')) ,
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2010-11-01 00:00:00')) ,
PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2010-12-01 00:00:00')) ,
PARTITION p12 VALUES LESS THAN MAXVALUE );
the results of executing partition query
explain partitions select count(*) from part_date3 where c3> timestamp('2010-05-03 00:00:00') and c3 < timestamp('2010-05-25 00:00:00')
result:
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12
not the
p0,p5
How to repeat:
rt
Description: database scripts CREATE TABLE part_date3 ( c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, c2 varchar(30) default NULL, c3 timestamp NOT NULL, PRIMARY KEY (c1,c3), KEY partindex(c3)) partition by range (UNIX_TIMESTAMP(c3)) (PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00')), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2010-02-01 00:00:00')) , PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2010-03-01 00:00:00')) , PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2010-04-01 00:00:00')) , PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2010-05-01 00:00:00')) , PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2010-06-01 00:00:00')) , PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2010-07-01 00:00:00')) , PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2010-08-01 00:00:00')) , PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2010-09-01 00:00:00')) , PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2010-10-01 00:00:00')) , PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2010-11-01 00:00:00')) , PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2010-12-01 00:00:00')) , PARTITION p12 VALUES LESS THAN MAXVALUE ); the results of executing partition query explain partitions select count(*) from part_date3 where c3> timestamp('2010-05-03 00:00:00') and c3 < timestamp('2010-05-25 00:00:00') result: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 not the p0,p5 How to repeat: rt