MySQL Forums
Forum List  »  Partitioning

Re: Pruning by month
Posted by: Michael Taggart
Date: October 23, 2008 06:17PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
10171
October 23, 2008 04:56AM
4378
October 23, 2008 06:12PM
Re: Pruning by month
4631
October 23, 2008 06:17PM
4317
October 23, 2008 06:46PM
4249
October 23, 2008 07:21PM
4046
October 25, 2008 06:42PM
3912
February 12, 2009 08:42AM


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.