Pruning by month
Hi,
I have the following table: -
CREATE TABLE `test3` (
`id` int(11) NOT NULL,
`dtime` datetime NOT NULL,
`value` bigint(20) NOT NULL,
KEY `test3_id_dtime_idx` (`id`,`dtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 /*!50100 PARTITION BY LIST (month(dtime))
(PARTITION pJan VALUES IN (1) ENGINE = MyISAM,
PARTITION pFeb VALUES IN (2) ENGINE = MyISAM,
PARTITION pMar VALUES IN (3) ENGINE = MyISAM,
PARTITION pApr VALUES IN (4) ENGINE = MyISAM,
PARTITION pMay VALUES IN (5) ENGINE = MyISAM,
PARTITION pJun VALUES IN (6) ENGINE = MyISAM,
PARTITION pJul VALUES IN (7) ENGINE = MyISAM,
PARTITION pAug VALUES IN (8) ENGINE = MyISAM,
PARTITION pSep VALUES IN (9) ENGINE = MyISAM,
PARTITION pOct VALUES IN (10) ENGINE = MyISAM,
PARTITION pNov VALUES IN (11) ENGINE = MyISAM,
PARTITION pDec VALUES IN (12) ENGINE = MyISAM) */
After running some test queries I noticed that partition pruning was not working when I specified a date range - however if a specified a single date pruning was done. Having read through the manual and various blogs/postings my understanding is that pruning on date only works when I use the YEAR() or TO_DAY() functions in my partitioning method?
So my question is: given the current limitations of partitioning is there anyway I can achieve partitioning by month and have pruning working when specifying a date range?
Thanks,
Alan