Skip navigation links

MySQL Forums :: Partitioning :: Prunning partitions with range by date


Advanced Search

Prunning partitions with range by date
Posted by: Diogo Domanski ()
Date: September 27, 2012 01:18PM

Hi all,

I created the following test table:

CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ins_date` datetime NOT NULL,
PRIMARY KEY (`id`, `ins_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( TO_DAYS(`ins_date`) ) (
PARTITION pre_year VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION cur_year_jan VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION cur_year_feb VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION cur_year_mar VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION cur_year_may VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION cur_year_apr VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION cur_year_jun VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION cur_year_jul VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION cur_year_aug VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION cur_year_sep VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION cur_year_oct VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION cur_year_nov VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION cur_year_dec VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION nex_year VALUES LESS THAN (MAXVALUE)
);

The table has these partitions:
- one partition for all records prior to 2012-01-01
- 12 partions for year 2012 (one for each month)
- one partition for all records after 2102-12-31

The objective was to test prunning behaivor. To achieve this, I inserted some records into this table:

-- Records for 2011
insert into test set ins_date = '2011-01-01';
insert into test set ins_date = '2011-02-02';
insert into test set ins_date = '2011-03-03';
insert into test set ins_date = '2011-04-04';
insert into test set ins_date = '2011-05-05';
insert into test set ins_date = '2011-06-06';
insert into test set ins_date = '2011-07-07';
insert into test set ins_date = '2011-08-08';
insert into test set ins_date = '2011-09-09';
insert into test set ins_date = '2011-10-10';
insert into test set ins_date = '2011-11-11';
insert into test set ins_date = '2011-12-12';

-- Records for 2012
insert into test set ins_date = '2012-01-01';
insert into test set ins_date = '2012-02-02';
insert into test set ins_date = '2012-03-03';
insert into test set ins_date = '2012-04-04';
insert into test set ins_date = '2012-05-05';
insert into test set ins_date = '2012-06-06';
insert into test set ins_date = '2012-07-07';
insert into test set ins_date = '2012-08-08';
insert into test set ins_date = '2012-09-09';
insert into test set ins_date = '2012-10-10';
insert into test set ins_date = '2012-11-11';
insert into test set ins_date = '2012-12-12';

-- Records for 2013
insert into test set ins_date = '2013-01-01';
insert into test set ins_date = '2013-02-02';
insert into test set ins_date = '2013-03-03';
insert into test set ins_date = '2013-04-04';
insert into test set ins_date = '2013-05-05';
insert into test set ins_date = '2013-06-06';
insert into test set ins_date = '2013-07-07';
insert into test set ins_date = '2013-08-08';
insert into test set ins_date = '2013-09-09';
insert into test set ins_date = '2013-10-10';
insert into test set ins_date = '2013-11-11';
insert into test set ins_date = '2013-12-12';

So I executed this select statement

EXPLAIN PARTITIONS SELECT * FROM test WHERE ins_date >= '2012-09-01' AND ins_date <= '2012-09-30';

expecting that the list of prunned partitions would contain only "cur_year_sep", but the result was "pre_year,cur_year_sep".

I would like to know if is this correct or I might consider it a bug?

If I change the WHERE clause to "WHERE ins_date = '2012-09-09'" (using the "equals" operator) the explain statement shows me only "cur_year_sep". However, I need to make use of "greater than" and "lower than" operators.

Thanks in advance

Diogo

Options: ReplyQuote


Subject Views Written By Posted
Prunning partitions with range by date 1166 Diogo Domanski 09/27/2012 01:18PM
Re: Prunning partitions with range by date 730 Aftab Khan 09/27/2012 01:39PM
Re: Prunning partitions with range by date (SOLVED) 662 Diogo Domanski 09/27/2012 03:00PM


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.