Re: Pruning by month

[Michael Taggart]

This issue seems to be that partition pruning only work specifically with TO_DAYS() and YEAR(). Anything else won't be pruned. So, here is what I am thinking. Change your table to this:

CREATE TABLE `test3` (
`id` int(11) NOT NULL,
`dtime` datetime NOT NULL,
`month` smallint(2) 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 HASH(month)
PARTITIONS 12;

Then all that is needed is to change your application to always insert the month integer into the month column. I wonder if I could use a function or trigger to automatically do it?