MySQL Forums
Forum List  »  Partitioning

Re: Partitioning by Date
Posted by: Mikael Ronström
Date: November 11, 2006 02:13AM

Hi,
YEAR(DATETIME) works for partition pruning, YEAR(TIMESTAMP)
should probably work as well but isn't activated in the
code today. Please file a bug and it will be looked into
if this can be activated. In the meantime the workaround
is to use the DATETIME data type instead.

Rgrds Mikael


Christian Starka Wrote:
-------------------------------------------------------
> I want to use partitioning by range, based on a
> TIMESTAMP(14) column, using the year-function. But
> when explaining the query, i can see:
> - always all partitions involved, independently
> from the year-range that i'm querying
> - some special characters in front of the first
> created partitions.
>
> Does anybody know, if there are restricitions on
> the format of the base-column that i can use for
> partitioning.
>
> i'm using 5.1.11beta on Win2003 Server, query
> browser.
>
> CREATE TABLE `T` (
> `rowid` BIGINT UNSIGNED NOT NULL,
> `ref` BIGINT UNSIGNED NOT NULL,
> `lastchange` TIMESTAMP(14) DEFAULT '0000-00-00
> 00:00:00',
> `msec` INTEGER(3) UNSIGNED,
> `value` FLOAT,
> `status` BIGINT UNSIGNED,
> `lastchange_event` TIMESTAMP(14),
> `msec_event` INTEGER(3) UNSIGNED,
> `zaediff` FLOAT,
> `target` VARCHAR(1) DEFAULT 'C',
> `expire` TIMESTAMP(14),
> `seq_num` BIGINT UNSIGNED,
> UNIQUE `UK_REF_LASTCHANGE`(`ref`,`lastchange`)
> ) ENGINE=MyISAM
> PARTITION BY RANGE ( YEAR(lastchange)) (
> PARTITION p0 VALUES LESS THAN (2006),
> PARTITION p1 VALUES LESS THAN (2007),
> PARTITION p2 VALUES LESS THAN (2008),
> PARTITION p3 VALUES LESS THAN (2009),
> PARTITION p4 VALUES LESS THAN (2010)
> );

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3622
November 10, 2006 01:19AM
Re: Partitioning by Date
2329
November 11, 2006 02:13AM
2160
January 18, 2007 11:53PM
2571
January 19, 2007 05:29AM


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.