Mystified by Partition Pruning on Dates
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