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