Partitioning with YEARWEEK
I'm trying to implement table partitioning with yearweek function. It's working normally as I insert data, the rows are inserted to the right partition.
When i try to do 'EXPLAIN PARTITIONS SELECT ... WHERE ...', I get this:
===> WHERE poll_time = [date] works OK:
mysql> explain partitions select * from trunk_stat_partitioned where poll_time = '2008-12-12'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trunk_stat_partitioned
partitions: p200850
type: ref
possible_keys: trunk_stat_poll_time_index
key: trunk_stat_poll_time_index
key_len: 8
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
===> WHERE poll_time > [date] doing a FULL TABLE SCAN:
mysql> explain partitions select * from trunk_stat_partitioned where poll_time > '2008-12-12'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trunk_stat_partitioned
partitions: --cut--p200846,p200847,p200848,p200849,p200850,p200851,p200852,p200901,p200902,p200903 --cut --
type: range
possible_keys: trunk_stat_poll_time_index
key: trunk_stat_poll_time_index
key_len: 8
ref: NULL
rows: 3867219
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
This is my table schema:
CREATE TABLE IF NOT EXISTS `trunk_stat_partitioned` (
`nams_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`trunk_no` smallint(5) unsigned NOT NULL DEFAULT '0',
`poll_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` tinyint(1) unsigned NOT NULL DEFAULT '1',
`tx_util` tinyint(3) unsigned NOT NULL DEFAULT '0',
`rx_util` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( YEARWEEK(poll_time)) (--cut-- PARTITION p200846 VALUES LESS THAN (200846) ENGINE = MyISAM, PARTITION p200847 VALUES LESS THAN (200847) ENGINE = MyISAM, PARTITION p200848 VALUES LESS THAN (200848) ENGINE = MyISAM, PARTITION p200849 VALUES LESS THAN (200849) ENGINE = MyISAM, PARTITION p200850 VALUES LESS THAN (200850) ENGINE = MyISAM, PARTITION p200851 VALUES LESS THAN (200851) ENGINE = MyISAM, PARTITION p200852 VALUES LESS THAN (200852) ENGINE = MyISAM, PARTITION p200901 VALUES LESS THAN (200901) ENGINE = MyISAM, PARTITION p200902 VALUES LESS THAN (200902) ENGINE = MyISAM, --cut--
On the second query I get a full table scan instead of p200850,p200851...
Did I do something wrong or maybe I miss something?
Thanks.