MySQL Forums
Forum List  »  Partitioning

Partitioning with YEARWEEK
Posted by: Ronnie Kurniawan
Date: January 06, 2009 10:29AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning with YEARWEEK
5974
January 06, 2009 10:29AM
3172
January 06, 2009 06:13PM
2758
January 13, 2009 04:34PM


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.