MySQL Forums
Forum List  »  Partitioning

Mystified by Partition Pruning on Dates
Posted by: John Thompson
Date: May 19, 2010 03:45AM

Apologies in advance if this is a dumb question, I'm a little new to My SQL. I've been trying to get partition pruning to work on date-based partitions (using v5.5.3). I've tried a couple of examples taken from the reference manual and forums, but the explain plans continue to show a full scan when querying using dates. If I use any datatype other than date, the tables prune as they should.

Does partitioning by dates need to be enabled on the server or should it work by default? Is there a problem with the syntax I'm using in either the CREATE or the SELECT?

-- partitioning with dates
-- attempt 1 - partition using to_days
DROP TABLE IF EXISTS MARS.t1;
CREATE TABLE MARS.t1 (
col1 VARCHAR(50) NOT NULL,
col2 DATE NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (to_days(col2 )) (
PARTITION p0 VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2010-02-01')),
PARTITION p2 VALUES LESS THAN (to_days('2010-03-01')),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO MARS.t1 (col1, col2) VALUES ('a','2010-01-02');
INSERT INTO MARS.t1 (col1, col2) VALUES ('b','2010-02-02');
INSERT INTO MARS.t1 (col1, col2) VALUES ('c','2010-02-03');

EXPLAIN PARTITIONS
SELECT * FROM MARS.t1
WHERE col2 = '2010-02-02';

-- PARTITIONS: p0,p1,p2,p3

-- attempt 2 - partition using columns (date)
DROP TABLE IF EXISTS MARS.t2;
CREATE TABLE MARS.t2 (
col1 VARCHAR(50) NOT NULL,
col2 DATE NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE COLUMNS (col2) (
PARTITION p0 VALUES LESS THAN ('2010-01-01'),
PARTITION p1 VALUES LESS THAN ('2010-02-01'),
PARTITION p2 VALUES LESS THAN ('2010-03-01'),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO MARS.t2 (col1, col2) VALUES ('a','2010-01-02');
INSERT INTO MARS.t2 (col1, col2) VALUES ('b','2010-02-02');
INSERT INTO MARS.t2 (col1, col2) VALUES ('c','2010-02-03');

EXPLAIN PARTITIONS
SELECT * FROM MARS.t2
WHERE col2 = DATE('2010-02-02');

-- PARTITIONS: p0,p1,p2,p3

Options: ReplyQuote


Subject
Views
Written By
Posted
Mystified by Partition Pruning on Dates
2980
May 19, 2010 03:45AM


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.